How to convert excel file into csv format with no changes on the comma placement?

How to convert excel file into csv format with no changes on the comma placement of the values in the cells?

The problem is when I save the xls file into csv format the comma placement changes. For instance:

xls format: 1870,1876,1900,1924 (the correct format)
csv format: 1,870,187,619,001,924

How can I fix this without the need to do it manually?

I badly need your help. Any assistance will be highly appreciated.

Thank you!

The thing you have to remember is that a CSV file is not a specialized file format. It is an extension we assign to regular ASCII text files in which commas are used to separate fields. A few notes:

1) Since it appears that you have it correctly in Excel at the "beginning", that suggests to me that you have done something (an apostrophe at the beginning of the cell maybe?) to tell Excel that that cell contains text that could possibly be (but you don't want it to be) interpreted as a number. When you save as a CSV file (or other ascii text file), the information Excel needs to know this is supposed to be text gets lost.

One step I use in debugging this sort of problem is to read the file using a regular text editor (like notepad). When you open a CSV file in Excel, you get Excel's interpretation of what the file contains. When you open a CSV file in a text editor, you see the file without "interpretation." When you open your file in a text editor, what do you see? Is this format what you are expecting? If it is not important to be able to open the file in Excel, this will be a better picture of what the CSV file looks like and whether it will work in the destination application.

2) As I noted, when you open a CSV file in Excel using the file - open command, it tries to make sense of the fields as best it can, which is sometimes wrong. If it really is important for Excel to read the file correctly, you might try opening the file using the import external data command. This will bring up the text import wizard which will allow you to specify to Excel that the column in question is to be imported as text rather than the default "general" with its tendency to identify numbers where possible.

Hello AnnaMarie88, and welcome to the forum.

Please notice that I have removed whole post quotes from your posts(s). Please read the following forum rule and keep it in mind for your future posts. Thanks.

Rule 12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Noted! Thanks for the reminder Cutter.

I don't think I can give a specific procedure, because I'm not sure I understand all of your requirements.

How important is it that the entire text string be in one cell in Excel? Is this what each record should look like in the text file?

Please Login or Register  to view this content.

The easiest way I know to create CSV files from Excel is, like ChemistB suggested, to have each value in its own cell. Then Excel will supply the commas when you do the save as.