LinkBack URL
About LinkBacks
I have a CSV file I'm importing into Excell that contains leading zeros in a number field. I cannot get the zeros to display in Excel, but the user of the report needs to see them. Is there a way to dsiplay the leading zeros on a number field?
I have a CSV file I'm importing into Excell that contains leading zeros in a number field. I cannot get the zeros to display in Excel, but the user of the report needs to see them. Is there a way to dsiplay the leading zeros on a number field?
Last edited by NBVC; 11-21-2008 at 04:58 PM.
Are the total number of digits the same for each?
If yes, you can select the range and go to Format|Cells and select Custom from the Number tab...
Then enter enough 0's to get you the desired total number of digits.
e.g. 0000000 will change 12 to 0000012
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
No the lenghts are not the same. Some are 12 Some are 15.
Once they've been imported, how would you tell which should have 12 digits and which 15? Is there a pattern or is a printout of the original data the only way to tell?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Can you give examples of what you are getting and what you are desiring to see?
They are in the CSV text file correctly, but they change when the CSV is opened in Excel. If the Shipment type field value is "Ground", I know it needs to be 15, if "Express", i know it needs to be 12. The CSV file is generated from a SSIS package and emailed to the user. So I don't get a chance to see it.
305830862350 is in the CSV file as 000305830862350
I don't have an example of a 12 digit number with leading zeros.
You'll probably need to use a formula in an adjacent column and then copy/paste special over the original...
e.g. =IF(A1="General",Text(B1,Rept(0,15)),Text(B1,Rept(0,12)))
Where A1 contains General or Express adn B1 contains the number.
Then copy the new column and paste Special... selecting Values, over the old column... then delete the new column.
As I said earlier, I don't get a chance to see the actual Excel file. The CSV is emailed to the user from an SSIS pack. I guess I can show the end users how to write the formula, if there is no easier option.
The only other thing I can suggest is to perhaps save the original as a txt file and then when you open that file in Excel... it should come up with the text import wizard where you can delimit the file by commas and in step 3, you can change the format of any given column to Text (from the General default) before you actually import it. That should preserve the leading 0's
That looks like it will work. I can show the end users how to change the field type on the import.
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks