Excel Auto Formating

**Wiredkevin** · 12-01-2008, 07:49 PM

If this has been asked before I'm sorry but this has been driving me nuts for awhile now.

I run an online store where all the product details are handled by a .CSV file and naturally all the information has to be exact or it won't work at all especially UPC codes and product codes. However whenever I open the .CSV file to edit anything it auto formats several bit of information including several UPCs and product codes that begin with one or more 0's. This among many other problem I have had are all caused by excel's auto formating "tool" and I find it of absolutely no use. Is there any way at all to disable this "tool" so it will stop messing with my data every time I need to make a product change? (I have tried using the '0 trick but when I save the file and open it again later all the '0s are gone again)

I am running Excel 2007 on Windows Vista SP1

**JBeaucaire** · 12-01-2008, 08:45 PM

Record a macro of you IMPORTING that CSV file and using the format options during the import to make sure the UPC column is imported as a text column, etc. Then name that macro AUTO_OPEN and save the basically blank file....nothing in it but that macro.

Now every time you open this file, it will import your CSV file properly. You can edit, then export the file. Close your original (macro) file without saving anything since you don't need to.

**Wiredkevin** · 12-02-2008, 01:22 PM

Ok, makes sense but every time i set it to text it turns all of the UPC codes into scientific notation [898404000067 turns into 8.98404E+11]. The only way I've been able to fix this is by setting it to number format but that brings back the "0" problem.

**JBeaucaire** · 12-02-2008, 01:56 PM

hmm, post one of the import sheets and I'll give it a try.

**Wiredkevin** · 12-02-2008, 02:17 PM

This is a list just containing the part numbers and UPC codes.

upc codes.zip

**JBeaucaire** · 12-02-2008, 03:31 PM

That imported flawlessly for me.

1. Data > Import External Data > Import Data, select UPC.csv
2. Choose delimited...Next
3. Check off Comma and Space and Treat consecutive delimiters as one...Next
4. In the Data Preview click on the first column, then select Text in Column Data Format, them click the second column and click Text, then click the third column and select Text
5. Click Finish and put it on your sheet

**Wiredkevin** · 12-02-2008, 04:13 PM

Ahh I get it now, I was importing it the wrong way, I wasn't using the import tool but was simply loading up the file then running the macro. Although for mine if I deselect the Comma Delimiter it merges all the data into a single column, once I use the comma delimiter if separates them properly.

Thanks for the help, I'll be able to use this to fix all the other problems I was having as well.