Databases
StackOverflow2013
Postsdbo
POTrying to import csv file with load data local file SQL and cast as integer
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrying to import csv file with load data local file SQL and cast as integer
    text
    copied!<p>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;</p> <pre><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); </code></pre> <p>But this does not work (I get an invalid data type error), any ideas?</p> <p>The CSV file is formatted like this;</p> <pre><code>ˇ˛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| </code></pre> <p>And the warning im getting is;</p> <pre><code>| Warning | 1292 | Truncated incorrect INTEGER value: '' | </code></pre> <p>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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload