Home

CSV format dropping leading zeros in a "general format" column

%3CLINGO-SUB%20id%3D%22lingo-sub-308629%22%20slang%3D%22en-US%22%3ECSV%20format%20dropping%20leading%20zeros%20in%20a%20%22general%20format%22%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308629%22%20slang%3D%22en-US%22%3E%3CP%3EHappy%20New%20Year%20all!%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20everything%20I%20can%20online%20with%20no%20luck%2C%20and%20am%20turning%20to%20you%20all%20for%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackground%E2%80%A6one%20of%20my%20work%20tasks%20is%20balancing%20and%20entering%20the%20details%20for%20our%20company%20credit%20card.%26nbsp%3BIt%20requires%20taking%20some%20downloaded%20detail%20(from%26nbsp%3Ba%20website)%20in%20a%20CSV%20formatted%20Excel%20spreadsheet%2C%20doing%20some%20additional%20formatting%2C%20saving%20it%20as%20a%20CSV%20spreadsheet%20and%20uploading%20it%20to%20Deltek%20(our%20accounting%20platform).%20Not%20a%20big%20to-do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%203%20years%20this%20worked%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbout%20a%20year%20ago%20something%20changed%20(I%20think%20in%20Excel)%20to%20where%20in%20one%20of%20my%20columns%2C%20which%20has%20been%20manually%20formatted%20(by%20me%2C%20the%20user)%20to%20a%20%E2%80%9CGeneral%E2%80%9D%20format%20and%20has%20a%20leading%20zero%20(example%20the%20date%2009242018)%20when%20I%20save%20the%20file%20(regardless%20of%20the%20type)%2C%20the%200%20(zero)%20drops%20and%20then%20Deltek%20doesn%E2%80%99t%20allow%20for%20the%20upload.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20had%20our%20IT%20guys%20look%20into%20it%20with%20zero%20success.%20I%20have%20read%20umpteen%20articles%20online%20and%20followed%20everyone%E2%80%99s%20suggestion(s)%20with%20zero%20success.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20going%20to%20ask%20you%E2%80%A6(see%20where%20this%20is%20going%3F%3F)%20if%20you%20might%20have%20any%20idea(s)%2C%20hints%2C%20clues%2C%20or%20any%20magic%20spell%20you%20might%20know%20to%20keep%20the%20leading%20zero%20from%20dropping%20(and%20yes%2C%20I%20have%20tried%20everything%20I%20found%20on%20the%20web).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20attaching%20a%20pre-formatted%20CSV%20spreadsheet%2C%20the%20step%20by%20step%20instructions%20for%20formatting%20it%20(remember%20it%20needs%20to%20remain%20a%20CSV%20format)%20and%20one%20that%20I%20formatted%20with%20the%20leading%20zeros%20dropped%20(columns%20B%20%26amp%3B%20D).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20love%20any%20input%20you%20might%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-308629%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-308916%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20format%20dropping%20leading%20zeros%20in%20a%20%22general%20format%22%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-308916%22%20slang%3D%22en-US%22%3E%3CP%3EIssue%20with%20autoconverting%20has%20a%20long%20history%2C%20you%20may%20check%20for%20example%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F165042%2Fstop-excel-from-automatically-converting-certain-text-values-to-dates%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F165042%2Fstop-excel-from-automatically-converting-certain-text-values-to-dates%3C%2FA%3E.%20But%20that's%20if%20you%20open%20CSV%20file%20in%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20if%20you%20save%20your%20file%20as%20CSV%20with%20leading%20zeros%20Excel%20doesn't%20remove%20them.%20You%20may%20check%20if%20open%20such%20file%20in%20Notepad%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20277px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F64665i79B2446EC25A473B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ELeading%20zeros%20will%20be%20removed%20only%20if%20you%20open%20again%20your%20CSV%20file%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E
tea4u
Occasional Visitor

Happy New Year all!

I have been trying everything I can online with no luck, and am turning to you all for help.

 

Background…one of my work tasks is balancing and entering the details for our company credit card. It requires taking some downloaded detail (from a website) in a CSV formatted Excel spreadsheet, doing some additional formatting, saving it as a CSV spreadsheet and uploading it to Deltek (our accounting platform). Not a big to-do.

 

For 3 years this worked perfectly.

 

About a year ago something changed (I think in Excel) to where in one of my columns, which has been manually formatted (by me, the user) to a “General” format and has a leading zero (example the date 09242018) when I save the file (regardless of the type), the 0 (zero) drops and then Deltek doesn’t allow for the upload.

 

I have had our IT guys look into it with zero success. I have read umpteen articles online and followed everyone’s suggestion(s) with zero success.

 

I’m going to ask you…(see where this is going??) if you might have any idea(s), hints, clues, or any magic spell you might know to keep the leading zero from dropping (and yes, I have tried everything I found on the web).

 

I’m attaching a pre-formatted CSV spreadsheet, the step by step instructions for formatting it (remember it needs to remain a CSV format) and one that I formatted with the leading zeros dropped (columns B & D).

 

I would love any input you might have.

 

Thank you all in advance. 

1 Reply

Issue with autoconverting has a long history, you may check for example https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-val.... But that's if you open CSV file in Excel.

 

But if you save your file as CSV with leading zeros Excel doesn't remove them. You may check if open such file in Notepad

image.png

Leading zeros will be removed only if you open again your CSV file in Excel.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies