Import data from CSV files into MySQL
When I got this task (importing data from csv into database) for the first time, my reaction was – “OK, I’ll transform the data into one big insert statement, with a bit help of regex and execute it”. And, indeed, I did it that way. Shortly after that, Nikola Cakarevic showed me how to import data directly, without additional changes (i.e. applying regex).
Let’s take some products table for example. Every now and then you’ll need to import new list of products and your csv file will contain product number, name and price. In that case you can use the following:
START TRANSACTION; LOAD DATA LOCAL INFILE 'C:/path/to/your/csv/file.csv' INTO TABLE products FIELDS TERMINATED BY ';' ENCLOSED BY '' LINES STARTING BY '' TERMINATED BY '\r\n' (product_number, price, name) SET some_other_field = 33, some_other_field_2 = 'another value etc.'; COMMIT;
As you may notice, I did this in transaction. In the case something goes wrong, you can do the rollback. On the line 3, there is a path to your csv file. Pay attention to use slash (“/”) instead of backslash (“\”) for this. If you copy an address on a windows platform, you’ll get the address with backslash.
You can specify how the fields are separated and enclosed (line 6 and 7). Also, you can specify how the lines are terminated. In this example I specify it to be ‘\r \n’.
The actual codes representing a newline vary across operating systems, which can be a problem when exchanging text files between systems with different newline representations. Wikipedia
Sometimes there can be a problem, caused by this new line specification. In order to be sure what end lines your csv is using, you can open it with Notepad++ and show it. When Notepad++ is opened, go to View > Show Symbol > Show End of Line. After that you should see some black CR or LF symbols (CR is \r and LF is \n).
Then, on the line 11 we are defining where we want to store our data, in which columns. Of course, we need to provide a csv file which has three columns (for this case). Last thing I want to mention is that you can set another values, like on the line 12. There you can set some additional values (such as for the fields that don’t have default values).
And, that’s all. No more regex for this kind of tasks.