Join 3,572 readers in helping fund MetaFilter (Hide)


Saving .csv files in Excel
June 30, 2011 1:06 PM   Subscribe

About a dozen people have tried to solve this. All failed. Exporting from Excel to .csv causes cells to show #NAME? instead of the actual value.

The file was created by exporting the product options from a shopping cart application. This is a .xlsx file. It is the only way that I could figure out to keep the data from being altered.

http://www.mediafire.com/?zhhlz03hlxh55hb

Column O contains the product model number extensions. So on the front end if you select Natural Gas or Propane it appends a -NG or -LP to the root model number.

An easy way to make bulk changes to products/options/pricing/etc. is to export the corresponding file and change the data in Excel and then import the updated file.

The cart exports a .cvs files and will only import .cvs files.

When you open the exported file in Excel all of the occurrences of -NG/-LP have been changed to =-NG/=-LP and display as #NAME?. This happens even if you format column O to be text. I can do a replace and remove all the =, however when I save it as a .cvs file they are altered again.

I have tried this with Excel, Open Office Calc and Google docs. All of them produce the same results.

Does anyone know how to take the above file and save it as a .cvs without it adding a = to every cell whose contents begin with a -?
posted by Mr_Zero to Computers & Internet (7 answers total) 1 user marked this as a favorite
 
It's not the exporting that's causing it to change to #NAME?. The export works fine. You can verify this by opening the exported .csv with something like Notepad rather than Excel.

The problem comes when you try to open the .csv in Excel. One workaround is to cut and paste the .csv contents (from a text editor like Notepad) into Excel, rather than opening the .csv directly in Excel. Then you can use the "text to columns" function, specifying that the column in question is text. That works fine for me.
posted by Perplexity at 1:16 PM on June 30, 2011 [2 favorites]


It thinks these are formulas. According to this answer over at superuser.com, a similar problem was solved by renaming the file to .txt instead of .csv, triggering Excel's text file importer. This importer gives you the opportunity to specify the types of columns, and you can choose to make the affected column text.
posted by kindall at 1:17 PM on June 30, 2011 [1 favorite]


I was about to say what kindall says -- if you rename is as .txt, then do file->open, it lets you say it's a comma delimited file. Like kindall said, make sure on the import to specify that the column in question is text, otherwise it still screws up.
posted by inigo2 at 1:20 PM on June 30, 2011


Have you tried the trick of adding a ' mark as the first character of that column? i.e.,:

'-NG/-LP
instead of
-NG/-LP

?
posted by odinsdream at 1:22 PM on June 30, 2011 [1 favorite]


Holy Cow Perplexity! It worked! Thank you. It was there all along. Apparently we are not very smart over here. Thank you!
posted by Mr_Zero at 1:32 PM on June 30, 2011


In Excel 2007, open a new blank file. Go to the data tab, press the From Text button in the Get External Data menu.
Select "delimited", choose "comma" as the delimiter, select the column that's causing the problems and change the data type to "text".
posted by atrazine at 1:33 PM on June 30, 2011 [1 favorite]


Oh yeah, what kindall and Perplexity suggest work as well. The problem is that if you just try to open a .csv file, Excel thinks it knows how to open it without you telling it anything about datatypes.
posted by atrazine at 1:36 PM on June 30, 2011


« Older Custom searching Flickr with B...   |  What do you think this white f... Newer »
This thread is closed to new comments.