I am trying to save a file from xls to csv. One of my columns in the xls file is filled with numbers separated by commas but is saves as a text. Whenever I save the file as a csv it converts the numbers saved as a text to be saved as numbers so it screws my commas up and therefor messes up the entire file. How can I fix this?
Please post an example file so I don't have to guess. Just column 1 is ok...
---
Ben Van Johnson
Here is the sample. I need it to look exactly how it does in the xls file in the csv file. Whenever i try and save it as a csv file and then close and re-open it, it converts it to "numbers" instead of "text"
Instead of "opening" the CSV file with Excel, you need to start Excel with a blank sheet then import the CSV file as text using the Get External Data option of the Data Menu.
---
Ben Van Johnson
I have the newest version of excel. Can you walk me through how to do it on this version? i saved the file as a xls originally because I needed to send it out via email. I cannot seem to find "get external data" in the data menu
actually I think I got it but when it gets uploaded there is " " around the numbers. Is there any way to get rid of the " "? I have about 4000 rows, otherwise I would just do it myself by hand.
O.k., I'm not sure what you mean by "uploaded"; we are either saving as... or importing as... ...
When the file is saved as a CSV file and viewed in Notepad, the data will have straight quotes around them; however, when imported into Excel as text, the quotes don't appear.
If you are concerned about the quotes in the CSV file, you just do a "Replace All" op on the data to remove them all at once.
Note that I tried importing as text both with and without the quotes with no problems. If I imported as General, though there were still no quotes, Excel converted the data to numeric with commas separating the digits into groups of three.
---
Ben Van Johnson
I am able to do everything you are saying but when I save the csv file without the " " and open it again to view the file it converts that column into numbers not text. This only happens in the csv file and not the xls file. I am importing all of these things into the back end of my website and it keeps reading it as numbers. I am not sure why it will not save as text. I appreciate all the help you have been giving me.
I need excel not to convert the data to numeric with commas separating the digits into groups of 3. The way I have it is very specific, with sometimes 4 numbers then a comma, etc.
We seem to be doing something differently, I'm not sure what, though. You said:
Open and import are not the same, so please see if this helps any:...when I save the csv file without the " " and open it again...
---
Ben Van Johnson
I think everything is all fixed now. I really appreciate your help.
Hi,
Really "Forum Guru" you have done a great job.
Few days back I have also faced the same problem while converting my .xls files in CSV format. At that moment i just searched on Google and found a simple to use tool its Birdie XLS to CSV Converter.
It helps me out to convert my large group of XLS files to CSV. I must refer this tool to others as you can also try this, get the software from - http://www.birdiesoftware.com/xls-to-csv/
Hope it is also useful for you....good luck buddy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks