Thread: dropping leading zero

I have one procedure which genrates reports.I am using front end to genrate this report.from using XSL i m creating csv file of this report and using vb I m sending this report to member as attachment of emails.

I have one column in my table named productcode of varchar datatype.

This column stores these type of values
09310
01599
But when user opens this csv in xsl sheet the leading zero's has been dropped.

so in xsl the values become like this
9310
1599
This is the example query which is used in procedures

Code:

SELECT  InvoiceNumber AS 'Invoice Number',    
 CONVERT(VARCHAR,OrderDate,103) AS 'Order Date',       
 ProductCode AS 'Product Code'     
FROM #Results    
ORDER BY InvoiceNumber

I hope the changes require in procedure only.

excel will determine a column type by the type of data put into it. so if it sees all numbers it will make them numbers and drop leading zeros. the only way around it is to save the file as .txt and when it's opened in excel it will invoke the Text to Columns Wizard which will allow the user to state that the field in question is text and not numbers, therefore keeping your leading zeros.

Can you force the column to be formatted as text ahead of time? If not you can use the text formula. You can write the formula as =TEXT(A2, "00000") and that would pad a four digit number with a zero in front. I don't know if that helps.

where should i write this formula.My product code might be 4 or 5 or 6 digits long.There is any way to force this rule in sql procedure only.

I have seen reference on the web to formatting the CSV files as:

Code:

ABC,="001234",Four-digit code
ZYX,="012345",Five-digit code

And reports are that is works. However, in my Excel 2007 this imports exactly as specified -- that is B1 is ="001234" and B2 is ="012345" . Perhaps MS 'fixed' this in 2007 and it may work for you.

Other than this, I know of no way to force Excel to read what appears to be a numeric value as text from a .CSV file.

The SQL for this:

Code:

SELECT...
  '="' + right('000000' + ProductCode, 6) + '"' as [Product Code]

[edit] silly me, the column value already has the leading zeros, so the right function is not necessary[/edit]

One other idea that might work but is really a TOTAL HACK JOB. If I prepend the value is a 0xA0 character (which is a space character in my default font in Excel), the values import fine into Excel -- except for a space in front of the value which is hardly noticeable anyway.

Usual caveats apply when using character values > 127 -- the may TOTALLY not work in your case.

Now the question of getting the SQL query to emit a 0xA0 -- I don't know off the top of my head.

[edit]Silly me again:

Code:

 Select Char(160) + ProductCode

[/edit]

I have no idea if this will work for you, but I have been able to get around the formatting by adding a single quote at the beginning of a string to force it not to change formats. In the actual cell the single quote is not visible, and it seems to ignore it. So... you might do as Wolffy suggested just use the char value for a single quote instead of a space.

Ya know... maybe we're over thinking this. Have you tried explicitly casting the product code to varchar? That might work. Give that a shot first before trying the character code hacks

Interestingly, adding the quote doesn't seem to work in Excel 2007, but other have reported that it does. I'd try this first.

The data format in SQL doesn't matter here as it's only being used to spin off a .CSV file. It's Excel that is trying to be overly smart when loading the data.

@Laura
I have already tried explicitly casting the product code to varchar but no luck.
@Wolffy
I will try your suggestion first in the morning.