Find solution
How to merge multiple CSV files into a single CSV file?

Saving .csv files in Excel

  • 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 -?

  • Answer:

    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.

Mr_Zero at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

It thinks these are formulas. According to http://superuser.com/questions/234997/how-can-i-stop-excel-from-eating-my-delicious-csv-files-and-excreting-useless-dat/240053#240053 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.

kindall

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

odinsdream

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".

atrazine

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.

inigo2

Holy Cow Perplexity! It worked! Thank you. It was there all along. Apparently we are not very smart over here. Thank you!

Mr_Zero

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.

atrazine

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.