troubleshooting Question

PostgreSQL: Copy from File missing data error

Avatar of trevor1940
trevor1940 asked on
DatabasesPostgreSQL
17 Comments1 Solution2160 ViewsLast Modified:
I'm trying to build a PostgreSQL/PostGIS database using Entire country files dataset but I'm getting missing data error

I'm wondering if the copy command is affected by diacritics or I've not set the database up properly
Created a new database with UTF8 encoding

I built the table schema based on the given format (but using type text for NM_MODIFY_DATE not varchar,  having these as dates didn't make a diferance)
PostgreSQL docs state fields can be NULL by default  so

create table my_table(
id SERIAL PRIMARY KEY,
test1 text;  -- can be null
test2 text NOT NULL;  -- Has to have a value
}

In my  theory every column apart from id doesn't have to contain a value
 
I used large text viewer to open the uncompressed countries.txt file and copied the top 5 rows into a test file

Using   PostgreSQL Copy this test file imported correctly so I know my schema is correct
copy my_table(List of columns ) from '\\Path\\To\\TestFile.txt' WITH delimiter E'\t' csv HEADER;


When tried to ingest the larger  countries.txt (2.9GB) file I get an error "missing data" for column xyz at line 12345 (Last column with  data in that row, NM_MODIFY_DATE)

Using large text viewer again I located this line with proceeding and following lines into my test file and tried the copy again but get the same error

I opened the test file in excel to  see if there is columns  missing although not every row has data in each column they do  all match
 
the problem row has UFI -3373955 & UNI 6329083  

I can't edit the large text file to remove the problem row (Software not available on system) also the file is UTF8 and I don't want to corrupt the character set


I contemplated writing a perl script to parse the file replacing the tab with a '|' pipe (and removing the problem row,  although perl nativity reads / writes in UTF8 I don't want to possibly corrupt the file especially the diacritics

I'm going to ask perl experts

PostGreSQL doesn't have the ability to skip problem rows if there is a problem the whole file fails to copy so there could be other rows that errors

Any one got any ideas?
ASKER CERTIFIED SOLUTION
Martyn Spencer
Software Developer / Linux System Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros