Today I required to load a file into a database, the file requires some parsing to be loaded. Normal developer would load the file using a programming language, do the parsing and insert into the table. However, for me, I thought I would do it using MySQL DATA LOAD, it is faster and yet, no programming language required to load the files.
I am posting a sample code on how to parse files line by line using MySQL.
LOAD DATA LOCAL INFILE ‘path/to/my/file’ into table [table-name](@line)set field1 = SUBSTR(@line,1,10),field2 = SUBSTR(@line, 11,12),field3 = SUBSTR(@line, 48,19)
Now, what I did, is I just parsed text, since the file was not delimited but it had fixed width.