1

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!

3
  • CSVs fields with embedded commas are supposed to be surrounded by quote characters. Can you post an example row that has an extra comma? Jun 14, 2010 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,[email protected],[email protected],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, 2010 at 15:17
  • Ok you can detect 1 validation failure because the city name would fail both a length test and there are ONLY 50 states so if its not on a said list you have a data record that is wrong. Also ZIP codes are 5 digits so assuming OH can be stored in said field then if length =2 then we have detected a failure.
    – cybernard
    Mar 1, 2022 at 18:02

5 Answers 5

1

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!

0
3

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.

4
  • +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, 2010 at 15:12
  • This seems the most sensical solution. I'm without much programming expertise, is there anything you could recommend? Jun 14, 2010 at 15:20
  • 1
    @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, 2010 at 15:37
  • +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, 2011 at 0:23
1

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...

2
  • 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, 2010 at 22:27
  • Correction - Excel 2007 upped this limit to 1,048,576 rows
    – Shevek
    Jun 14, 2010 at 22:27
0

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.

1
  • 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, 2013 at 4:53
0

I know this might sound a little basic, but it saved my bacon. If it's a name, it is likely a comma followed by a space. But an exported CSV won't have a space after the comma between columns. So if you do a find and replace for ", " it might get rid of the unwanted commas.

1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 1, 2022 at 16:08

You must log in to answer this question.

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