You are here

Problem while loading CSV data

4 posts / 0 new
Last post
fjbehr
Problem while loading CSV data

In version suas3_12_0513_beta1.zip I had problem while uploading a CSV file.

One sample record:

;Badeorte;Badeorte_0173;Point;8.36861;50.985;8.36861;50.985;POINT(8.36861 50.985);;EPSG:4326;<attributes><attribute name='Name' type='C' length='64' dec='0'>Bad Berleburg-Stuenzel</attribute><attribute name='Long' type='N' length='11' dec='0'>8.36861</attribute><attribute name='Lat' type='N' length='11' dec='0'>50.985</attribute><attribute name='Kategorie' type='C' length='64' dec='0'></attribute></attributes>;

Error message:
Error: Error when input CSV file into database!
MySQL Error: Out of range value adjusted for column 'id' at row 1 (1264)
Refer to MySQL Error Messages

I assumed that the id is created automatically by SUAS.

leelight

One reason is that the data contains single quote, like name='Lat' type='N', this is not compatible with the SQL data import command. I will fix this bug.
Thanks!

fjbehr

I modified my MapInfo program to create double quotes.

But I still get the error:
MySQL Error: Out of range value adjusted for column 'id' at row 1 (1264)

There seems to be a problem with the autoincremented id field which is left blank in the data.

But this error only occurs on my localhost, not on my server (both MySQL version5)

I read it might be a problem with the MySQL configuration ("strict").

Prof. Dr. Franz-Josef Behr - Home Office
Author of: Strategisches GIS-Management - http://www.gismngt.de
eMail: franz-josef.behr@hft-stuttgart.de
http://www.gis-news.de

leelight

[quote=fjbehr] MySQL Error: Out of range value adjusted for column 'id' at row 1 (1264)

I read it might be a problem with the MySQL configuration ("strict").
[/quote]
Yes!
This is a wide spread problem in New MySQL 5.0 installs. You can open the file 'my.ini' in your MySQL install folder - the config file. And commenting out the command near the top that turns on the 'strict' mode.
Change

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

TO
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And then restart MySQ.

The error is actually to do with the STRICT SQL Mode. With the above INSERT statement you are trying to insert an empty string ('') to rely on the AUTO_INCREMENT'ed value to be replaced(In our case it is id). The STRICT SQL Mode does not allow data conversions to take place like that (where earlier versions were a little more lax).


Another solution is to use NULL instead of '' for your AUTO_INCREMENT column.

For example:

NULL;Badeorte;Badeorte_0173;Point;8.36861;5........

ref:
http://forums.belution.com/en/sql/000/095/13s.shtml
http://www.kuaiyigang.com/post/78.htm

Log in to post comments