Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Nov 2005
    Posts
    93
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Read CSV file and insert to DB

    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
    Last edited by coolguyraj; 08-17-2012 at 07:46 AM.

  • #2
    New Coder
    Join Date
    Sep 2011
    Posts
    80
    Thanks
    0
    Thanked 13 Times in 12 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Jun 2012
    Location
    Near Chicago, USA
    Posts
    123
    Thanks
    7
    Thanked 19 Times in 19 Posts
    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
    Numerology API for apps - Facebook, iPad, mobile phones. No charge to use API. [info]

  • #4
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Quote Originally Posted by coolguyraj View Post
    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.


    ---
    Leonard Whistler

  • #5
    New Coder
    Join Date
    Nov 2005
    Posts
    93
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Len Whistler View Post
    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.

  • #6
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Quote Originally Posted by coolguyraj View Post
    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($handle1000" ")) {
    $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
    Code:
    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.


    ----
    Last edited by Len Whistler; 08-18-2012 at 08:17 AM.
    Leonard Whistler

  • #7
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    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_fh1000" ")) {
    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($results0, -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
    Leonard Whistler

  • Users who have thanked Len Whistler for this post:

    coolguyraj (08-22-2012)

  • #8
    New Coder
    Join Date
    Nov 2005
    Posts
    93
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Len Whistler View Post
    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_fh1000" ")) {
    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($results0, -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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •