0

I have some data prepared in Excel 2016 for export into CSV with the help of VBA:

workbookToExport.SaveAs Filename:=CSVFilename, FileFormat:=xlCSV, local:=True

Then I take the CSV and I use the Table Data Import Wizard of MySQL Workbench. Here I get

Error
Unhandled exception: list index out of range
Check the log for more details.

This is the sample data, which leads to the above error:

id;name;information;reference;limit;release
26;Lorem ;"Lorem ""ipsum"" Lorem ipsum.
Lorem ipsum.
Lorem „Ipsum“ lorem";Lorem ipsum;2;10000

Here you can see, that the string itself is in quotes ", an escape quote is used "" and the data goes over multiple new lines.

If I remove the new lines, the import does work. Is there a possibility to preserve the new lines? How?

I read that you can preserve new lines if you quote your text. But that does not work for my case.

What can I do?

PS: The encoding of the CSV is in latin 1 (iso-8859-1) / ANSI.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
testing
  • 19,681
  • 50
  • 236
  • 417
  • CSV stands for comma-separated values, where each line represents a record. If you need it to hold lorem ipsum and preserve newlines, consider using another data format that's better suited for this. – Mathieu Guindon Sep 17 '19 at 16:30
  • @MathieuGuindon: Thanks for the tip. That would be a solution. *MySQL Workbench* also supports JSON import. Now I have to figure out how to convert my worksheet into a JSON file. – testing Sep 17 '19 at 16:36
  • Does it support XML? You might have a much easier time with XML, but YMMV – Mathieu Guindon Sep 17 '19 at 16:36
  • Does not seem to be supported. Except I would use another tool which does the import into the MySQL database for me ... – testing Sep 17 '19 at 16:38
  • TBH I'm surprised Workbench doesn't support importing directly from an Excel file through ODBC. SQL Server Management Studio does it. Alternatively you could connect to the db with ADODB, iterate your rows, and insert the records via an ADODB.Command, parameterized with the values you want to insert (could be slow though). – Mathieu Guindon Sep 17 '19 at 16:39
  • I would have to make a research (never worked with ADODB). I saw [another option](https://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function): write a XML file and then import directly via mysql through command line. Or I write my own SQL file, which is then executed. But as I said I have to figure out, what's faster (aka easier) to implement. – testing Sep 17 '19 at 16:45

1 Answers1

0

Now I took the same CSV file and did the import via the mysql command line.

  1. Install MySQL server
  2. Navigate to C:\Program Files\MySQL\MySQL Server 8.0\bin
  3. Login to your remote database with mysql -u user -ppassword -h hostname -P portnumber --local-infile
  4. Check if your db server has local_infile activated with SHOW VARIABLES LIKE 'local_infile'; and turn it on with SET GLOBAL local_infile = 1;

  5. Import the CSV with the following command

    LOAD DATA LOCAL INFILE  
'C:/some.csv'
INTO TABLE dbname.tablename
CHARACTER SET latin1
FIELDS TERMINATED BY ';' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id;name;information;reference;limit;release);

  6. Turn local_infile off with SET GLOBAL local_infile = 0;

Other options would be:

  • don't care about new lines and remove them
  • export data as JSON with new lines and the MySQL Workbench importer
  • create XML and import via command line
  • write your own SQL script file
  • used ADODB
testing
  • 19,681
  • 50
  • 236
  • 417