Solved

MySQL Error 1366

Posted on 2014-03-17
16
702 Views
Last Modified: 2014-03-17
I am a real rookie on MySQL (migrating from MS/ACCESS 2013). I need to export an old table from ms/access to ms/excel and saving it as CSV in order to import to MySQL.

The database is set with a single table (this table), to be imported. It looks alright, but I am consistently getting the error on import when there are special characters such as [ó] [á] to [ã]. The error is: "1366 Incorrect string value: ' ...' for column.."

My SQL statement is:

load data local infile '/Users/jlsp/Dropbox/md/AD/GuineBissau/eleicoes GNB/11 BDDE Lc/BDDE_01/T_21_Delegados59Migra.csv'
into table Militantes
character set utf8
 fields terminated by ';'
 enclosed by '"'
 lines terminated by '\r'
      (Mil_Estatuto, Mil_Organismo, Mil_Regiao, Mil_Sector, Mil_Numero,
      Mil_Orgao, Mil_Nome, Mil_Sexo, Mil_Cargo, Mil_Observacoes, Mil_Fonetica, Mil_OutroNome, Mil_Telefone1, Mil_Telefone2)

all records are imported but, for every column, the character after the accented character are not inputted to the column
can anyone hope?
0
Comment
Question by:jirdeaid
  • 8
  • 8
16 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39934532
We all hope :)

Please post some sample lines from your csv and your CREATE TABLE statement so we can test.

Thanks,
Dan
0
 

Author Comment

by:jirdeaid
ID: 39934562
The CSV is attached. I do not have a create (I have used the workbench) but I have the following statement:

ALTER DATABASE MySQL
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

which was successfully executed.

After posing the question, I have managed to sort out by changing to "latin1" the UFT8 specification on the "load data file". Now my front end is a mess changing all characters.

As I do not understand the issue I do not know what to do
T-21-Delegados59testeFF.csv
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 500 total points
ID: 39934608
Since you told MySQL that the fields are enclosed by "", maybe enclosing them will help.
See attached.
If not, I'm going to need the table structure, cause you have 41 fields in your csv and importing 14 into your table.
FWIW, in Excel I could correctly import your csv using latin3, while UTF-8 produced errors.
T-21-Delegados59testeFF.csv
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:jirdeaid
ID: 39935021
I am sorry. Sent you the wrong file! Anyway, as I told in my previous message the problem looked to be sorted when I used "latin1" and the front end (MySQL workbench) looked perfect. The data is there and all accented characters are perfect.

But on my application front ente (I am using LiveCode), it shows wrong and weird characters... And, because I do not really understand the picture, I do not know what to do on the front-end side to sort out the problem.

Do you think that by enclosing on "" will solve?
Joao
T-21-Delegados59Migra.csv
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 500 total points
ID: 39935030
Nope. If it looks good on Workbench, then the data imported correctly.

When presenting the same data, you need to use the same encoding, to avoid character misrepresentation.
0
 

Author Comment

by:jirdeaid
ID: 39935050
so which commend shall I send to mySQL to query the data in the proper manner? Or is this a problem with LiveCode?
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39935060
The only command to query is SELECT.

Mysql will send the data, then it's the application's job to interpret it.
0
 

Author Closing Comment

by:jirdeaid
ID: 39935075
Ok.
I am closing the question. It is not MySQL anymore, and hope that someone in experts exchange can help me on live code/runrev....
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39935089
You might find this to be of interest: http://forums.runrev.com/viewtopic.php?f=12&t=6040
0
 

Author Comment

by:jirdeaid
ID: 39935102
went there an it looks pretty easy. My database has a single table and in order to be able to import I had to use the "latin1".

So now this table is latin1. How do I change the whole table to UFT8?
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39935107
Try creating a new table with UTF-8 collation and then INSERT SELECT from your original table.
Don't know if it will work properly, but it's worth a try.
0
 

Author Comment

by:jirdeaid
ID: 39935129
ok. I'll try. as I am totally newbie to SQL I need first to look for  a syntax to do it. I will the let you know

joao
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39935135
INSERT INTO new_table
    SELECT Mil_Estatuto, Mil_Organismo, Mil_Regiao, Mil_Sector, Mil_Numero,
      Mil_Orgao, Mil_Nome, Mil_Sexo, Mil_Cargo, Mil_Observacoes, Mil_Fonetica, Mil_OutroNome, Mil_Telefone1, Mil_Telefone2 FROM old_table
0
 

Author Comment

by:jirdeaid
ID: 39935138
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39935145
You can try that too. Just on a copy of the table, so you don't need to import your data again.
0
 

Author Comment

by:jirdeaid
ID: 39935153
nope. same problem. but when I edit the field in the LiveCode grid and put a right character it accepts. It looks that this is a presentation problem on the LiveCode grid...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
How to create a custom search shortcut to site-search Experts Exchange using Google in the Firefox browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch your Bookmark Menu: Press 'Ctrl +…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question