Forum Discussion
Solved
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...
I found a solution.
Another function in the same menu - Text to Columns.
This one processed the comma separator correctly. Hurray.
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.
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.
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!
