• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 911
  • Last Modified:

Cannot import string 'NU¥EZ' or 'NUÑEZ' into MySQL table

I have a MyISAM table with a MailTo column.  The column is varchar(100), Column CharSet: utf8

I have a .txt file that I am trying to import with 'load data infile'

The file contains one row with a value of 'NU¥EZ' for the MailTo column.

When I try to load the file, I get error message: "Incorrect string value: '\xA5EZ LU...' for column 'Mailto' at row 1"
This is referencing the '¥EZ'  of 'NU¥EZ'.  The ''¥'  is being represented by the \xA5.

Same problem if I change the string to 'NUÑEZ':  "Incorrect string value: '\xD1EZ LU...' for column 'Mailto' at row 1"
The 'Ñ' is '\xD1'

Now if I COPY the string from the .txt file and edit the table in MySQL Query Browser and paste the string into a new row, MySQL accepts it without a problem.

Why is the load statement failing?  And how can I load the file?!

I am also annoyed that I am apparently dealing with 2 different code pages: I suspect the data was originally stored with some us / western / latin code page where N with tilde is \xa5; and then I have a database table using utf8 where \xa5 is a yen symbol, whereas \xD1 is the 'N with tilde'.  Is there a more elegant way for me to import without having to do a global replace to change the .txt file (i.e replace all \xA5 with \xD1)?
0
SAbboushi
Asked:
SAbboushi
  • 6
  • 5
  • 4
2 Solutions
 
Ludwig DiehlSystems ArchitectCommented:
Have you tried using 'iso-8859-1' for storing such kind of symbols into that column?.
How does the file you are importing ".txt" is created? is it a SQL script? or just a comma separated file?.
I suppose you are trying to load it using "Query Browser" right?
0
 
SAbboushiAuthor Commented:
>>Have you tried using 'iso-8859-1' for storing such kind of symbols into that column?
Nope...  I don't suspect this is the issue since I can copy and paste the symbols FROM the text file into the table without a problem.

>> How does the file you are importing ".txt" is created?
I am trying to import with 'load data infile': it is a tab-delimited file

>> I suppose you are trying to load it using "Query Browser" right?
Yes

0
 
Ludwig DiehlSystems ArchitectCommented:
It is weird i have tried importing a text file containing same symbols and it works fine. Could you please attach your text file or part of it?.
By the way what charset and collation was your table created with?. Please show me your table create script.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
SAbboushiAuthor Commented:
LOAD DATA INFILE 's:\\100104\\data\\nunez.txt' INTO TABLE test.real_acct fields escaped by ''

The offending field is 'Mailto'
CREATE TABLE  `test`.`real_acct` (
  `ACCOUNT` varchar(13) DEFAULT NULL,
  `Tax_Year` varchar(4) DEFAULT NULL,
  `Mailto` varchar(100) DEFAULT NULL,
  `Mail_Addr_1` varchar(75) DEFAULT NULL,
  `Mail_Addr_2` varchar(40) DEFAULT NULL,
  `Mail_City` varchar(50) DEFAULT NULL,
  `Mail_State` varchar(10) DEFAULT NULL,
  `Mail_Zip` varchar(10) DEFAULT NULL,
  `Mail_Country` varchar(50) DEFAULT NULL,
  `Str_pfx` varchar(50) DEFAULT NULL,
  `Str_num` varchar(50) DEFAULT NULL,
  `str_num_sfx` varchar(50) DEFAULT NULL,
  `Str_name` varchar(50) DEFAULT NULL,
  `Str_sfx` varchar(50) DEFAULT NULL,
  `Str_sfx_dir` varchar(50) DEFAULT NULL,
  `str_unit` varchar(50) DEFAULT NULL,
  `Site_addr_1` varchar(80) DEFAULT NULL,
  `Site_addr_2` varchar(50) DEFAULT NULL,
  `Site_addr_3` varchar(50) DEFAULT NULL,
  `State_Class` varchar(4) DEFAULT NULL,
  `School_Dist` varchar(50) DEFAULT NULL,
  `Map_Facet` varchar(6) DEFAULT NULL,
  `Key_Map` varchar(6) DEFAULT NULL,
  `Neighborhood_Code` varchar(50) DEFAULT NULL,
  `Neighborhood_Group` varchar(10) DEFAULT NULL,
  `Econ_Area` varchar(50) DEFAULT NULL,
  `Econ_Bld_Class` varchar(50) DEFAULT NULL,
  `Center_Code` varchar(50) DEFAULT NULL,
  `Yr_Impr` varchar(50) DEFAULT NULL,
  `Yr_Annexed` varchar(50) DEFAULT NULL,
  `Splt_Date` varchar(20) DEFAULT NULL,
  `Dsc_Cd` varchar(2) DEFAULT NULL,
  `Nxt_Building` varchar(50) DEFAULT NULL,
  `Total_Building_Area` varchar(50) DEFAULT NULL,
  `Total_Land_Area` varchar(50) DEFAULT NULL,
  `Acreage` varchar(50) DEFAULT NULL,
  `Cap_Account` varchar(50) DEFAULT NULL,
  `Shared_CAD_Code` varchar(4) DEFAULT NULL,
  `Land_Value` varchar(50) DEFAULT NULL,
  `Improvement_Value` varchar(50) DEFAULT NULL,
  `Extra_features_Value` varchar(50) DEFAULT NULL,
  `Ag_Value` varchar(50) DEFAULT NULL,
  `Assessed_Value` varchar(50) DEFAULT NULL,
  `Total_Appraised_Value` varchar(50) DEFAULT NULL,
  `Total_Market_Value` varchar(50) DEFAULT NULL,
  `Prior_Lnd_Value` varchar(50) DEFAULT NULL,
  `Prior_Impr_Value` varchar(50) DEFAULT NULL,
  `Prior_X_Features_Value` varchar(50) DEFAULT NULL,
  `Prior_Ag_Value` varchar(50) DEFAULT NULL,
  `Prior_Total_Appraised_Value` varchar(50) DEFAULT NULL,
  `Prior_Total_Market_Value` varchar(50) DEFAULT NULL,
  `New_Construction_Value` varchar(50) DEFAULT NULL,
  `Total_RCN_Value` varchar(50) DEFAULT NULL,
  `Value_Status` varchar(50) DEFAULT NULL,
  `Noticed` varchar(50) DEFAULT NULL,
  `Notice_Date` varchar(50) DEFAULT NULL,
  `Protested` varchar(50) DEFAULT NULL,
  `Certified_Date` varchar(50) DEFAULT NULL,
  `Last_Inspected_Date` varchar(50) DEFAULT NULL,
  `Last_Inspected_By` varchar(6) DEFAULT NULL,
  `New_Owner_Date` varchar(50) DEFAULT NULL,
  `Legal_Dscr_1` varchar(50) DEFAULT NULL,
  `Legal_Dscr_2` varchar(50) DEFAULT NULL,
  `Legal_Dscr_3` varchar(50) DEFAULT NULL,
  `Legal_Dscr_4` varchar(50) DEFAULT NULL,
  `Mill_cd` varchar(50) DEFAULT NULL,
  KEY `Center_Code` (`Center_Code`),
  KEY `Real_AcctAccount_Number` (`ACCOUNT`),
  KEY `Str_num` (`Str_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

nunez.txt
0
 
Kevin CrossChief Technology OfficerCommented:
If you are using LOAD DATA INFILE then you can use the CHARACTER SET option to specify how the file should be converted/handled.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html
0
 
Ludwig DiehlSystems ArchitectCommented:
The problem is that such symbol or set of symbols are not recognized by utf8 so you can try using:

LOAD DATA INFILE 'nunez.txt' INTO TABLE real_acct CHARACTER SET latin1;

It worked for me. Hope it helps. But even though "NU¥EZ" will be imported as it is with no charset conversion so in the end you will always have "NU¥EZ" for that row in your table if that is what you expect.
0
 
SAbboushiAuthor Commented:
mwvisa1
thanks for your post.  Without specifically relating it to my issue, I had no idea how to proceed.

ludwigDiehl
>> such symbol or set of symbols are not recognized by utf8
I don't understand what you mean.  I believe that utf8 has to do with how the character is STORED, not whether a character is valid.

And I still don't understand why it is displaying as "NU¥EZ" in the first place when it should be 'NUÑEZ'.

I will try latin1 for loading
0
 
Kevin CrossChief Technology OfficerCommented:
I opened the text file and one record has  'NUÑEZ' and the other 'NU¥EZ', so it would appear the issue is with the original export / saving of the data to the file.  Check that the export process or original data is not creating that portion of the issue.  As for the import, using the CHARACTER SET as shown should work out fine.  Advise if you don't get the row with  'NUÑEZ' imported correctly.
0
 
SAbboushiAuthor Commented:
This is a test file for the purposes of this post.  The original file contained 'NU¥EZ'; I duplicated the line and changed one of the lines to 'NUÑEZ' for testing.  If the data in the source database has 'NUÑEZ', then how would you account for the export file having 'NU¥EZ'?

0
 
Kevin CrossChief Technology OfficerCommented:
It exporting with an incorrect character set.  Therefore, if latin1 (which is  'iso-8859-1' by the way) ends up working for you, then you can ensure that you export the data in that format as well.
0
 
Kevin CrossChief Technology OfficerCommented:
If you can't impact the data file export and are absolutely certain that all ¥ should be Ñ then you can simply do a find and replace in the text file or after importing into the database.
0
 
SAbboushiAuthor Commented:
I have no control over the export

>>It exporting with an incorrect character set.  
For example?
0
 
Kevin CrossChief Technology OfficerCommented:
I am sorry, you established in the question that you were exporting in latin code page and importing to utf8; therefore, you are already doing what you need to which is to import the file using the latin1 character set on load data file.  Did that not work on your end as it worked fine for me as well.
0
 
Ludwig DiehlSystems ArchitectCommented:
As far as I know charsets are only character representations and if you STORE these representations depends on how you want to handle such data because you can just display information: a web site for instance.
Now back to your case, "NUÑEZ" can be handle using latin1 charset however "NU¥EZ" ...still don't know what codification is uses coz if it was utf-8 then it would be ( "NUÃEZ") so if you cannot control the export process then the only way would be to replace those characters or try to change the charset u use for exporting in case u can.
0
 
SAbboushiAuthor Commented:
Thanks for the help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now