Mysql unable to import csv files

Question

I want to clarify that i browse similar questions about file encoding and importing csv files to Mysql. None of these worked for me.

The situation is like this:

i was in charge of the task of creating a spring boot application which generates csv files filled with random data to populate a database (each csv file correspond to a table in the DB).

the first scenario was to import these .csv files into Oracle 12c. There was no problem, i done it, there was no errors in the data.

The actual problem is using the same .csv files to import them into Mysql, it is not possible.

Clarifying both Databases (Oracle and Mysql) have both the same tables with the equivalent Data Types

I tried:

• LOAD IN FILE option but the query never completes (it never shows any error message).

• Exporting the data from Oracle (from SQLDeveloper) into a .csv file and then import it in Mysql, i got the same error below.

• the Data Table Import Wizard from Mysql-workbench and it says:

Can't analyze file, please try to change encoding type, if that doesn't help, maybe the file is not: csv, or the file is empty.

Followed by:

Unhandled exception: 'ascii' codec can't encode character u'\xcd' in position 8: ordinal not in range(128).

I browsed questions about the encoding but none works to my case. And i'm pretty sure that i set the encoding to UTF-8 in my spring boot application. My file writer has it:

writer = new OutputStreamWriter(fileOutputStream, StandardCharsets.UTF_8);

How is it possible that the generated .csv files work perfectly in Oracle but no in Mysql?

Aditional info:

• springboot 2.0.3.RELEASE

• Java version 1.8.0_162

Answer 1

If you are working on Java environment. I think DBIS solution can work better for you. In this solution you only need to map csv file columns(header name or index) with column name of database table in XML file. no other code is required. https://stackoverflow.com/a/50180272/2480620

Add one script to export table to csv and next csv to table.

<?xml version="1.0" encoding="UTF-8"?>
<config>
  <connections>
      <jdbc name="mysql">
          <driver>com.mysql.jdbc.Driver</driver>
          <url>jdbc:mysql://localhost:3306/test1</url>
          <user>root</user>
          <password></password>
      </jdbc>
      <jdbc name="oracle">
          <driver>oracle.jdbc.OracleDriver</driver>
          <url>jdbc:oracle:thin:@localhost:1521:orcl</url>
          <user>root</user>
          <password></password>
      </jdbc>
  </connections>
  <component-flow>
      <execute name="oracle2csv" success="csv2mysql" enabled="true">
          <migrate>
              <source>
                <table connection="oracle">locations</table>
              </source>
              <destination>
                <file delimiter="," header="false" path="D:/test_source.csv"/>
              </destination>
              <mapping>
                  <column source="location" destination="1" />
              </mapping>
          </migrate>
      </execute>
      <execute name="csv2mysql" enabled="true">
          <migrate>
              <source>
                  <file delimiter="," header="false" path="D:/test_source.csv"/>
              </source>
              <destination>
                  <table connection="mysql">locations</table>
              </destination>
              <mapping>
                  <column source="1" destination="location" />
              </mapping>
          </migrate>
      </execute>
  </component-flow>
</config>

full solution is explained https://dbisweb.wordpress.com/

I have used it on MySql, Oracle and Postgres.