How can I prepare the logs or textfiles to be read by MySQL using Load DATA INFILE INTO ?

Question

What is the best way to prepare the log files before inserting their values in the MYSQL database using

LOAD DATA INFILE '/tmp/someFile.txt' INTO TABLE

How can I make sure and make the data log file ready to be read by such command ? Someone told me by scripting (using sed,grep,cat,sub ...) .. how can I do that or what are the tools ?

I know how to place the file so MySQL can read it, but I want to know how to organize it and prepare its content for that.

Answer 1

You can use excel to setup your csv file correctly, or even calc from openoffice.

Do you not have access to phpMyAdmin? This will make your job a little easier. Also try searching Google for "mysql import csv". That should give you some information on how to layout your file.

Remembering from when I did this using phpMyAdmin, you could have your data in 1 of 2 ways depending on your import settings.

column_name_1 | column_name_2 | column_name_3
value1_1      | value1_2      | value1_3
value2_1      | value2_2      | value2_3

Your csv for this would look like:

column_name_1,column_name_2,column_name_3
value1_1,value1_2,value1_3
value2_1,value2_2,value2_3

OR

value1_1      | value1_2      | value1_3
value2_1      | value2_2      | value2_3

I hope that helps.

Answer 2

As long as it's readable as a certain kind of csv format (column & row delimiters, possibly quoting characters & escape characters) then you're ready. Remember that you can specific in which column (or no column) a certain portion of your csv-file needs to go, so no preparation needed for that.

Log files tend to use no quoting characters (should be fine if no spaces can exist in values), spaces or tabs as column delimiters, and newlines (\n) as row delimiters.