11 Jan

Load Delimited Data – CSV to MySQL, Excel to MySQL Database

Loading csv to MySQL or any delimited data files to MySQL database is a very common task frequently questioned about and almost every-time LOAD DATA INFILE come into rescue.

In this article I will explain you about all different scenarios and explain you how to get the data loaded directly from csv to MySQL database using single command.

Download PDF Tutorial: Load data CSV to MySQL

The Load Data Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name’

[REPLACE | IGNORE]

INTO TABLE tbl_name

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}

[TERMINATED BY ‘string’]

[[OPTIONALLY] ENCLOSED BY ‘char’]

[ESCAPED BY ‘char’]

]

[LINES

[STARTING BY ‘string’]

[TERMINATED BY ‘string’]

]

[IGNORE number LINES]

[(col_name_or_user_var,…)]

[SET col_name = expr,…]

Consider we have to load file with following contents:

#File-name: example.csv

col-1,col-2,col-3

a,2,3

b,4,5

c,6,7

** Excel file can be easily exported as comma separated / delimited file (csv) by File-Save As option to load data.

1. A simple comma-saperated file with column header:

#table structure: example

 

col-1 col-2 col-3

Considering our MySQL table having same column sequence we can issue:

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES ;

This is a very common and simple scenario.

Quick updates:

– Ofcourse, if we don’t have column headers (col-1,col-2,col-3) in example.csv, IGNORE 1 LINES is not required.

– Note the file path. Here you should make sure your slashes are proper.

You may give path as: C:\\path\\file.csv or C:/path/file.csv.

– If we have datafile to be loaded stored on client ( Not on server ), we will add LOCAL keyword as given in Syntax.

So, the command will become:

LOAD DATA LOCAL INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES ;

– If we want to replace existing data by data being loaded from file, we will add REPLACE keyword before INTO TABLE.

Similarly if we want input rows that duplicate an existing row on a unique key value to be skipped, we will use IGNORE keyword before INTO TABLE.

2. Column sequence in file and table are different.

#table structure: example

 

col-2 col-1 col-3

In this case we need to specify column-name sequence of csv file in order to get data loaded in to proper columns.

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (col-1,col-2,col-3);

3. csv / load data file have lesser number of columns than targetted table

#table structure: example

 

col-1 col-2 col-3 col-4

Consider, col-1 is auto-increment and not provided in csv.

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (col-2,col-3,col-4) set col-1=null;

Passing null value will make col-1 to take an auto-increment value.

Using SET you can assign values to those columns which were not available in csv and are not-null.

You may also use a function for doing some perticular task and set a value.

eg. SET col-x=rand();

4. Filling the extra date columns:

This is very similar to 3. Here, we are required col-4 to be filled with present timestamp value, a very simple way to do is altering table :)

ALTER TABLE example CHANGE COLUMN col-4 col-4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

And then,

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (col-1,col-2,col-2=3) set col-4=null;

It should automatically fill the current_timestamp values for us.

5. Loading data with caculated columns:

#table: example

 

col-1 col-2 col-3 col-4

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (col-1,col-2,col-3, @var1) SET col-4 = @var1/100;

Similarly we can alter a string variable as well by altering the variable as follows:

SET col-4 = replace(@var1,"find","replace")

 

6.  Loading csv with table value lookup:

Consider you’ve got a csv with col1 and col2 data and the 3rd column data is availble in another table. You can load the referenced data using a sub-query as follows. You have to make sure you get single row in return may be by using distinct or limit clause.

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (col-1,col-2) SET col-3 = (SELECT field-name FROM linkedTable where keyfield=col-1);

7. Other ways of loading separated files to MySQL:

CREATE TABLE csv_foo LIKE foo;

ALTER TABLE csv_foo MODIFY COLUMN id INT(10) UNSIGNED NOT NULL;

// remove auto increment

ALTER TABLE csv_foo DROP PRIMARY KEY;

// drop key as no keys are supported in csv storage engine

Alternatively you may do:

CREATE TABLE csv_foo AS SELECT * FROM FOO LIMIT 0;

// Ignores key definitions and auto-increment

 

// Make sure you don't have any nullable columns.

Now,

STOP MYSQL SERVER

 

under data directory replace csv_foo.csv file by available data-file.csv. (Rename it to csv_foo.csv)

START MYSQL SERVER

 

We may need to do: REPAIR TABLE csv_foo;

Well, this is not a “good” way though.

8. Loading multiple files:

Documentation says that MYSQL LOAD DATA will not be able to do it for us.

We have a separate option available for the same, mysqlimport.

You may want to refer: mysql-import

Conclusion: I hope we have covered common scenarios which shall mostly help; rest will always be answered here.

Go through user comments below, you may get some specific use cases for for typical load data or csv to mysql conversion questions and probable solutions too.

Also refer to  the post where I’ve attempted to create a simple excel speadsheet to generate the Load Data Infile MySQL Syntax.

08 Jan

My Google Exploitation – Web History Graphs 2009

Well no doubt I’m a google freak. I’ve learnt a lot through it and I have searched a lot.
But I wasn’t much aware about my intencity of googling stuffs.
I hope you know there is something called Web History.

In case you’re unaware: www.google.com/history/

Google Web History includes:
– View and search details across webpages you’ve visited in the past
– trends on your web activity, such as your most visited sites and top searches
– Search strings and your visited sites
– Google Web History do allow you to notice unusual web activities (incase intruder is not smart enough) you may change your password.

Well now let me show you my Google Search activities / statistics in year 2009.

Daily search activity:

daily-search-activity

Monthly search activity:

daily-search-activity

Hourly search activity:

daily-search-activity

Ofcourse you should be logged in while googling and you should have your google history enabled to get this graphs.

Am I exploiting google too much?

I didn’t know many things I searched for, but I know them now :)
My love for Google shall sustain forever…
Hoping to break these limits in 2010 !!


07 Jan

crontab not working with dynamic date filename – rhel linux

I have done it for so many times, setting a cronjob.
Today I was tring to set a simple cronjob which will output the log file with timestamp attached filename.

Hourly running cron entry was:

0 * * * * perl demo.pl > demo-out_`date "+%Y-%m-%d_%H-%M"`.log

Running the same command on shell prompt was working fine. Execute permissions and path were proper. But it kept on failing to create the proper log!!

I changed the crontab as follows, added \ before % to escape and voila!!

Things worked !!

0 * * * * perl test.pl > test-out_`date "+\%Y-\%m-\%d_\%H-\%M"`.log

A quick lesson.

Good to note some of the date changers in linux for finding yesterdays and tomorrows and so on:
date –date=”1 days ago”
date –date=”yesterday”
date –date=”next day”
date –date=”-1 day”
date –date=’tomorrow’
date –date=’1 day’
date –date=’10 day’
date –date=’10 week’
date –date=’10 month’
date –date=’10 year’
date –date=”next Friday”
To escape problems you should learn to escape “special characters” – Krex
P.S.:This works in real life as well ;)

04 Jan

3 Idiots – Chatur Silencer Speech Chamatkar

Well 3 idiot was no doubt one of the best movie of last dacade.
But I laughed a lot at speech from Mr. Silencer, where rancho replaced words Chamatkar and Dhan :)

Here is the speech – spread it like a virus :)

Adarniya sabhapati mahodaya…
Atithi vishesh, Shikshan mantri shri R D tripati [tripathi] ji…
Maanyaniya shikshagan aur mere piyaaare sahpatiyo [sahapathiyon]…
Aaj agar I.C.E aasmaan ki bulaaandiyo [bulandiyon] ko chhu raahaa [raha] hai; to uska shreya sirrf [sirf] ekinsaan [ek insaan] ko jaataahai [jata hai] Shri Veerusahastra Bbuddhe… Give him a a big hand… He is a great guy really …
Peechle buttis saal se inhone nirantar is college mein balatkar pe balatkar kiye… umeed hai aagey bee karte rahege.. Hamine to aashcharya hota hai ki ek insaan apne jeevan kaal mein itni balatkar kaisi kar sakta hai?!!
Inhone kadi tapaasya se apne aapko is kaabil bunaya hai…
Waqt ka sahi upyog ghante ka purna istemaal koi inse seeke… seeke inse seeke…
Aaj hum sab chaatra yaha hai… Kal desh videsh mein fail [faael] jayenge… Waadaa hai aapse, jis desh mein honge waha balatkar karenge. I.C.E ka naam roshan karenge… Dika [dikha] denge sabko jo balatkar Karne ki shamtaa yaha ke chaatro mein hai wo sansaar ke kisi chaatro mein nahiii… No other chaatra No other chaatra…

Adarniya mantraji namashkar aapne is sansthaan ko wo chees di jiski hamein sakht zaroorat thi… sstunn… stunn hota sabi [sab hi] ke paas hai… sab chupa ke rakte hai… detaa koi nai…
aapne apna stunn is balatkari purush ke haat mein diya hai… ab dekiye yeh kaisa iska upyog karta hai…

Also the sanskrit poem was killing at the end :)

utamamm dad datdat padam..madhyam padam thuchuk thuchuk
khanishtham thudthudiii padam..sursuria pran khatkam..!!!!

3 idiots is musically good as well, especially i loved “Give me some sunshine”.

3 Idiots – Nice movie !!

02 Jan

waiting for another Avatar – discussion

Are we waiting for another “Avatar”??

As per Hindu mythology, lord Vishnu took 10 different avatars to save our world. As per mythology, lord Vishnu will take avatar when Rakshasas get out of control. Rakshasas will come from PatalLok. Rakshasas will kill innocents to gain power and money. Their guru Shukracharya will promise them that if they follow his orders they will get heaven. After all the sufferings,  people will start praying to Lord Vishu to save them from Rakshasas and this is when lord Vishnu listens to their followers (bhakts) and take avatar on earth (ManushyaLok). The avatar of lord will have difficult battle with powerful Rakshasas but at the end truth only wins. That’s why in Sanskrit we have “Satyam-ev Jayate” , which is also written under the statue with four lions. (Ashoka-stambh)

So how this does relates to today’s world? Today we are dealing with a different kind of Rakshasas. Now we call them terrorists. Yes, they did not come from “PatalLok” this time, but they have similar characteristics. Both kill people very easily. Both are aiming for heaven. Both are guided by gurus (guru Shukracharya previously, some moulana now).

Now the question remains, are we again going to pray to the lord to save us from these modern Rakshasas armed with Ak47 and hand grenades? Are we waiting for the miracle to happen? Are we ready for the battle of the Satya and Asatya?

There is a bomb blast somewhere in the world every day. Innocent people are dying. More than 20,000 people have died till date in Kashmir. Last year 26th November 2008, over 200 people died in terrorist attack in our own Mumbai. The terrorism has risen to an insufferable level. We saw thousands of people came protesting on road to punish the one who have attacked/ planned the attack on Mumbai on 26/11/2008. Many of them are asking for a war against terrorism. Now, are we ready for a war? Many are saying that war is a foolish thing to do. There are two points that they put here, firstly our country cannot afford a war.  Secondly war will not stop terrorism.

They may be true, but they are not ready to understand one thing here; the war is always on from terrorists’ side. When I say, I support war on terrorism; I do not support invade on another country. I support war so that terrorists do not invade my life. I support war for truth.

When I say war for truth, let me go back to Hindu mythologies again. Hindu mythology has two mahagranths: Ramayana and Mahabharata. Both the mahagranths depict war for truth. In Ramayana, lord Ram (Avatar of Lord Vishnu) fights with Rakshasa king Ravan. Ravan takes away Mata Sita by tricking her and kidnaps her in his Pushpak Viman. Lord Ram is now forced to attack on Ravan’s Lanka to save Mata Sita. With the help of Lord Hanuman and Vanar Sena(Army of Monkey) lord Ram attacks on Lanka and kills Ravan and saves Mata Sita. In Mahabharata, Kauravas forcefully captured kingdoms of Pandavas, hence Pandavas with the help of lord Krishna (Avatar of lord Vishnu) wins over Kauravas.

What is so common in between both the Mahagranths? First, both have avatars of lord Vishnu and second, war is forced upon lord Ram and Pandavas. Now let’s apply peace formula here. Lord Ram meets Ravan in bilateral meeting of peace. Or say Pandavas and Kauravas sits together and decides on peace process. Will Ravan or Kauravas want peace? I guess no, that’s what even lord Ram has thought and he attacked Lanka. When Arjun gave up on the war field with the fear of lose, lord Krishna guided him to war. Why isn’t the same situation applied here?

The terrorist are never going to stop. In my opinion, the peace process is of no use with terrorism, instead we make ourselves look like frightened cowards.  We afraid of cost of war but we like paying in EMIs. We are ready to pay with our lives and properties every day but cannot afford a war. The fear of lose drives us towards the peace process. But aren’t we losing with every bullet, every bomb blast?

To conclude this, it’s time for us to stand up for ourselves and fight. We cannot wait for these modern Rakshasas to invade our life.

-d’ Chix.

-- Kedar Vaijanapurkar --