Inserting CSV to Postgres via COPY

Questions / Help
1

Hello,

I have some troubles with importing of CSV data.
Here is some snippet:

s1 = "\"47\";\"-17756145\";\"some data\";\"type\";\"17756145\";\"50\";2017-07-27 13:34:28.095734;2017-07-27 13:34:28.095734"
s2 = "\"47\";\"-17756146\";\"some data 2\";\"type\";\"17756145\";\"50\";2017-07-27 13:34:28.095734;2017-07-27 13:34:28.095734"

stream = Ecto.Adapters.SQL.stream(MyRepo, "COPY areas(region_code,code,name,type,parent_code,addr_level,inserted_at,updated_at) FROM STDIN CSV DELIMITER ';' ")

MyRepo.transaction(fn -> Enum.into([s1,s2], stream) end)

I get the following error:

** (Postgrex.Error) ERROR 22P04 (bad_copy_file_format): extra data after last expected column
    (db_connection) lib/db_connection.ex:654: DBConnection.execute!/4
         (postgrex) lib/postgrex/stream.ex:78: anonymous fn/7 in Collectable.Postgrex.Stream.make_into/4
             (ecto) lib/ecto/adapters/sql/stream.ex:35: anonymous fn/4 in Collectable.Ecto.Adapters.SQL.Stream.make_into/2
             (ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:790: DBConnection.transaction/3

What am I doing wrong?

If I’m trying to import a single line everything is just fine:

MyRepo.transaction(fn -> Enum.into([s1], stream) end)
2

Insert line endings between your data, it will probably work then. I do guess that io lists are used, this would mean you are concatenating your data into a single line.

3

@NobbZ thank you so much! I’ll try that soon.