Read CSV file and insert to DB

[coolguyraj]

Hi,

How can read a CSV file, Convert the columns in CSV to rows and save it into a database table using PHP. The number of columns is dynamic.

Example:
CSV file data:

Test1 Test2 Test 3
1 sss ppp
2 sss ppp
3 sss ppp

After reading the CSV file the data has to be converted and saved as below in the database:

Test1 1 2 3
Test2 sss sss sss
Test3 ppp ppp ppp

Could you please help me to achive this.

Thanks

[MarkR]

Look at fopen(), fread(), str_explode() and mysqli.

You can use fopen/fread to open your file and read it line by line, str_explode to grab your csv elements in each line as an array, and mysqli to store it.

[Will Bontrager]

fgetcsv() can also be used. It reads a line for fields in CSV format and returns an array with the fields.

How-to: http://us2.php.net/manual/en/function.fgetcsv.php

Will

[Len Whistler]

Originally Posted by coolguyraj

Hi,

How can read a CSV file, Convert the columns in CSV to rows and save it into a database table using PHP. The number of columns is dynamic.

Why would you even want to do this? DB columns are not dynamic, and there is no reason to turn rows into columns and columns into rows. This would be a good php brain teaser though.


---

[coolguyraj]

Originally Posted by Len Whistler

Why would you even want to do this? DB columns are not dynamic, and there is no reason to turn rows into columns and columns into rows. This would be a good php brain teaser though.


---

I am clear till the point a import the data and add it to an array using php explode statement. I need help on the next part how do i convert the columns into rows, I have to leave out the first column and convert all the columns from the second onwards to rows. The reason is that the first column in the CSV is the header for the target table in mysql.

The CSV file contains the data for different models of the same type of machine and same type of parts, the parts of every model are put in different columns in CSV file i have to convert them into rows and insert to mysql table.

The example data in the csv file is as below:

Type B1 C1 C2
Motor 23A 23A 23U
Fins FRC FRC FRZ
Gear 3B12 3B12 3B33
Handle XL XL XL
Pully Normal Normal Normal


In the above data, the first column will be the table haeder in mysql table, so we ignore it while insering to the mysql table we take only from the second column. Hope i am clear.

[Len Whistler]

Originally Posted by coolguyraj

The example data in the csv file is as below:

Type B1 C1 C2
Motor 23A 23A 23U
Fins FRC FRC FRZ
Gear 3B12 3B12 3B33
Handle XL XL XL
Pully Normal Normal Normal

I suggest rewrite the data to a new CSV file formatted correctly, then insert into a database. The code below appears to be OK, but will need additional code to remove the last comma or whatever CSV character you use.

Work in progress

PHP Code:

<?php
$handle = fopen("db.csv", "r");
while ($data = fgetcsv($handle, 1000, " ")) {
$column1 .="$data[0],";
$column2 .="$data[1],";
$column3 .="$data[2],";
$column4 .="$data[3],";
$column5 .="$data[4],";
}
echo "$column1<br>";
echo "$column2<br>";
echo "$column3<br>";
echo "$column4<br>";
echo "$column5<br>";
fclose($handle);
?>

Ouput

Type,Motor,Fins,Gear,Handle,Pully,
B1,23A,FRC,3B12,XL,Normal,
C1,23A,FRC,3B12,XL,Normal,
C2,23U,FRZ,3B33,XL,Normal,

EDIT: You can use substr(); to remove the last character from each row, which in this case is the comma.


----

[Len Whistler]

The code below appears to work. It will rewrite the old.csv file to a new.csv file, which is properly formatted to insert into DB.

PHP Code:

<?php
error_reporting(0);

$old_file = "old.csv";
$new_file = "new.csv"; // use this file to insert into DB.
$csv_character = ",";

$lines = COUNT(FILE($old_file));
$lines=$lines-2;

$old_fh = fopen($old_file, "r");
while ($data = fgetcsv($old_fh, 1000, " ")) {
for ($i=0; $i<$lines; $i++) {
${column.$i} .="$data[$i]$csv_character";
}
}

$new_fh = fopen($new_file, "w");
for ($i=0; $i<$lines; $i++) {
$results=${column.$i};
$results = substr($results, 0, -1);
fwrite($new_fh,"$results\n");
echo "$results<br>";
}
fclose($old_fh);
fclose($new_fh);
?>

old.csv

Type B1 C1 C2
Motor 23A 23A 23U
Fins FRC FRC FRZ
Gear 3B12 3B12 3B33
Handle XL XL XL
Pully Normal Normal Normal

new.csv

Type,Motor,Fins,Gear,Handle,Pully
B1,23A,FRC,3B12,XL,Normal
C1,23A,FRC,3B12,XL,Normal
C2,23U,FRZ,3B33,XL,Normal

[coolguyraj]

Originally Posted by Len Whistler

The code below appears to work. It will rewrite the old.csv file to a new.csv file, which is properly formatted to insert into DB.

PHP Code:

<?php
error_reporting(0);

$old_file = "old.csv";
$new_file = "new.csv"; // use this file to insert into DB.
$csv_character = ",";

$lines = COUNT(FILE($old_file));
$lines=$lines-2;

$old_fh = fopen($old_file, "r");
while ($data = fgetcsv($old_fh, 1000, " ")) {
for ($i=0; $i<$lines; $i++) {
${column.$i} .="$data[$i]$csv_character";
}
}

$new_fh = fopen($new_file, "w");
for ($i=0; $i<$lines; $i++) {
$results=${column.$i};
$results = substr($results, 0, -1);
fwrite($new_fh,"$results\n");
echo "$results<br>";
}
fclose($old_fh);
fclose($new_fh);
?>

old.csv



new.csv

Works Perfectly. Thanks for your help.