Random thoughts shooting out of volatile mind
Importing a CSV Files into MySQL
CSV stands for Coma separated Values. Normally in this file each field is separated from other using ",". Well as part of my project I created a sniffer which will be sitting on the gateway and captures all packet coming and going to and from it. I needed to record all the traffic which I capture. Bestway is to dump it into a log file. First I thought of using space as delimeter but our sir told its better to use CSV instead so I seperated each fields using coma's. As per the requirement of the project I needed to create different forms of graphs. First I thought of creating graphs statically at the end of each day by using the Crontab facility. But our sir wanted us to make this graph generation dynamically. Well using flat file which grows very fast really gave us a head ache. But I read in some site that I can easily import the CVS files into any application. I stumbled uppon the PHPMyAdmins import facility which supports importing data from CSV File. I got the query from PHPMyAdmin to do the job.
Let me tell how to import your log files into database.
1. Create a table with number of fields equal to number of fields in your fail.
2. Use the LOAD DATA command to get files content into table. Below is the syntax of the command

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 = expr,...]
For example my log file consisted of 8 fields. Format is shown below
timestamp,eth_src,ip_src,src_port,eth_dest,ip_dest,dest_port,protocol
I created table to consist these 10 fields with time stamp as primary key and used load data command as follows
LOAD DATA LOCAL INFILE '/var/log/sniffer.log' IGNORE INTO TABLE `sniffer`
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
 In your log file delimeter can be any charachter you want, you just need to specify it as FIELDS TERMINATED BY ','. Similarly you can specify the line terminator also.
For more information about this command refer this link. Also don't forget to try out PHPMyAdmin
Posted by: copyninja on Saturday, 7 March 2009

blog comments powered by Disqus
Fork me on GitHub