Trying to import csv file with load data local file SQL and cast as integer

Question

I try to import a (huge) csv file, containing a hotel_id in the first column, I want the data-type in sql table to be Integer with this code;

load data local infile 'Images_20121121.csv' into table cimages fields terminated by '|' LINES terminated by '\n' (@var1, link, description) set hotel_id=CAST(@var1 AS UNSIGNED INTEGER);

But this does not work (I get an invalid data type error), any ideas?

The CSV file is formatted like this;

ˇ˛hotelid|url|description
4052465|https://carsolizeprod.blob.core.windows.net/images/hotels/a7ce966f-1c8d-4cdb-8050-0000132d2561.jpeg|
4020907|https://carsolizeprod.blob.core.windows.net/images/hotels/7230b738-4746-4751-8212-0000171a99c5.jpeg|
4263993|https://services.carsolize.com/images/hotels/f7f27005-3546-4347-8e18-000021a66962.jpeg|Exterior
4136518|https://services.carsolize.com/images/hotels/30ba8994-acd9-4993-9f74-0000359c309b.jpeg|Guest room
4305893|https://services.carsolize.com/images/hotels/c960b56a-bba6-4256-a0cd-00003f4be196.jpeg|Bar
4023461|https://services.carsolize.com/images/hotels/30388432-ffd2-4b2d-bb86-0000489cfbcf.jpeg|
4205330|https://services.carsolize.com/images/hotels/7473dde7-e7e3-4228-ab1d-000049e7ecfe.jpeg|

And the warning im getting is;

| Warning | 1292 | Truncated incorrect INTEGER value: '' |

UPDATE: I found that the varchar-fields that are actually being imported contains a nul value between each character so this is probably where the problem is (?) (looking at the value in binary theres a 00 field between each, as text it is aNULbNULcNUL how can I avoid this?

Can you show some lines from the CSV-file? Are values for the first column correct? — Devart
– Devart, Commented Dec 6, 2012 at 11:34
if i change the hotel_id database field to varchar it works, when it is integer it just does not... — hso
– hso, Commented Dec 6, 2012 at 13:36

Radhi · Accepted Answer · 2012-12-07 02:08:10Z

2

the query seems to be fine. But, what are the contents of the CSV file? (Provide a sample if possible).

Also, take note that if the first row contains column names, then you might want to change your query to:

load data local infile 'Images_20121121.csv' into table cimages fields terminated by '|' LINES terminated by '\n' IGNORE 1 LINES (@var1, link, description) set hotel_id=CAST(@var1 AS UNSIGNED INTEGER);

Also, this warning makes a bit more sense, please try to validate the contents of the first column for white space rows, if such a row exists it might be the culprit.

Another suggestion, try to apply the same query for the small sample you have shown, does it work?

Take a quick look at the link below, might provide you some more insight on how the LOAD DATA works.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

edited Dec 7, 2012 at 2:08

answered Dec 6, 2012 at 11:30

Radhi

1256 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hso Over a year ago

Updated original question with sample from CSV file.

Radhi Over a year ago

Updated my answer. Please check and let us know the result.

hso Over a year ago

Thanks for that, unfortunately it didn't help (same error), however i found that the varchar-fields that are actually being imported contains a nul value between each character so this is probably where the problem is (?) (looking at the value in binary theres a 00 field between each, as text it is aNULbNULcNUL so i need to update the import query to remove these NUL's any idea how?

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Carsolize provide the CSV file encoded in UTF-16. You can verify this by running the Linux file command against the CSV file.

As documented under LOAD DATA INFILE Syntax:

Note

It is not possible to load data files that use the ucs2, utf16, or utf32 character set.

You can use iconv to convert to UTF-8:

iconv -f UTF-16 -t UTF-8 Images_20121121.csv > Images_20121121_utf8.csv

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 27, 2013 at 15:57

Shane O'Grady

2,49518 silver badges21 bronze badges

Collectives™ on Stack Overflow

Trying to import csv file with load data local file SQL and cast as integer

2 Answers 2

3 Comments

Note

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Note

Comments

Your Answer

Sign up or log in

Post as a guest

Related