August 14, 2018 at 11:48 pm
Database table field is Varchar(4), value is 0000 (4 zeros)
exporting it to .csv file via Flat File Destination/Flat File Connection (column named Value in the attached image). BIDS 2008.
it preserves its correct value (0000) same as it is in sql server table.
when the person who receives the file opens it in Excel, only one zero remains.
is there any way to export it to csv file or txt file so that when file opened in Excel all 4 zeros get preserved ?
Likes to play Chess
August 15, 2018 at 12:44 am
VoldemarG - Tuesday, August 14, 2018 11:48 PMDatabase table field is Varchar(4), value is 0000 (4 zeros)exporting it to .csv file via Flat File Destination/Flat File Connection (column named Value in the attached image). BIDS 2008.
it preserves its correct value (0000) same as it is in sql server table.when the person who receives the file opens it in Excel, only one zero remains.
is there any way to export it to csv file or txt file so that when file opened in Excel all 4 zeros get preserved ?
This has nothing to do with the export, it is the way Excel converts the value into a generic numerical format, where all the leading zeros are discarded. A possible workaround could be to format the column as Text in Excel.
😎
August 15, 2018 at 2:17 am
Or add a single apostrophe to the beginning of the four zeros
Far away is close at hand in the images of elsewhere.
Anon.
August 15, 2018 at 5:30 am
If you can export it as '="0000"'
August 15, 2018 at 5:54 am
Unfortunately, Excel does not place integer values automatically in the common string table when initially reading the CSV, leading zeros will be lost.
😎
Few points:
August 15, 2018 at 6:01 am
Eirikur Eiriksson - Wednesday, August 15, 2018 5:54 AMUnfortunately, Excel does not place integer values automatically in the common string table when initially reading the CSV, leading zeros will be lost.
😎Few points:
- If the value in the CSV is prefixed with an apostrophe will be exactly that, a text containing an apostrophe and digits in the Excel cells.
- Formatting is not included in the CSV and will be lost.
- Any exports formats that are not supported by the CVS format (which are all of them) will be lost and such exercise is futile as the problem is the way Excel reads the file.
Indeed, Excel, honestly, can be an awful tool for interacting with CSV files, as it tends to make poor/wrong assumptions on the data. Like here, where the value "0000" is interpreted as the integer 0, not the string "0000".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 15, 2018 at 2:00 pm
Thanks.
Likes to play Chess
August 16, 2018 at 4:51 am
Not that it necessarily helps you here but I would never start data with leading zeros as, the chances are, it will end up in Excel at some point.
Appreciate sometimes it's out of our control e.g. phone numbers - although not sure what the leading zero actually does here (Should point out, I'm from the UK)
- Damian
August 16, 2018 at 5:00 am
DamianC - Thursday, August 16, 2018 4:51 AMNot that it necessarily helps you here but I would never start data with leading zeros as, the chances are, it will end up in Excel at some point.
Appreciate sometimes it's out of our control e.g. phone numbers - although not sure what the leading zero actually does here (Should point out, I'm from the UK)
In phone numbers, starting with 00 is the same as the + sign, almost always followed by the country code, a single 0 is normally a reference to an area type code.
😎
August 16, 2018 at 5:10 am
I wanted to elaborate on the response provided by Eirikur. While his statement is true: "This has nothing to do with the export, it is the way Excel converts the value into a generic numerical format, where all the leading zeros are discarded." I want to caution how the next statement is implemented: "A possible workaround could be to format the column as Text in Excel."
When opening a .csv file by double clicking the file, the user is in effect allowing Excel to decide on its own how best to interpret each comma delimited value. By default, it recognizes fields as "general" where it sounds like you would want this particular field opened as "text". I'm sure there are more ways to accomplish this, but please allow me to outline 3 possible approaches:
1. From within the Excel UI, import the .csv file by clicking on the data tab and selecting import from text. Browse to the .csv file and select it for import. You'll be presented with a series of windows asking how to interpret the file; the first of which asks if it is fixed width or column delimited. After selecting column delimited, ensure you select comma in the next window telling Excel the fields are separated by commas. The third window should allow you to preview the data and set each columns data type. Your "0000" column should get set to "text", instead of "general". Click finish to complete the import.
2. Copy the data from SSMS, and paste as values (right click Excel spreadsheet and click the "123" button) which will paste the fields as text.
3. Open a new Excel spreadsheet and highlight the columns that will contain your pasted "0000" field (along with any other field that should be recognized as text). Change the data type from "general" to "text" before pasting your data from SSMS.
Users should be aware this default "general" datatype used by Excel affects more than just leading zeros. If you have a field that contains "Feb-25", when pasted directly into Excel, it will change to a date field, even though that is not what was originally intended. Also, Excel likes to transform values that look like scientific notation into such. So a value of 1234-e23 will look entirely different once pasted into a spreadsheet.
At the minimum, I'd suggest following option #2 above, but #1 (in my opinion) makes the most sense and seems to provide the least room for erroneous data results.
August 16, 2018 at 5:44 am
mattbr80 - Thursday, August 16, 2018 5:10 AMI wanted to elaborate on the response provided by Eirikur. While his statement is true: "This has nothing to do with the export, it is the way Excel converts the value into a generic numerical format, where all the leading zeros are discarded." I want to caution how the next statement is implemented: "A possible workaround could be to format the column as Text in Excel." When opening a .csv file by double clicking the file, the user is in effect allowing Excel to decide on its own how best to interpret each comma delimited value. By default, it recognizes fields as "general" where it sounds like you would want this particular field opened as "text". I'm sure there are more ways to accomplish this, but please allow me to outline 3 possible approaches:1. From within the Excel UI, import the .csv file by clicking on the data tab and selecting import from text. Browse to the .csv file and select it for import. You'll be presented with a series of windows asking how to interpret the file; the first of which asks if it is fixed width or column delimited. After selecting column delimited, ensure you select comma in the next window telling Excel the fields are separated by commas. The third window should allow you to preview the data and set each columns data type. Your "0000" column should get set to "text", instead of "general". Click finish to complete the import.2. Copy the data from SSMS, and paste as values (right click Excel spreadsheet and click the "123" button) which will paste the fields as text.3. Open a new Excel spreadsheet and highlight the columns that will contain your pasted "0000" field (along with any other field that should be recognized as text). Change the data type from "general" to "text" before pasting your data from SSMS.Users should be aware this default "general" datatype used by Excel affects more than just leading zeros. If you have a field that contains "Feb-25", when pasted directly into Excel, it will change to a date field, even though that is not what was originally intended. Also, Excel likes to transform values that look like scientific notation into such. So a value of 1234-e23 will look entirely different once pasted into a spreadsheet. At the minimum, I'd suggest following option #2 above, but #1 (in my opinion) makes the most sense and seems to provide the least room for erroneous data results.
I agree, the #1 option is by far the safest of the three.
😎
Looks like I was typing faster than I was thinking, there is an oxymoron in my reply, sorry about that.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply