0

I have a MySQL table that is populated by CSV input from a local folder. I create the CSV by exporting an Excel sheet to .txt format, add the comma delimiter, and change the encoding to UTF-8 because I was getting errors before when inserting a CSV directly exported from Excel.

Question is: Is it possible to skip the steps of manually changing the exported CSV file to TXT and encode it to an UTF-8 file? The goal I have in mind is to export an UTF-8 CSV directly from Excel - or maybe with a script - into a folder.

Thanks for the answers.

Adrian
  • 442
  • 4
  • 15

1 Answers1

0

This may be a duplicate of Excel to CSV with UTF8 encoding, you could check there to piece together an answer.

I'm not sure that any answer really gives an authoritative/concise programmatic way to solve the problem, though. If using VBA is an option, check out this post, which gives a pretty complete solution: http://www.cpearson.com/excel/ImpText.aspx

If not, you could elaborate on a solution hinted at in a comment by Sebastian Godelet (check it out at the SO link above!). I would follow these steps in a script:

  1. export to txt
  2. use iconv to convert from utf-16 to utf-8
  3. use sed to convert tabs to commas

I can't find a non-VBA, non-GUI solution for 1, but 2 and 3 can be done by simply doing

iconv -f utf-16 -t utf-8 file.txt | sed $'s/\t/  /' newfile.txt

where the $ is a bash way to interpret the tab character (if using another shell, there may be another way to interpret the string properly). As for 1, you can export to utf-16 txt right from excel if you export as txt, then run this for every file in your folder.

Community
  • 1
  • 1
Tim
  • 380
  • 2
  • 10