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":{"0":"post-1749","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-columnar-data","9":"tag-load-data","10":"tag-load-text-file","11":"tag-mysql","12":"tag-mysql-database","13":"tag-text-file","14":"tag-text-to-mysql"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1749","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1749"}],"version-history":[{"count":3,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1749\/revisions"}],"predecessor-version":[{"id":1752,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1749\/revisions\/1752"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Company1
\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