Forum Discussion
CSV not parsed into columns despite Get Data delimiter set
My Excel does not parse CSV file correctly. The separator is comma (,). Even if I use the Get Data tool and set the delimiter there to be COMMA I still get everything in one column. Obviously, I want the result to be data parsed in columns (name, email, date, etc).
Do I need to change regional setting? Where? I usually work with files where comma is used as a decimal separator so I do not really want to override global setting.
This yet another CSV story, I know. I googled and checked other posts first yet could not fix it.
I cannot attach the file itself because it contains personal data.
The CSV is generated by a Wordpress plugin.
I am using Windows 10 Pro on a laptop. Using Excel 2016 from our company's Office 365 Premium license.
Thank you!
I found a solution.
Another function in the same menu - Text to Columns.
This one processed the comma separator correctly. Hurray.
34 Replies
Filip Vrlik I have come across a similar situation where I added some rows to a csv file and found that the added rows as well as the original header row is parsing into columns correctly, while all the original data ended up in a single column. The delimiter was a comma (,). I attempted a number of fixes and eventually found that the only difference between the rows that were parsing correctly and those that weren't, was that the files that weren't parsing correctly had some numerical values surrounded by " " (e.g. ...,"50",...). This symbol (") also appeared as the only 'quote character' in the advanced settings. I found that once I removed all the " " from the data that was not parsing correctly (e.g. ...,50,...), the entire sheet functioned properly.
While coming to this fix, I also noticed that selecting the 'quote character' as 'none' in the advanced editor had the effect or parsing all the original data correctly, but returning null values for the new rows and the header row.
Apologies for any incorrectly used jargon, I don't have a background in tech.
- Lemme give y'all a simple way rather than making it complicated, by messing with VBA, settings, versions, etc.:
File is .csv (perhaps because it's been renamed from a .txt file) but Excel opens it with all columns in each row in a single column (i.e. not recognizing separators).
1. Make a small dummy file (even with 1 record) with the same number of columns, and comma separators.
2. Open Excel first, then navigate to the file and open it as comma delimited.
3. Save it as type name.csv.
4. Go to DOS (yes, DOS), or CMD and type in this:
Copy name.csv + yourfilename.csv. (if you have many files, you can make a .bat file with many 'copy' statements.)
5. Go back into the file, remove that first dummy record and resave.
You've just 'juked' Excel into forcing the appended file to follow the same formatting as the first. Hi!
I'm facing a bit different problem with my .csv file. I write on my excel .csv file with another application. I'm writing on string format and can separate string to different columns with "," symbol. I would like to know how could I separate the string to different rows? What is the symbol for this one? Only tips I could find is if I would open it with this editor thing, but as I said this is not possible for me. Also could change my settings on this computer control panel but would prefer not to do that one either.
Thank you for your replies and please ask if I'm representing my problem unclear.
The easiest way is by Power Query. Step by step instruction is here https://sfmagazine.com/post-entry/november-2017-excel-split-delimited-data-into-new-rows/
I found a solution.
Another function in the same menu - Text to Columns.
This one processed the comma separator correctly. Hurray.
- How to translate data from .csv from Ppl and CoinB downloads to columns and rows. Open the .csv file in Excel. Confirm it is not separated into columns. Select only the first commas separated value data and then press Data on the menu bar, then press Text to Columns. Answer questions in the dialog box selecting commas as the separator to translate it to Columns and rows.
Filip, good to know you found the way. However, in general it could be interesting to understand why Get Data doesn't work. Perhaps some special non-printable symbols at the beginning of the file which Text to Columns definitely ignores, perhaps something else.
SergeiBaklan i have my regional setting set to comma but when i open a cvs file it wont sparce into columns...i can do the text to columns to solve this but then when i save as csv comma delimited it saves with semicolon delimeter..what sould be wrong? thanks
Hi Filip,
In general connector recognizes the delimiter, plus you may change it on first step
If that doesn't work better if you attach sample file - just couple of rows will be enough where you could substitute personal information on something abstract
SergeiBaklanHey, where is this option in MS excel ? which tab ?
- Hi, this is in Data Tab.
- This exactly does not work
which puzzles me.
Thank you for response though!I don't think I am answering this any different but in Excel 2016 did you go to the Data tab, then select From Text/CSV?
Then just select the CSV file you want.
