SQL Mail
-
While sending csv attachments via sql mail, the leading zeroes on some of the fields are being lost after exporting into excel. How can this be avoided?
-
I think the problem is in Excel.
You must set the field format to text, so that you can maintain the leading zeroes.
-
Yes, I believe that if you import a text file into Excel, it samples the first x rows to make a best guess at the data type. In your case its probably guessing that the data is numeric and stripping off the leading zeros
-
Yes the problem is EXCEL. One possible workaround it to have the first row with no leading zeros, sort of a header row.
xxxxxx,xxxxxx,xxxxxx,xxxxxx
001234,012345,000123,000012
-Isaiah -
One way around this is to save your file as a text file (.txt). When you open it in Excel, specify that it is delimited, the delimiter is comma, and when it asks you to specify the format of each field, change the format to text. You can set up a macro to open and load your file.
-
You don't specify how you are creating the CSV file, but if you put a single quote before the numeric value, Excel will automatically treat it as text.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
quote:
but if you put a single quote before the numeric value, Excel will automatically treat it as textOnly because single quote is not a numeric value. The single quote will be part of the text in the cell, eg '0001 will be '0001 (5 chars). This is different to typing in '0001 which will result in 0001 (4 chars) or using F2 on a cell containing '0002 which will result in 0002 (4 chars). It is all due to csv files not having formatting.
Edited by - davidburrows on 12/11/2003 05:59:19 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply