Problem with CSV

[fpauser]

Hi,

pgloader has problems with csv-values like this one:

"C:\\some\\nasty\\windows\\path\\"

It responds with:

ERROR We finished reading a quoted value and got more characters before a separator or EOL 69

I used the following WITH options:

WITH truncate,
    fields optionally enclosed by '"',
    fields escaped by backslash-quote,
    fields terminated by ','

The csv was created with mysqldump:

mysqldump -u root --fields-optionally-enclosed-by='"' --fields-terminated-by=',' --fields-escaped-by='\' --tab /tmp/ dbname tablename

Seems like there is a bug in pgloader's csv-parser?

[dimitri]

I think I will need more details, like a reproductible complete test-case, because my testing here shows no problem:

CL-USER> (cl-csv:read-csv-row "C:\\some\\nasty\\windows\\path\\,foo"
                              :separator #\,
                              :quote #\"
                              :escape "\\\"")
("C:\\some\\nasty\\windows\\path\\" "foo")
CL-USER> (cl-csv:read-csv-row "C:\\some\\nasty\\windows\\path\\"
                              :separator #\,
                              :quote #\"
                              :escape "\\\"")
("C:\\some\\nasty\\windows\\path\\")

See the test/ directory for easy self-contained examples using the FROM inline syntax.

Also, in your case, did you notice that you can actually directly connect pgloader to the MySQL source and load from a database connection?

[fpauser]

I was trying to use CSV because I have to load the data (only) from mysql to postgres and the CSV-source gives me the possibility to configure the table/column mapping between source and target (e.g. 'user.userid' => 'users.id').

I don't think that this is possible with the mysql-source, or at least I did not find any options to specify the table/column-mapping between mysql and postgres. I can specify a specific table, I can say 'data only', and I can cast types by column - but I cannot configure the actual mapping. Or did I miss something?

[dimitri]

You can speficy the mapping using a MySQL View on top of your current data model, then have pgloader only migrate the views. Easier way to do that is create a new schema in MySQL and have all the views in there, as they will be able to refer to the main tables in the main schema.

See the clause MATERIALIZE VIEWS where you can name the views you want to migrate the content of.

[fpauser]

Nice workflow - thanks for your hints!
For the CSV-Problem - I'll have a look and come back with a more detailed error-explanation.

[fpauser]

Closing this for now as I lost my failing pgloader-script and cannot provide more details to reproduce the csv-reading error.