On mysqlimport, line breaks, and character encoding (I think)

Question

First, some disclosure: I am not a Linux admin, and my Linux admin is not a programmer.

That said, we have a cronjob that runs a mysqlimport command to import a text file that's generated daily. I have no control or involvement in how this file is created. Through trial and error, we discovered that the text file is generated on a Windows machine, so for the lines-terminated-by argument we had to specify \r\n. Last week it stopped working correctly, and we determined it was because the file was now being generated in Linux, so we changed it to just \n. My understanding (which isn't entirely clear) is that it depends on who generates the text file that determines what platform and encoding is used.

We have a shell script that's executing the mysqlimport command. When we provide the correct encoding, everything works perfectly. But since we don't know who's going to create the text file in the first place, is there a way to determine what the encoding is and implement the proper line-break character(s)? (And is "encoding" the proper term here?)

Answer 1

fromdos, dos2unix, and tofrodos are three programs sometimes installed on Linux systems. You could use one of these to always convert the format to unix line ends (\n).

Answer 2

Probably easiest to use tr to delete the extra \r from Windows line endings before running mysqlimport.

Answer 3

Hi,
You need to run the dos2unix command on the file that mysqlimport is reading, prior to calling mysqlimport. dos2unix will convert Windows line endings "\r\n" to *nix line endings "\n" if the provided file was generated on *nix system the file remains untouched.

That way you are making sure that you will always have the same format of line ending being fed to your shell script.

Best Regards

Answer 4

I think you can use dos2unix or unix2dos commands on linux to convert between 'Windows' and Linux encoding. So you don't have to detect what encoding is used, just run the command to make sure the file has correct encoding.

It depends on the version of the utility but generally the command would be executed like this:

dos2unix input.txt output.txt
dos2unix input.txt > output.txt