09-04-2018 08:29 AM
09-04-2018 08:29 AM
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.
09-05-2018 02:57 AM
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
09-05-2018 02:13 PM
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.
09-06-2018 02:44 AMSolution
I found a solution.
Another function in the same menu - Text to Columns.
This one processed the comma separator correctly. Hurray.
09-06-2018 02:51 AM
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.
06-04-2019 02:10 PM
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
06-04-2019 03:19 PM
@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
click Transform Data, after that Split Column by Delimeter
Select Semicolon, Ok
Close and load to back to Excel with desired options
06-04-2019 03:43 PM
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.
06-04-2019 04:41 PM
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
06-05-2019 02:44 PM
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.
07-21-2019 03:04 AM
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.
07-21-2019 08:32 AM
07-22-2019 04:02 AM
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
07-22-2019 11:45 PM
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.
10-02-2019 12:30 AM
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.
10-02-2019 02:23 AM
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/