Excel and CSV files adding ' to leading zeros

[Rodinski]

Hey, 

 

I have to constantly convert an excel sheet with hundreds of barcodes as the CSV format is adding a ' to all barcodes with a leading zero. The format needs to be CSV or else the program we're using won't recognize it. 

 

Now I'm thinking it won't be an issue but I was wondering if there is a better way to do this. Excel also likes to convert some barcodes to scientific notation. Having to reconvert them all the time introduces issues such as removing some of the barcode and or adding zeros to the end. I'm trying to find a way around this so the next guy who doesn't have a background in this field modifies the reference data and ends up messing up all the barcodes. 

 

Any help would be appreciated. 

[Dedayog]

19 minutes ago, Rodinski said:

Hey, 

 

I have to constantly convert an excel sheet with hundreds of barcodes as the CSV format is adding a ' to all barcodes with a leading zero. The format needs to be CSV or else the program we're using won't recognize it. 

 

Now I'm thinking it won't be an issue but I was wondering if there is a better way to do this. Excel also likes to convert some barcodes to scientific notation. Having to reconvert them all the time introduces issues such as removing some of the barcode and or adding zeros to the end. I'm trying to find a way around this so the next guy who doesn't have a background in this field modifies the reference data and ends up messing up all the barcodes. 

 

Any help would be appreciated. 

How are you doing this currently?  What system are the barcodes coming from before hitting Excel?

 

You can add leading zeros thru formulas, then make a macro to do it.

 

 

[Rodinski]

I'm using go canvas, but modifying their lists through excel. 

 

Turns out the ' is needed for leading zeros in order for it to read properly. But setting up a macro, I'll look into that. 

[Dedayog]

19 minutes ago, Rodinski said:

I'm using go canvas, but modifying their lists through excel. 

 

Turns out the ' is needed for leading zeros in order for it to read properly. But setting up a macro, I'll look into that. 

Yes, without the ' Excel will treat the cells as numbers, and drop the leading zeros.  The ' means text so it'll keep em.

 

 

[Flavius Heraclius]

This is the sort of thing that is trivial to do with some command line utilities. I'm not clear from your question if you're trying to add the ' characters to the CSV before importing into Excel, or to remove them from the CSV after exporting from Excel, but either way it would be possible to set up a command/tool that you can pipe the CSV through before importing/after exporting.

 

I understand that this isn't a desirable solution for a lot of people, but if you're interested I could probably come up with something for you.

 

As far as the scientific notation thing, I've had that annoyance with Excel a lot myself. It really thinks it's smarter than its user when it comes to data formats. My favorite will always be the genes that scientists renamed to avoid Excel formatting them as dates automatically (which has happened in quite a few published papers without the authors noticing).