[benvanderbeek]

My company's part numbers are in the form 00-0000. After enough conversations about how to convert back to this format from the date Excel changes it to, we've finally just decided to change our sku format. A rolling change though, so we'll still be dealing with it till the current 5k sku's are all EOL.

I also hate the scientific notation default, in addition to the leading zero. Guess what, UPC's exist and no one wants them in scientific notation.

[mpyne]

Format the cell as Text first? Enter the UPC with a leading ' to force intepretation as Text?

If you're changing SKUs anyways you may want to change it to have a letter to that Excel "guesses" correctly by default.

[kyllo]

But when someone sends you a CSV file (a very common format for database exports and EDI), Excel does the type conversions automatically when you open it. You don't get a chance to change the cell format to Text beforehand. The ' workaround is a huge time-waster if you are dealing with a large amount of data, plus it screws up the file for use outside of Excel.

There really needs to be an option to turn off all type conversion globally for all files in Excel.

[srdev]

Rather than open it directly in Excel, open a blank workbook and use the data import functionality. This lets you specify the type of each column.

Its not a perfect solution, but its a passable work-around.

[kyllo]

This works when you have control of the data.

The real problem arises when you ask someone to send you data in CSV format. If, in between exporting it from their database and sending it to you, they happened to open and save it in Excel, you will get corrupted data. Usually the sender is blissfully unaware of what Excel's automatic type conversion does to their data.

CSV has been made unreliable as a format for data exchange between companies (aka EDI) largely because Microsoft decided that CSV files should always be opened in Excel by default in Windows. At the very least they should turn automatic type conversion off for CSVs.

[tgflynn]

Not sure if this would work but have you tried quoting the values in the generated CSV ?

[kyllo]

Excel ignores double quotes around the fields, it just uses them to escape commas inside the fields.

The official way to do it is to insert a single quote at the beginning of every field that you don't want auto-converted. In practice this is a time-wasting pain in the neck and ruins your data for use outside of Excel.

[benvanderbeek]

The problem isn't the lack of workarounds. The problem is that we have all departments - Purchasing, Marketing, Operations/Warehouse, Sales, Finance, IT - working with sku's in spreadsheets. Every single time, every single person needs to know the workaround(s), and as other people are mentioning, you then have to hope it's not somehow saved as something else. And also as mentioned, if someone else has worked with the data before you get it, good luck restoring it to what it was before.

But yes we'll probably stick a letter in there.

[EEGuy]

I've had success getting rid of the scientific notation by just widening the column. Maybe that doesn't work all the time, I don't know.