Load columnar text file into MySQL table

Load columnar text file into MySQL table

Ever come across a situation where you have to load a flat text file into MySQL with data provided in single column!
Say, your data to be loaded in table looks something like this:

Company1
Street Name
City, MyState 8582
(999)999-999
another_Company
Another Street 2
New City, NWSTATE 8582
(111)111-1111

So how’d you go about loading a text file into MySQL where field values are given in a single column!!
In the above sample, data has fields like company-name, address-1, address-2 and phone given in a text file line by line.
Ofcourse you can write a piece of code in your choice of language: perl, shell, python…
But can you do this by sheer power of MySQL by using some commands or functions only?

Well, normally you will have to have a script for such data to be inserted into database. We always prefer the raw data / text file in the csv / tab separated (columns) file(s), which you can directly load to MySQL table as explained in my previous article: http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server

But that’s not the case here. And hence I have a workaround to share. With little more efforts you can also automate / scriptize this. [ I’m feeling bit lazy now.  ]

Follow these steps:
– Save above data in a file [say c:/main.txt considering windows]
– Connect to MySQL through commad prompt

– Create a main table to load all data to process later.

drop table if exists main; create table main ( id integer not null auto_increment primary key,alldata varchar(100), type int );

– Load data to MySQL table:

load data local infile 'C:/mainfile.txt' into table main lines terminated by '\r\n' (alldata) set id=null;
set @var=0;update main set type=if(id mod 4 = 1,@var:=@var+1,@var);

– Create table as per your requirement:

create table company_details (
company varchar(100),
street varchar(100),
citystatezip varchar(100),
phone varchar(100) );

– Generate insert statement:

select concat("insert into company_details (company,street,citystatezip,phone) values (", my_values, ");") from (select group_concat('"',alldata,'"') my_values from main group by type) X;

you may alternatively redirect this data to a txt file and load it back to mysql!

put above query in sql :
mysql -uroot -ppassword database < generate_insert_query.sql > inserts.sql

Finally load thus generated inserts to MySQL database table.
mysql -uroot -ppassword database < inserts.sql