Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a large database with customer addresses that was exported from an SQL database to CSV. In the event that a company has a comma in their name, it (predictably) throws the whole database out of whack.

Unfortunately, there are so many instances of this (and commas in the second address line) that the whole CSV (~100k rows) is a huge mess. The obvious fix is to export the data again in a different, non comma reliant format, but access to that SQL database is more or less impossible at the moment... I've tried a few tools and brainstormed about combining things to fix this, but I figured asking couldn't hurt. Thanks!

share|improve this question
CSVs fields with embedded commas are supposed to be surrounded by quote characters. Can you post an example row that has an extra comma? – Dour High Arch Jun 14 '10 at 15:12
Yes they are. I'm unsure how they got this to output in this manner, but they aren't surrounded by quotes: 66666,,,13,Name Name,Product-stock,Name Data Systems, Inc.,666 Raod Rd ,Suite 100,City,OH,43666,US,666-666-6666,14,1,1 – 分かりますか Jun 14 '10 at 15:17
up vote 1 down vote accepted

In my experience, trying to fix this from your current data, will take any time you want, and there's no warranty, that your result is identical to the initial data. It might be easier, to wait until the database is available again.

The basic problem is, that the conversion to a simple CSV format is not bijective - there's simply no direct mapping back from the CSV file to the original data. You can try to cut this problem down using some heuristics, but it will need a certain amount of scripting or programming. It also depends on the platform and tools you have available and - last not least - on your skills.

You might have some records, where no field value has an embedded comma. Extract these first, even if there few, it's a starting point.

May be you find a heuristic to distinguish at least some of the "embedded" commas by field separating commas.

You could try to identify data values of characteristic columns, e.g. mail address, phone number etc. Working from there, you could narrow down the number of columns which need more careful inspection.

Proceeding stepwise in such a manner should allow you to transform your data into a format that is better suited, e.g. using tabs as field separators.

You should also think about extracting only some columns and a unique key value (record number?) in the first step, and later matching the different pieces.

Write down, document, keep a record of what you are doing. Otherwise you will make things worse instead of better.

Good luck!

share|improve this answer
Very thorough. Thank you! – 分かりますか Jun 14 '10 at 15:56

As a programmer, my approach would be to create a small app which parses the file line by line and checks for the number of commas that are in the line (you should, I assume, know how many a correct line will have)

If it matches the expected figure then output to a new file with tabs replacing commas.

If it doesn't match, display the line with an option to exclude which commas should be converted to tabs and ouput based on that selection.

This should then give you a tab separated file with commas in some address fields.

share|improve this answer
+1 counting the commas in each row. Hopefully most of the rows are good, so you can start off by sorting the good ones from the bad. – Jarvin Jun 14 '10 at 15:12
This seems the most sensical solution. I'm without much programming expertise, is there anything you could recommend? – 分かりますか Jun 14 '10 at 15:20
@fi-no - find a friendly neighbourhood programmer? :) Seriously though, if this is something that is important to your business then there are many coder freelance sites out there where you can tender for quotes based on your requirements. – Shevek Jun 14 '10 at 15:37
True enough. Thanks! – 分かりますか Jun 14 '10 at 15:42
+1 to the neighborhood programmer. Especially if it is important to your neighborhood. This is especially trivial in PERL, PHP, or any other scripting language. Plus, you'll find the guy useful for in the future. . . – surfasb Jul 22 '11 at 0:23

The first choice is to just wait until you have access to the database again.

In case that is not practical, Excel is your CSV friend (if you are not a programmer)

Here is an Excel method I have used:

  1. Import the CSV into Excel so that the rows of your file with the correct number of commas end up in the correct number of cells in the Excel row. ie, Say you have seven fields with seven correctly placed commas that will be parsed by Excel into seven cells with the correct CSV import settings.

  2. Now take your excel sheet and go to the top row and one cell to the right of a correct row's last cell. ie, If you have 7 fields, these will span from "A" to "G". Go to column "H"

  3. On the Mac, hit Command + down arrow; On a PC, Ctr + down arrow. This will skip over all the blanks and stop at the first cell with content. Manually fix the row and move on to the next.

  4. If you have any rows that too few fields (commas) you can find these by going to the first cell to the left of correct cell's last cell. ie, if you have a correct row spanning A - G, go to column F and repeat step 3.

With 100,000 rows, this is only doable if a high percentage of your rows are correct, but you'll be surprised how quickly you can repair the file this way. You can now use Excel to save the file in a correct form of CSV.

Best of luck...

share|improve this answer
Excel is limited to 65,536 rows per worksheet so you'd need to split the CSV file up before attempting this. – Shevek Jun 14 '10 at 22:27
Correction - Excel 2007 upped this limit to 1,048,576 rows – Shevek Jun 14 '10 at 22:27

There is no obvious solution to this problem because if fields that contain commas aren't marked some special way (and you did not mentioned that) it is basically impossible for a computer program to determine if a comma is in address or it is not. You can still use some heuristics (like "if you have space before comma, it's probably in address") for rows with extra commas, but they are heuristics, and they will miss. (The 'space' heuristic is a good one to start from anyway).

I can only help by writing a shell script which will seek erroneous lines and offer to edit them. If you need anything like that, post a note.

share|improve this answer
It's nowhere neat 'basically impossible' for a computer to figure out whether the comma is within an address. I write console applications to do this all the time, but each solution is very specific to the application. – Ehryk Nov 10 '13 at 4:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .