Forum Discussion
CSV not parsed into columns despite Get Data delimiter set
I found a solution.
Another function in the same menu - Text to Columns.
This one processed the comma separator correctly. Hurray.
I found a solution.
Another function in the same menu - Text to Columns.
This one processed the comma separator correctly. Hurray.
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
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?
- Hi, thanks. Yes this solved the problem. Thanks for your anwser.
I figured it out, if everything is loaded in one colomn you can go to the transform data button as SergeiBaklan showed earlier, then specify the delimiter it worked for me.
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?
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
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
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
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.
Here is a screenshot from the connector:
