A CSV (Comma Separated Values) as it’s name implies is supposed to be a file in which each line represents a row of data and each row has a series of values which are separated by commas.
Well, I had a client share with me a CSV in which all the values were separated by semi-colons (;). I thought, makes no difference, go into Excel, Open, … and there were no options anywhere to apply settings for the Open/import process. Thus, open opening the file, I had a big mess on my hands.
So what is one to do exactly?
Solution 1
Instead of using the File -> Open … process, import the data instead.
- Open your workbook or start a new one
- Click on From Text (from the DATA tab, in the Get External Data grouping)
- Follow the prompts (be sure to specify your delimiter when you are presented with the option to do so)
Solution 2
Another option which avoids the entire issue, so you can simply open the file normally,is to add
sep=;
as the first line in the CSV file. Obviously, you would change the semi-colon to whatever separator is used in your CSV file.
Now, you can merely open the file and Excel will now recognize the proper separator.
Solution 3
Another quick and dirty solution, which can be automated if you’d like, would be to merely perform a Find and Replace on the semi-colon to replace it with a comma throughout the file.
Solution 2 is a neat trick! Thanks for sharing.
I was building a utility for a client to process a multitude of such ‘CSV’ files and I simply automated inserting that line into the beginning of each CSV file and then everything fell into place. It always amazes me how much more there is to learn. All these hidden tricks.
Solution 2 would seem to render the file unable to be opened in anything BUT Excel from now on.
Solution 3 is a bad idea. If commas aren’t being used as separators in the CSV, they could still be present. For instance:
Title; Pages; Price
Eats, Shoots and Leaves; 300; 13.99
Inky, Blinky and Clyde: Enemies of Pac-man; 250; 20.00
Replacing the semi-colons with commas and then trying to parse the CSV via commas will result in unequal numbers of columns for different lines, splitting the book titles at each comma.
Even worse, a file could be using semicolons as the separator because it’s from one of those crazy European countries who have never heard of a “decimal point” and do this instead:
Item; Price
garbage can; 17,99
gum; 0,99
Commas could also be present as separators in integer numbers, etc.
So, best to stick with option 1: assume the creator of the file knew what they were doing when they chose a semicolon and make the parser aware of this fact. Mucking about on your own can cause all sorts of problems with importing the data.
Now that I think about it: there’s always option 4, “Use LIbreOffice”, whose file open process does ask you about CSV separators…. 😉
Joseph,
Yes and no. It all depends on what you are doing. It always comes down to your needs.
As for “assume the creator of the file knew what they were doing” in my case that isn’t so, nor is it in many cases. The files were produced by another system and the client had no clue and couldn’t understand why Excel simply couldn’t open it properly. Assumption are always dangerous!
The manual process simply wouldn’t work for my use case at the time. I needed to process multiple CSVs in an automated manner. That said, it is all a question of knowing your data. I knew there was no risk of the text containing semi-colons elsewhere, so there was no issue with adding sep=; to the start of the file. My client only uses Microsoft Office, so he doesn’t care about it being incompatible with other application. Beyond which the whole point was to import it into Excel, once there it is saved as an xlsx which is fully compatible.
As for option 4, it is simply unrealistic to expect people to switch applications just to open a CSV. That said, I am a supporter of alternative MS Office suites, LibreOffice being a great option and I have several clients running their businesses on it! With all the continual issues with Microsoft products (Windows, Office) switching to such alternative is a smart move IMPO, but not just to be able to open a CSV.
You could also add Option 5, re-export the data properly using the comma separator!
Like everything else, I am simply sharing information that helped me. It won’t serve for all situations but may be most useful in other. As with all things, test, test, test and see if it works to address your needs, or not.