Export - Import csv

[expired user #5687]'s profile image [expired user #5687] posted 13 years ago in General Permalink
Hi,
i have two db mysql with the same table.

I export the result query from first db with the function Export grid data to file... and save the file csv

Import on the second db the file csv just create with the function Import CSV file... but i receive this error

No rows were imported. This can have several causes:
-File is empty
-Wrong file encoding was selected or detected
-Field and/or line terminator do not fit to the file contents

Help me
ansgar's profile image ansgar posted 13 years ago Permalink
Please have a look at the file using some text editor - what does it contain exactly? You can post some lines here so we can discuss that here.
[expired user #5687]'s profile image [expired user #5687] posted 13 years ago Permalink
the file not is empty and the fist line are:

"field1";"field2";"field3";"field4";"field5"
"1469164";"1109";"35";"126;295;332;241;575;527;635;675;836;838;826;153;300;344;828;834;621;706";"341"
"1469165";"53116";"35";"102;295;226;334;672;687;659;719;838;826;914;107;916;610;501;258;359;917";"340"
"1469166";"16228";"35";"101;353;332;233;635;697;702;545;826;895;865;103;874;870;570;560;231;360";"123"
ansgar's profile image ansgar posted 13 years ago Permalink
Ok, looks good so far. Now, what control characters did you put into the import dialog - or, if you didn't touch them, which were they? Your output expects a semicolon as separator, double quotes as encloser and windows linebreak as line terminator. If you get the above warning I guess these were not set?
[expired user #5687]'s profile image [expired user #5687] posted 13 years ago Permalink
I don't change the setting factory

The preferences export are:

Fields separated by: ;
Fields enclosed by: "
Lines terminated by \r\n

Limit copy as... 5
ansgar's profile image ansgar posted 13 years ago Permalink
No, I didn't mean the settings in the preferences dialog, but the settings on the import dialog where you selected that file and got the above mentioned "No rows" warning.
[expired user #5687]'s profile image [expired user #5687] posted 13 years ago Permalink
sorry :)

econding cp1252 west european (latin1)
ignore first 1 lines
Fields terminated ;
Fields enclosed by "
Fields escaped by "
lines terminated by \r\n

select Insert (may throw errors)
select Server parses file contents (LOAD DATA)
ansgar's profile image ansgar posted 13 years ago Permalink
Think you need to blank out the "escaped by" option - if it's really the double quote, that may prevent the encloser from being recognized as a value encloser.
[expired user #5687]'s profile image [expired user #5687] posted 13 years ago Permalink
work very fine...

thank you for help smile
[expired user #8316]'s profile image [expired user #8316] posted 10 years ago Permalink
Hi,

I receive the same error but cudnt overcome trying last option.

[img][/img]

cud u help me ?
ansgar's profile image ansgar posted 10 years ago Permalink
You need to try out different control characters. I had the error too today and didn't know the file I was importing had UNIX linebreaks (\n), not Windows linebreaks (\r\n). Just try it out.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
When I try this this with (comma separated, double quote) still get this error irrespective of linebreak format
LOAD DATA LOCAL INFILE 'C:\\Users\\User\\UK_Statistical_Geographies\\Postcode\\Postcodes_(Enumeration)_(2011)_to_output_areas_(2011)_to_lower_layer_SOA_(2011)_to_middle_layer_SOA_(2011)_to_local_authority_districts_(2011)\\PCD11_OA11_LSOA11_MSOA11_LAD11_EW_LU.csv' REPLACE INTO TABLE "public"."pcd11_oa11_lsoa11_msoa11_lad11_ew_lu" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ("pcd7", "pcd8", "oa11cd", "lsoa11cd", "lsoa11nm", "msoa11cd", "msoa11nm", "lad11cd", "lad11nm", "lad11nmw", "pcdoasplt");
/* ERROR: syntax error at or near "DATA"
LINE 1: LOAD DATA LOCAL INFILE 'C:\\Users\\User\\UK_Statistical_Geog...
^ */
ansgar's profile image ansgar posted 10 years ago Permalink
Ok, "syntax error near DATA" probably means the whole LOAD command is not allowed or supported by your server. What server version is it? I recall you are a PostgreSQL user, right?
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Server 9.3.5
jfalch's profile image jfalch posted 10 years ago Permalink
"load data" is mysql only. postgresql doesn't have it (LOAD only loads plugins there); use COPY instead.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Thanks @jfalch: Just experimenting with COPY to_table FROM file.csv but now get permission denied. Something to do with being a superuser which I thought I was.
ansgar's profile image ansgar posted 10 years ago Permalink
Is that "COPY table FROM file.csv" valid for PostgreSQL? If yes I could implement that in HeidiSQL, as a replacement for the MySQL syntax.
ansgar's profile image ansgar posted 10 years ago Permalink
Ok, jfalch and the docs already answered my question.

Does anyone know if that COPY command allows to upload *local* csv files to a remote server? Like the MySQL command LOAD DATA LOCAL INFILE syntax?
jfalch's profile image jfalch posted 10 years ago Permalink
that would be COPY FROM STDIN ...; the docs state "STDIN: Specifies that input comes from the client application". Its is however not clear to me how the application is supposed to provide the data. This and this are related; in short, the client must use libpq.PQputCopyData() and .PQputCopyEnd() to transfer the data to be COPYed to the server, the only point still missing is how to determine when to start calling these functions; this seems to indicate that no event/callback is necessary, but you can start calling PQputCopyData() as soon as PQExec(..,'COPY FROM STDIN...') is done and PQresultStatus() returns PGRES_COPY_IN.
ansgar's profile image ansgar posted 10 years ago Permalink
Oh my god... surprised
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
I used Pentaho to transform from csv file to postgres table. Eventually did it after the processor over-temperature sensor tripped a couple of times. Oh my god...

Please login to leave a reply, or register at first.