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]

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

[#3]

This exactly does not work
which puzzles me.

Thank you for response though!

[#4]

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. 

[#5]

That's what I did (also Sergei suggested) but did not work :(

[#6 · Accepted solution]

I found a solution.

Another function in the same menu - Text to Columns.

This one processed the comma separator correctly. Hurray.

[#7]

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.

[#8]

@Sergei Baklan 

Hi,

I'm having a similar problem, but I can post my .CSV file.  These are the steps I have tried:

1. I tried to open the comma-delimited CSV file directly from File Explorer

2. I opened Excel, then tried to open the CSV file

3. I tried to use the Data/Get from CSV option

4. I opened the file in a text editor and replaced all the commas with semi colons

5. I created a blank text file, copy and pasted the text from the CSV file, saved the renamed the file

In every case, when the connector screen pops up, changing the delimiter type does almost nothing.  I think one of them splits the data into 3 columns, but my file is many columns wide.

I'm using Windows 10 with Excel for Office 365

[#9]

Here is a screenshot from the connector:

[#10]

@CannonAli , not sure why such effect. If open your csv file in Excel and save as CSV UTF-8 or CSV MS-DOS in both cases such file is imported correctly by default

At the same time your initial file is also could be imported correctly, but it's required few more steps

 From here

click Transform Data, after that Split Column by Delimeter

Select Semicolon, Ok

Close and load to back to Excel with desired options

[#11]

@Sergei Baklan 

Thank you very much.  I saved it as CSV MS-DOS and was able to import it.  I don't know why, but with each new release of Office, Microsoft makes it harder and harder to use their programs.  I never had this problem with older versions of Excel.

[#12]

@CannonAli ,

You are welcome. Sorry, but I also don't know why...

[#13]

@Sergei Baklan 

I thought Excel parsed the file correctly, but it looks like only some of data was parsed.  The Data is row 4 is all contained in A4

[#14]

@CannonAli 

Yes, I see. On preview stage Excel make some guesses how to transform your data, not always correct ones. It's always better to give it direct instructions of what to do and not rely on guesses.

Other words, query your initial file, click on Transform and directly ask Excel to split your column by semicolons. Steps as in my previous post. When it works.

I repeat here the file which do that.

[#15]

@Sergei Baklan 

Thank you.  Your instructions were spot on.

[#16]

@Sergei Baklan

Hi.. i'am having this same issue, but with an interesting  "effect" . Im using office 2010 64bit windows10. I have tried all the suggestions listed here and spent hours searching for solutions on google (and tried most of them).. i even uninstalled office 2010 and upgraded to office 2013 64bit but still the problem persist. If i open the file .csv file on a different pc ..( with exactly the same office 2013 ) everything works fine.. so i went through .. patches ,, Add-on's .. every setting i could find.. they are the same.. between the two pc's the only difference is the one pc is on win7 64bit and the other win10.. the region settings are the same in both pc's   ..??

I would appreciate so help or advice.

[#17]

Since your data is already in excel use the text to column command use either comma (,) or quotation marks (") as separators.

[#18]

@ihatebills 

Thank you for the reply, i have done that and yes with some manipulating and editing the data, it kinda works (some of the merged data does not keep its formatting or formula) .. the work around is to right click on the merged data in Word and select "preserve formatting during updates"... i do a mail merge with about 25 of these csv files to 1000's of clients ..witch leave this as a poor solution.

it still bugs me that all works fine on one pc but not the other .. although both has the same setting and office 2013 versions..

my final solution is to try and have the same OS's on both pc's ..witch does not make scene to me at all sins this is a MS Office  issue..? and not Windows

[#19]

@Jack_Eijbers 

Okay, well this is most interesting ... i have reinstalled Windows10 64bit , MS Excel (2013) is working fine now..??

i still have no idea why or what caused the problem. Clearly Windows OS had or have a integration outcome in MS Office.

[#20]

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.

@Filip Vrlik