CSV not parsed into columns despite Get Data delimiter set

[Filip Vrlik]

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!

[#2]

@Sergei Baklan

[#3]

@StudentFI 

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/

[#4]

@Sergei Baklan 

hi, I have a very similar problem. We run Office 365 in our office on Win10.

When I import the file using Get Data TXT/CSV on my computer the data is immediately parsed in columns.

When my colleague does it ! No chance, doesn't matter which separator we use, the data definitely stays in only one column!

This can not be a problem linked to the file as I can import it properly. 

Do you have an idea Sergei?

[#5]

@Banza2170

I had a similar problem but as Scott said earlier you can import it all in one column, then use the text to columns command in the data tab in excel. It's in the data tab on a little to the right of the middle. try that

[#6]

@Sergei Baklan 

I had a similar problem ans solved it with the text to columns command in the data tab. then I notices that some rows ha less columns other had more than others even though they are supposed to be the same length. I traced back the problem to when i first imported the data, apparently when importing the data the lines of my file were cut short in excel, like that there is some king of cap for how long the line can be when importing data. Do you know any fixes for this? 

[#7]

@omer_babiker 

I figured it out, if everything is loaded in one colomn you can go to the transform data button as @Sergei Baklan showed earlier, then specify the delimiter it worked for me.

[#8]

@Sergei BaklanHey, where is this option in MS excel ? which tab ?

[#9]

Hi, thanks. Yes this solved the problem. Thanks for your anwser.

[#10]

Hi, this is in Data Tab.

[#11]

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.

[#12]

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.

[#13]

@Sergei Baklan 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

[#14]

@Pffft this is rather complicated as solution, when all you need is convert the data from the function included in Excel.
Once data imported and in only one column, Go to Data>Convert select the options as needed.

That's all folk!

[#15]

HI  @Banza2170

this does not solve the issue (just changes the view)as I then need to upload the csv file to another app and it keeps being saved with the semicolon....

thanks, regards 

[#16]

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