Best Answer

Excel wizards wanted. Cells with leading zeros removed when converting to .csv

  • 29 March 2023
  • 6 replies
  • 53 views

Userlevel 7
Badge +5

Hello,

Hoping someone can illuminate me.

I have usernames that begin with one or more zero(s) for one of my EE tenants. When normal reports export as .csv, these leading zeros may be removed. If we manually export the data we can use .xls and that keeps the zero(s). If I format the username column as Text, this helps keep the leading zero(s).

The issue is when I go to upload these spreadsheets back into the LMS. When I convert the .xls to .csv, the zeros are removed, even if the column is formatted as text.

I was looking at the Automation App, and I think I’ll run into the same issue of needing a .csv file.

Any help is much appreciated!!

icon

Best answer by Bfarkas 29 March 2023, 21:48

View original

6 replies

Userlevel 7
Badge +3

So typically, the 0’s are still there when initially saved as a CSV, the problem becomes, if you open the file agian in something like excel, they get removed. If you are not careful it is very easy to fall into a cycle of save the csv out with it formatted. (when you save to CSV the formulas for save as text are lost), and then open and lose them again. If you save the CSV out and open it in something like notepad or notepad++ you will see it still there.

It’s a tricky thing of dealing with excel and csvs and 0’s

Userlevel 4

We have experienced the same issue and resolved it by exporting the file as a csv, so no conversion is needed. 

Userlevel 7
Badge +5

@BfarkasSo this is just a ‘display’ problem? The data doesn’t really get changed ??

I opened a file I has converted to .csv (where I thought I had lost the zero(s)) in Sublime Text, and the zero(s) were still there!

I’ll test to see if Manage CSV still sees the zero(s).  If yes, then I’m good.

 

Userlevel 7
Badge +3

Right, so excel when you reopen the csv removes them, if you use a text editor or something that does not actively modify, they stay. it’s a bad excel > csv > excel checking cycle :)

Userlevel 7
Badge +5

All … we did find another solution that works pretty well for our novices.

https://help.salesforce.com/s/articleView?id=000387891&type=1

You can save multiple times while you still have the file open and you can upload even after you close it - it's just when you reopen the file the formatting erases. SO I think good rule of thumb is just to keep it open when you upload and close after upload is completed successfully.

Userlevel 7
Badge +3

All … we did find another solution that works pretty well for our novices.

https://help.salesforce.com/s/articleView?id=000387891&type=1

You can save multiple times while you still have the file open and you can upload even after you close it - it's just when you reopen the file the formatting erases. SO I think good rule of thumb is just to keep it open when you upload and close after upload is completed successfully.

Yup thats what I was saying, you can close it too, its not the keeping it open, its the re-opening, and you end up in a bad user cycle that way. 

The other thing I often do is build an excel workbook as an input engine, and then have a sheet that is the proper formatiting/output and just save as and switch to csv and never open, but its all avoiding the same problem of excel opening a csv again.

Reply