Loading 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.

Replace text in many files using perl

Last night, I have several files containing the same text and I wanted that text to be replaced. I used perl one liner to do the job and I thought it may be useful for someone else so here it is:

perl -e “s/text/replacewith/g;” -pi.orig *.txt

This is going to save a backup of the files with .orig extension and do the replace. If you don’t want backup, then remove the .orig from the line.

Also, if you want to replace text in all files in a directory, then use:

perl -e “s/text/replacewith/g;” -pi.orig $(find . -type f)