Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have ruby and rails app, and I have cron task to download text file and import it to the database using 

\copy hotels FROM 'db/ActivePropertyList.txt' DELIMITER '|'

There is a header field which is called EANHotelID in this text file. My import fails because of this particular filed and if I manually rename it to, for example hotel_id, then import goes OK.

Is there any way to overcome this issue because I will be unable to rename it every time manually?

In my database schema the column is called ean_hotel_id.

UPDATE Error Description:

PG::Error: ERROR:  zero-length delimited identifier at or near """"
LINE 1: COPY "hotels" ("","sequence_number","name","address1","addre...

Text file example

EANHotelId|SequenceNumber|Name|Address1|Address2|City|StateProvince|PostalCode|Country|Latitude|Longitude|AirportCode|PropertyCategory|PropertyCurrency|StarRating|Confidence|SupplierType|Location|ChainCodeID|RegionID|HighRate|LowRate|CheckInTime|CheckOutTime
180997|1|Bristol Metropolitan|Av. Getulio Vargas, 286 - Savassi||Belo Horizonte||30112-020|BR|-19.93301|-43.92641|CNF|1|BRL|.0|52|ESR|Near Liberdade Square||577|90|54||
183714|1|Ibis Vitoria Praia Do Canto|R Joao Da Cruz 385 Praia Do||Vitoria||29055-620|BR|-20.29412|-40.29437|VIX|1|||43|ESR||2141|3711|96.4557|95.4557|12:00 PM|12:00 PM

Table definition

  t.column :ean_hotel_id,         "int"
  t.column :sequence_number,      "int"
  t.column :name,                 "varchar(70)"
  t.column :address1,             "varchar(50)"
  t.column :address2,             "varchar(50)"
  t.column :city,                 "varchar(50)"
  t.column :state_province,       "varchar(2)"
  t.column :postal_code,          "varchar(15)"
  t.column :country,              "varchar(2)"
  t.column :latitude,             "numeric(8,5)"
  t.column :longitude,            "numeric(8,5)"
  t.column :airport_code,         "varchar(3)"
  t.column :property_category,    "int"
  t.column :property_currency,    "varchar(3)"
  t.column :star_rating,          "numeric(2,1)"
  t.column :confidence,           "int"
  t.column :supplier_type,        "varchar(3)"
  t.column :location,             "varchar(80)"
  t.column :chain_code_id,        "varchar(5)"
  t.column :region_id,            "int"
  t.column :high_rate,            "numeric(19,4)"
  t.column :low_rate,             "numeric(19,4)"
  t.column :check_in_time,        "varchar(10)"
  t.column :check_out_time,       "varchar(10)"

For COPY I use gem postgres-copy (https://github.com/diogob/postgres-copy). in my task I have the code to map table columns names in the file to the database 

Hotel.pg_copy_from('db/ActivePropertyList.txt', :delimiter => '|', 
:map => {'EANHotelID' => 'ean_hotel_id',
    'SequenceNumber' => 'sequence_number',
    'Name' => 'name'
     .......
share|improve this question
Could you present here the first three rows of your file, the error code and maybe the table definition? – dezso Mar 8 at 21:48
Thank you! I ve added the required info. – Ilya Cherevkov Mar 8 at 21:55
As you can see all fields pass normally except EANHotelId – Ilya Cherevkov Mar 8 at 21:56
If I open the text file and replace EANHotelId with anything in lower case, and do so in my cron task, COPY proceed well, but I need to do this automatically because database should be renewed at least several times a month – Ilya Cherevkov Mar 8 at 22:08
Have you tried using "with csv header delimiter..."?? – akim Mar 9 at 7:10

1 Answer

up vote 0 down vote accepted
  1. Divide the CSV file to determine which string causes the error.

  2. Check that issue is not related to 'postgres-copy'

    cat import.csv | \
  psql -U foo -d bar -c "COPY your_tbl FROM STDIN DELIMITER '|' CSV HEADER;"

  3. Preprocess the CSV file with sed or awk, or fix postgres-copy.

share|improve this answer
From this: COPY "hotels" ("","sequence_number"... it looks like that the problem is in the column mapping. – dezso Mar 9 at 19:50
Yes, I found a solution. The text file was in UTF-8, so it added invisible special character in the beginning of the text file, so replacement didn't work. I just wrote a script to delete this symbol and pg-copy worked well, but maybe it is not good practice and I should convert it somehow (for example using iconv) – Ilya Cherevkov Mar 9 at 22:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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