If you are working with MySQL and need a way to import a flat file into a table in MySQL, this method should always work.
The reason I say it should always work is because depending on if you have access to the machine where MySQL is running, you might be able to import and export files differently.
Since you can't always be guaranteed that the machine where the import file lives is the same machine where the MySQL database files live, stick to using mysqlimport.
- Login to MySQL and create a table that will contain the data that you are about to import.
- Rename the import file so that it matches the name of the newly created table (extensions to the filename are ignored)
- Run mysqlimport to load the data
Here is a sample command line that tells mysqlimport that the file is local (on my machine) and the database is on some remote host:
$ mysqlimport --host=someremotehost.com --local --port=5363 --user=importman --password=mypassword --lines-terminated-by="\n" --fields-terminated-by="\t" mydatabasename myimport_tablename.importfile.csv
In the above example the filename of the file to be imported is myimport_tablename.importfile.csv. Remember that filename extensions are ignored, so the table name that will be imported into is called myimport_tablename.
Learn more about the mysqlimport program. Read the MySQL documentation to figure out how to interactively enter the password, or to have most of the parameters already specified for you by using a .my.cnf file.
Use the following command to help you identify hidden characters used for column separation and line endings:
$ od -c file_to_import.txt |more
Use the following command to determine the type of file:
$ file file_to_import.txt
Use the following command to remove strange characters from a non ascii file:
$ iconv -f UTF-16 file_to_import.txt > fixed_file_to_import.txt