Ever come accoss a situation where you have to load a flat text file into MySQL with data provided in single column!
\nSay, your data to be loaded in table looks something like this:<\/p>\n
So how’d you go about loading a text file into MySQL where field values are given in a single column!! 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\/load-delimited-data-csv-excel-into-mysql-server<\/p>\n 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. \ud83d\ude09 ]<\/p>\n Follow these steps: – Create a main table to load all data to process later.<\/p>\n – Load data to MySQL table:<\/p>\n – Create table as per your requirement:<\/p>\n – Generate insert statement:<\/p>\n you may alternatively redirect this data to a txt file and load it back to mysql!<\/p>\n put above query in sql : Finally load thus generated inserts to MySQL database table. I hope this helps many.<\/p>\n","protected":false},"excerpt":{"rendered":"Ever come accoss 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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[319,61,317,427,318,320,321],"class_list":["post-1749","post","type-post","status-publish","format-standard","category-mysql","category-mysql-articles","tag-columnar-data","tag-load-data","tag-load-text-file","tag-mysql","tag-mysql-database","tag-text-file","tag-text-to-mysql"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\tCompany1
\nStreet Name
\nCity, MyState 8582
\n(999)999-999
\nanother_Company
\nAnother Street 2
\nNew City, NWSTATE 8582
\n(111)111-1111<\/code><\/p>\n
\nIn the above sample, data has fields like company-name, address-1, address-2 and phone given in a text file line by line.
\nOfcourse you can write a piece of code in your choice of language: perl, shell, python…
\nBut can you do this by sheer power of MySQL by using some commands or functions only?<\/p>\n
\n– Save above data in a file [say c:\/main.txt considering windows]
\n– Connect to MySQL through commad prompt<\/p>\ndrop table if exists main; create table main ( id integer not null auto_increment primary key,alldata varchar(100), type int );<\/code><\/p>\nload data local infile 'C:\/mainfile.txt' into table main lines terminated by '\\r\\n' (alldata) set id=null;
\nset @var=0;update main set type=if(id mod 4 = 1,@var:=@var+1,@var);<\/code><\/p>\ncreate table company_details (
\ncompany varchar(100),
\nstreet varchar(100),
\ncitystatezip varchar(100),
\nphone varchar(100) );<\/code><\/p>\nselect 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;<\/code><\/p>\n
\nmysql -uroot -ppassword database < generate_insert_query.sql > inserts.sql<\/code><\/p>\n
\nmysql -uroot -ppassword database < inserts.sql<\/code><\/p>\n