Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

copy data from csv file into mysql

firestar_lenefirestar_lene Member Posts: 33
how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?

then ,how i can get data from csv file into mysql using php coding?any body can provide the coding to me?

Comments

  • DarQDarQ Member Posts: 1,625
    : how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?
    :
    : then ,how i can get data from csv file into mysql using php coding?any body can provide the coding to me?
    :

    simple
    with fopen and fgets you read the entire file line for line
    each line is a row in your database
    with explode you can get the comma seperated fields in a numeric array
    now, you only need sql INSERTS to put them in the db

    [size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
    http://mark.space.servehttp.com

  • ManningManning Member Posts: 1,621
    : : how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?
    : :
    : : then ,how i can get data from csv file into mysql using php coding?any body can provide the coding to me?
    : :
    :
    : simple
    : with fopen and fgets you read the entire file line for line
    : each line is a row in your database
    : with explode you can get the comma seperated fields in a numeric array
    : now, you only need sql INSERTS to put them in the db

    There's actually an fgetcsv() function you can use to read directly into a numeric array. Saves a step or two using that.
  • firestar_lenefirestar_lene Member Posts: 33
    : : : how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?
    i have found this coding that can display the data from csv but the problem is it didnt insert the data into mysql?why is this happen?

    <?php


    ## Connect to a local database server (or die) ##
    $dbH = mysql_connect('localhost', '', '') or die('Could not connect to MySQL server.<br>' . mysql_error());

    ## Select the database to insert to ##
    mysql_select_db('apple_sql') or die('Could not select database.
    ' . mysql_error());



    $file_name= $_FILES['csvfile']['name'];

    $columnheadings = 0; ##columnheadings,whether there is field name in csv file##
    $pass = 0;
    $fail = 0;

    $row = 1;
    $handle = fopen ("$file_name","r");
    while ($data = fgetcsv ($handle, 1000, ",")) {
    $num = count ($data);
    echo "

    $num fields in line $row:

    ";
    $row++;
    for ($c=$columnheadings; $c < $num; $c++) {
    echo $data[$c];
    $insertrecord = "Insert Into `details` Values ($data[$c])";
    mysql_query($insertrecord);
    if(mysql_error()) {
    $fail += 1; # increments if there was an error importing the record
    }
    else
    {
    $pass += 1; # increments if the record was successfully imported
    }
    }
    }

    echo "fail" .$fail."";
    echo "pass" . $pass;

    fclose ($handle);
    ?>



    : : :
    : : : then ,how i can get data from csv file into mysql using php coding?any body can provide the coding to me?
    : : :
    : :
    : : simple
    : : with fopen and fgets you read the entire file line for line
    : : each line is a row in your database
    : : with explode you can get the comma seperated fields in a numeric array
    : : now, you only need sql INSERTS to put them in the db
    :
    : There's actually an fgetcsv() function you can use to read directly into a numeric array. Saves a step or two using that.
    :

  • ManningManning Member Posts: 1,621
    : : : : how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?
    : i have found this coding that can display the data from csv but the problem is it didnt insert the data into mysql?why is this happen?
    :
    : <?php
    :
    :
    : ## Connect to a local database server (or die) ##
    : $dbH = mysql_connect('localhost', '', '') or die('Could not connect to MySQL server.' . mysql_error());
    :
    : ## Select the database to insert to ##
    : mysql_select_db('apple_sql') or die('Could not select database.' . mysql_error());
    :
    :
    :
    : $file_name= $_FILES['csvfile']['name'];
    :
    : $columnheadings = 0; ##columnheadings,whether there is field name in csv file##
    : $pass = 0;
    : $fail = 0;
    :
    : $row = 1;
    : $handle = fopen ("$file_name","r");
    : while ($data = fgetcsv ($handle, 1000, ",")) {
    : $num = count ($data);
    : echo "<p> $num fields in line $row:
    ";
    : $row++;
    : for ($c=$columnheadings; $c < $num; $c++) {
    : echo $data[$c];
    : [red]$insertrecord = "Insert Into `details` Values ($data[$c])";[/red]
    : mysql_query($insertrecord);
    : if(mysql_error()) {
    : $fail += 1; # increments if there was an error importing the record
    : }
    : else
    : {
    : $pass += 1; # increments if the record was successfully imported
    : }
    : }
    : }
    :
    : echo "fail" .$fail."";
    : echo "pass" . $pass;
    :
    : fclose ($handle);
    : ?>

    Your insert line up there (I coloured red) is going to try to insert each "column" from the CSV file as it's own row in the MySQL table because you have it in a loop. I'm guessing that's not right. Since we don't know your table layout we can't give an exact INSERT statement, but assuming all the data types are int types (so don't need quotes), you could use a shortcut like this:

    [code]
    while ($data = fgetcsv ($handle, 1000, ",")) {
    $num = count ($data);
    echo "

    $num fields in line $row:
    ";
    $row++;
    $insertrecord = "INSERT INTO Details VALUES (" . implode(",", $data) . ")";
    mysql_query($insertrecord);
    // and the rest of the code...
    [/code]

    If your columns aren't all numbers, so some need quotes, then you can't use the implode() shortcut and will have to build the $insertrecord line manually. The thing to remember is that you can only insert one row for each line you read from the file.

  • firestar_lenefirestar_lene Member Posts: 33
    thanks..i solve my problem already using other methods


    : : : : : how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?
    : : i have found this coding that can display the data from csv but the problem is it didnt insert the data into mysql?why is this happen?
    : :
    : : <?php
    : :
    : :
    : : ## Connect to a local database server (or die) ##
    : : $dbH = mysql_connect('localhost', '', '') or die('Could not connect to MySQL server.' . mysql_error());
    : :
    : : ## Select the database to insert to ##
    : : mysql_select_db('apple_sql') or die('Could not select database.' . mysql_error());
    : :
    : :
    : :
    : : $file_name= $_FILES['csvfile']['name'];
    : :
    : : $columnheadings = 0; ##columnheadings,whether there is field name in csv file##
    : : $pass = 0;
    : : $fail = 0;
    : :
    : : $row = 1;
    : : $handle = fopen ("$file_name","r");
    : : while ($data = fgetcsv ($handle, 1000, ",")) {
    : : $num = count ($data);
    : : echo "<p> $num fields in line $row:
    ";
    : : $row++;
    : : for ($c=$columnheadings; $c < $num; $c++) {
    : : echo $data[$c];
    : : [red]$insertrecord = "Insert Into `details` Values ($data[$c])";[/red]
    : : mysql_query($insertrecord);
    : : if(mysql_error()) {
    : : $fail += 1; # increments if there was an error importing the record
    : : }
    : : else
    : : {
    : : $pass += 1; # increments if the record was successfully imported
    : : }
    : : }
    : : }
    : :
    : : echo "fail" .$fail."";
    : : echo "pass" . $pass;
    : :
    : : fclose ($handle);
    : : ?>
    :
    : Your insert line up there (I coloured red) is going to try to insert each "column" from the CSV file as it's own row in the MySQL table because you have it in a loop. I'm guessing that's not right. Since we don't know your table layout we can't give an exact INSERT statement, but assuming all the data types are int types (so don't need quotes), you could use a shortcut like this:
    :
    : [code]
    : while ($data = fgetcsv ($handle, 1000, ",")) {
    : $num = count ($data);
    : echo "

    $num fields in line $row:
    ";
    : $row++;
    : $insertrecord = "INSERT INTO Details VALUES (" . implode(",", $data) . ")";
    : mysql_query($insertrecord);
    : // and the rest of the code...
    : [/code]
    :
    : If your columns aren't all numbers, so some need quotes, then you can't use the implode() shortcut and will have to build the $insertrecord line manually. The thing to remember is that you can only insert one row for each line you read from the file.
    :

  • DarQDarQ Member Posts: 1,625
    : : how to i save excel file into csv file?save as csv(comma delimited) or csv(ms dos) or other cvs?
    : :
    : : then ,how i can get data from csv file into mysql using php coding?any body can provide the coding to me?
    : :
    :
    : simple
    : with fopen and fgets you read the entire file line for line
    : each line is a row in your database
    : with explode you can get the comma seperated fields in a numeric array
    : now, you only need sql INSERTS to put them in the db
    :
    : [size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
    : http://mark.space.servehttp.com

    oh, and who clicked that link and tried to access the admin area? its a futile attempt guys.

    [size=5][italic][blue]Dar[RED]Q[/RED][/blue][/italic][/size]
    Jou my no rap dy lekkere dikke tsjap

Sign In or Register to comment.