Blog PostLoading fixed width lines in MySQL

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.

Comments

Discuss with me and others You need to sign in to post comments