...

View Full Version : Importing from csv



imatrox05
07-04-2006, 06:31 AM
Hi i am using the following code to import records from a csv file into mysql


connect_db();
$fname = $_FILES['userfile'];

$fcontents = file ('./Productivity.txt');

//Displays Data from file
echo "<pre>";
print_r($arr);
echo "</pre>";


for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i], '\t');
$arr = explode("\t", $line);

$sql = "insert into qcheck (qdate, empno, ename, jobno, activity, chapter, pages, time, dataerr, tagerr)values ('".implode("','", $arr) ."')";
$result = mysql_query ($sql) or die ("Query failed");
}
This code inports the data into the database but there is a minor glitch
The tab delimited data is set like this

Date orderno quantity team amount

When the print_r($arr) is executed it shows the data as it is

eg: 20-06-06 5585 50 TeamA 25000

But when the data is added into the database its added's a number 20 before the date and the data is stored like this

2020-06-06 5585 50 TeamA 25000

Can anyone throw light on why this is happening?

Fumigator
07-04-2006, 06:43 AM
Assuming that "qdate" field is a date and is going into a date field in the database, it's assuming it's a date of 2020 and making it so. A "date" field type in mySQL will always store the full date.

If you are trying to put the entire value "20-06-06 5585 50 TeamA 25000" into a date field, then that's not good either.

lavinpj1
07-04-2006, 07:43 AM
You don't need to do that. It is a slow way to do things. Use the ultra fast mysql function LOAD. An example would be...


LOAD DATA LOCAL INFILE '/tmp/mycsv.txt' INTO TABLE `my_table` FIELDS TERMINATED BY ' ' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

Clearly, I don't know your CSV layout, so you would have to modify the above query, or click "Insert data from a text file into the table" in phpmyadmin if you have it.

~Phil~

imatrox05
07-04-2006, 07:45 AM
Since i'm not going to do any date manipulation i changed the field type to varchar, and yet i'm getting the same result.

Thanks lavinpj1 i'll try your suggestion



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum