1

I'm having troubles with special characters like í, ó, ñ, and so on in my MYSQL database.

I've read a lot about character set, collation, utf and son on but I might not being able to proper apply it to this problem. I'm not sure if the problem is with my tables or when reading the source file and encoding. I'm working on netbeans developing an interface that generates specific reports

Here I briefly explain the process when the issue occurs

  1. There is a table where are located the path files for the Excel files that are requested.
  2. My code takes that path, opens the file using the JXL library.
  3. Then it starts reading every cell that it is indicated in the code and export the data to the tables indicated (HERE IS WHERE IN THE TABLES APPEARS THIS � IN EVERY SPECIAL CHARACTER)
  4. The data is correctly exported to the several tables, and there is no problem with that, only special characters that are replaced with this �

So, after researching I've tried this

  • As I'm using MYSQL workbench I've alter every table in the collation from utf8 - default TO utf8 - utf8_spanish_ci and utf8 - utf8_spanish_ci
  • I've tried also changing the collation to utf16 - default collation, utf_spanish_ci, bin, etc
  • And also, I've tried using the utf32 collation.
  • The netbeans encoding it is correctly set to utf-8

  • I've tried exploring the JAVA functions that connects to MYSQL, I'm using this connection but I haven't found nothing about encoding: 

    private Connection conecta=null;
String Driver="com.mysql.jdbc.Driver";
String Url="jdbc:mysql://XXX.XXX.XXX.XX/BLAABLA";
Class.forName(Driver);
conecta=DriverManager.getConnection(Url,User,Password);

  • The database when I manually insert data that contains special characters, it correctly displays whenever I need it, but when I try using the automatically insert option as previously described that reads the file using jxl library and so on, then it happens the � thing.

  • I've found some useful answers here in stackoverflow but all are related to specific cases of php, html, xml and so on. All the answers about java are related to the utf8 collation

So I hope you can provide me some help.

Am I not doing the collation correctly?, Should I try something weird directly in the console? Does mysql workbench is forbidding something?

I'm open to all kind of suggestions but if the answer is like "You must use another library because jxl does not work with that" please consider that my project is almost done, and re-do this with a different library could take me much more time as I already have expected. Please, if JXL is the problem probably there must be something else. Nothing is impossible right?

Thanks for the time.

Kevin Diaz G
  • 35
  • 1
  • 1
  • 3

1 Answers1

0

Excel files by default uses windows-1255 (Cp1255) codification. So when you read those bytes from the excel file you need to treat them with that charset and then store them with utf8.

reos
  • 8,766
  • 6
  • 28
  • 34
  • Thank you. It was difficult to understand this thing but thankfully jxl does support encoding, I got it by CP1252 just as an example I found on this answer. [link](http://stackoverflow.com/questions/5701743/encoding-problem-in-jexcel) After reading your answer I knew what to look for on the internet. Thanks. – Kevin Diaz G Dec 07 '15 at 17:44
  • Maybe _your_ Excel defaults to Hebrew. – Rick James Dec 11 '15 at 00:33