I have a few lists of 2 million rows or so consisting of:

Name, Address, Phone Number

but these have errors like multiple commas in a row.

When I import these into postgres as .txt and comma delimited it shows errors "Extra data after column Phone". I then have to go find the line manually, edit out the extra comma, then reupload/repeat. It's been 7 days and I'm still working on the same list and I'm not even past 250k records. This is really obnoxious and there has to be a way to fix this.

Thoughts and suggestions? Don't mind paying for good software.

Maybe there is a program similar to Excel for this kind of work? Something that can handle millions of lines with similar functions/tools as Excel that will allow me to edit this large data sets easily? Excel is good for my datasets that are less than 1 million lines, but can't open larger ones.

I tried the delimit and emeditor editors, but they didn't work for me.

  • 2
    "I" is always uppercase on English. – peterh - Reinstate Monica Jul 18 '17 at 12:36
  • @peterh thanks for the Input beter it really solved my problem, you truly are a genIus. – RayCrush Jul 18 '17 at 15:24
  • 1
    Sorry for the inconvenience, but I am really shocked on it. Half the internet simply writes it falsely, and don't bother. I never understood it. Why is it so hard to press the shift key? – peterh - Reinstate Monica Jul 18 '17 at 15:55
  • maybe stupid suggestion but have you tried opening txt file in an editor like Sublime text or Atom and just replacing two commas with one in the file? i know both Atom and Sublime Text will open very large files. – jmh Jul 18 '17 at 17:09
  • I've seen performance issues with Sublime on large files, not familiar with Atom. – Matthew James Briggs Jul 19 '17 at 8:19


Create an extra table, with two columns:

  • a primary key of type SERIAL (or GENERATED … AS IDENTITY in Postgres 10 and later)
  • text column to contain the entire row from import data, unparsed

Import your rows.

Now search for the patterns of bad data, such as too many commas or double commas. If possible, write UPDATE statements to fix. Or use an admin app such as pgAdmin to edit the data. Or write a little app in Java or whatever to make changes. Or write a GUI app in a toolkit such as Vaadin.

| improve this answer | |

I would probably use Notepad++ and try to do this with a regex find and replace. Here's the top Stack Overflow question on the topic of regex find and replace in Notepad++. https://stackoverflow.com/questions/20286068/how-to-replace-all-strings-to-numbers-contained-in-each-string-in-notepad

The pattern that the SO OP is searching for is not like yours, but regex is powerful enough for most situations where a pattern exists.

| improve this answer | |

Just in case anybody stumbles across this I have experimented with using Python to do this task:

import re
import datetime

striptrail = re.compile(r',+\n') # Regular Expression for trailing commas

started = datetime.datetime.now() # For timing
with open("stripped.txt", 'wt') as outfile: # Output file
    with open("bigfile.txt", 'rt') as infile: # Input file
        for line in infile:  # Read the lines
            outfile.write(striptrail.sub(r'\n', line)) # replace and save
print(datetime.datetime.now() - started) # How long did it take

With a file with 2 million lines each consisting of a quoted name (letters, hyphens & spaces), quoted address (numbers, letters, commas and spaces) and telephone number (digits & +-) (all random) followed by a random number from 0-3 of commas at the end of each line, (107 MB file). Processing took 9.145 seconds on my laptop.

Python is a Free, Open Source & Gratis, programming language that is available for most platforms.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.