1

My Csv file has missing entries denoted as -

1.2E+09 8.59E+09    In  3   480 5697277130  822277190   OK  1   0   1.23E+09    34295
4.89E+09    9.59E+09    Out 3   480 1677272449  6747272509  OK  1   -100    8.87E+09    21
2.33E+09    4.49E+09    In  4   640 4347277310  4567277370  OK  1   -   3.23E+09    123
 2.33E+09   4.49E+09        4   640 4347277310  4567277370  OK  1   -   3.23E+09

I have defined my table schema as follows

create table zip_codes(
  Source numeric,
  manual numeric,
  TTYPE varchar, 
  feature1 numeric, 
  feature2 numeric, 
  LBP1 numeric, 
  RLBP numeric, 
  STATUS varchar, 
  NUMBER_OF_TRASVERSE numeric, 
  rank numeric, 
  image_number numeric, 
  parent_class numeric);

and my import script from csv file is

test_db=# COPY zip_codes FROM 'C:/tmp/dd_1.csv' delimiter ',' csv;

my error:

ERROR: invalid input syntax for type numeric: "-"

CONTEXT: COPY zip_codes, line 7, column rank: "-"

Should I change the numeric schema of rank to something else to get the - values imported? Any suggestions on how i can get these values imported.

It is a large csv so is there a way i can import all (-) values? Any suggestions?

Any suggestions?

Thanks!

Community
  • 1
  • 1
sam venu
  • 13
  • 5
  • You can easily [remove](https://stackoverflow.com/questions/5876296/regex-remove-lines-containing) specific lines using notepad++ – JGH Sep 10 '17 at 15:15

1 Answers1

0

You can specify the string used to denote the Null value. By default, it is \N but you can use any string, as per the doc, using the option NULL 'null_string'. It would be similar to

test_db=# COPY zip_codes FROM 'C:/tmp/dd_1.csv' delimiter ',' NULL '-' csv;

Should you have valid negative values, you may have to play with the string (with tab, comma or else), or to completly change the way you denote the nulls.

JGH
  • 15,928
  • 4
  • 31
  • 48
  • thanks for this ! but if i have say multiple missing entries . say one is denoted as " " and other is denoted by "invalid" .is there a way i can combine multiple missing entries? test_db=# COPY zip_codes FROM 'C:/tmp/dd_1.csv' delimiter ',' NULL '-' ,NULL ' ', NULL 'Invalid' csv; Will this be correct? – sam venu Sep 15 '17 at 14:04
  • @samvenu I doubt.. You can edit the file to standardize the Null value. – JGH Sep 15 '17 at 17:51