Rectangle 27 1

I think you mean that you want to not insert the first row of the CSV data into your table. Working on that assumption (note that I'm not a PHP programmer, so the changes I've made should be taken as pseudocode):

if ($_FILES[csv][size] > 0) { 
    $first_time = true;

    //get the csv file 
    $file = $_FILES[csv][tmp_name]; 
    $handle = fopen($file,"r"); 

    //loop through the csv file and insert into database 
    do {
            if ($first_time == true) {
            $first_time = false;
            continue;
        }

        if ($data[0]) { 
            mysql_query("INSERT INTO lead (name, lead_value, status) VALUES 
                ( 
                    '".addslashes($data[0])."', 
                    '".addslashes($data[1])."', 
                    '".addslashes($data[2])."' 
                ) 
            "); 
        } 
    } while ($data = fgetcsv($handle,1000,",","'")); 

    //redirect 
    header('Location: import.php?success=1'); die; 
}

@Mike...Yeah am trying to make the script ignore the first row of the csv file, as the user might upload files with heading, i dont want that heading row to get inserted into mysql database as row.. I tried making above changes in the code, but it did't made any difference Mike..

php - Removing the first row in a csv file upload to mysql database - ...

php mysql
Rectangle 27 2

example code from what I'm using, and it should work because I'm using it in production environment

$handle = fopen("stuff.csv","r");

/* assuming that first row has collumns names */
$query = "INSERT INTO table(";
$cols = fgetcsv($handle,100000,';','"');
$query .= implode(", ",$cols).") VALUES";

$values = "";

    /* cycle through each row and build mysql insert query */
while($data = fgetcsv($handle,100000,';','"')) {
    $values .= " ( ";

    foreach($data as $text) {

        $values .= "'".$text."', ";

    }

    $values = substr($values,0,-2);

    $values .= "), ";

}

    /* remove last 2 chars */

$values = substr($values,0,-2);

$query .= $values;

echo $query;

fclose($handle);

Mind the fact that this script will return the mysql query... not execute it, so alter it to your needs.

its not working cristi

it should now , example of what I'm using for csv uploads

Thanks cristi...it works with me and i got a solution for it..i appreciate ur help..!

php - Removing the first row in a csv file upload to mysql database - ...

php mysql
Rectangle 27 2

A simple LOAD DATA INFILE will work just fine. It will also be a lot faster.

<?php
//connect to Database
mysql_select_db($database_csv, $csv);

if (isset($_POST['submit'])) {

    $tmp = $_FILES['filename']['tmp_name'];
    $name = $_FILES['filename']['name'];

    //Can be any full path, just don't end with a /. That will be added in in the path variable
    $uploads_dir = 'C:/Users/Ernest/Desktop';

    $path = $uploads_dir.'/'.$name;

    if(move_uploaded_file($tmp, $path)){
        echo "<br><center><p>". $name ."</p></center>";

        //Import uploaded file to Database
        //If the query fails, try LOAD DATA LOCAL INFILE
        $import = "
        LOAD DATA INFILE '".$path."'
               INTO TABLE results  CHARACTER SET utf8 FIELDS TERMINATED BY ','
               OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (name, description, price, shipping, quantity);
        ";

        mysql_query($import) or die(mysql_error());
        //If you do not want to keep the csv, you can delete it after this point.
        //unlink($path);

    }else{
        echo 'Failed to move uploaded files';
    }

}
?>

i tried it but still got the following error:File 'C:xampp mpphp6A79.tmp' not found (Errcode: 22)

It appears to be the same error: File 'C:xampp mpphpE5D0.tmp' not found (Errcode: 22). But when i make my file location known it works like C:/Users/Ernest/Desktop/Book1.csv in the LOAD DATA INFILE. Thanks please help.

The tmp_name is the problem now... 'C:xampp mpphpE5D0.tmp' not found

I updated my answer with moving the uploaded file to your desktop and then LOAD DATA INFILE with that path. You can change that path to whatever you would like. Also, make sure the file is moved to a different folder than the source file folder.

File Upload for csv file into Mysql/PHP - Stack Overflow

php mysql file csv upload
Rectangle 27 1

If I am understanding you correctly you want to remove the header row? I would use LOAD DATA instead of INSERT INTO

LOAD DATA INFILE '/tmp/test.csv' INTO TABLE test FIELDS TERMINATED BY ',' IGNORE 1 LINES;

i had to use Load Data once or twice and it was really useful (over 1 million rows)

php - Removing the first row in a csv file upload to mysql database - ...

php mysql
Rectangle 27 6

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
}
?>

@Saiyam Patel Read question carefully .."i know how to upload a csv file into mysql database but only through cmd. i want to know how to upload csv file into mysql database using php"

will these work if the important data starts from row 10 and the previous rows contains information but they are not really that important,so the importing has to skip rows 1-9 and starts only from row 10? thanks

uploading csv file into mysql through php - Stack Overflow

php mysql csv
Rectangle 27 3

Bear in mind the "client" from MySQL's perspective is the application which connects directly to it. In your case, that's PHP. The fact that PHP's "client" is the user's web-browser is irrelevant - and you almost certainly don't want your users connecting directly to the MySQL server.

So, you might use LOCAL to pass the file from PHP to MySQL if they're running on different machines, but that'd be unnecessary if they're on the same machine and MySQL has access to read from wherever PHP has written the file.

It really depends what you're going to do with the data, but on the whole - yes, put it all in one table.

In the LOAD DATA statement you can tell MySQL which columns in the CSV map to which in the table - you could either make some guesses based on the file contents (especially if it includes a header row), or based on a hint provided by the user e.g. from which software the CSV originated; or, if you can't decide how the CSV maps to the table, you could present a short sample to the user in a browser and ask them to resolve it.

What is syntax to map columns in the csv file to the table? I suppose I could read all the column heads and use a switch so cases first, firstname, etc. all map to first and zip, zipcode etc. all map to zip..

PHP/MYSQL Upload, import .csv file to mysql-process-table design - Sta...

php mysql csv upload
Rectangle 27 1

You can use the MySQL LOAD DATA INFILE statement to bulk-insert thousands of records at once. PHP can handle the file upload. The PHP code would be something similar to:

$query = sprintf("
    LOAD DATA LOCAL INFILE '%s'
    INTO TABLE `table1`
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\\r\\n'
    IGNORE 1 LINES
    ",
    mysql_real_escape_string($FILES["file1"]["tmp_name"])
);

The LOCAL keyword should allow you to workaround some security restrictions. Change the FIELDS TERMINATED BY and LINES TERMINATED BY parameter to match the separators used by excel while exporting. IGNORE 1 LINES tells MySQL to skip the header row(s).

Note: Excel does not seem to use an escape character; but it will (i) enclose the fields that contain , and " with " (ii) use "" to escape a single " inside data. I believe MySQL will understand this encoding and import the data correctly.

thanks, i will try that..thanks a lot for your help.

hi i used your php script and it worked, i modified it a bit. however, how would i able to check if one data is already in the database so that if the user accidentally selected the same csv file, an error will pop and inform him that some data are already in the database. thanks.

i used the ignore or replace keyword somewhere in the statement. thanks

uploading csv file into mysql through php - Stack Overflow

php mysql csv
Rectangle 27 2

Try this it's working well, you can add as many values as possible depending on the number of columns you have in the CSV file. Then in the HTML code put the uploading syntax in the tag.

$fname = $_FILES['csv_file']['name'];     
$chk_ext = explode(".",$fname);             

        $filename = $_FILES['csv_file']['tmp_     name'];   
$handle = fopen($filename, "r");      
if(!$handle){
die ('Cannot open file for reading');
}      
              while (($data = fgetcsv($handle,     10000, ",")) !== FALSE)
{
          $query = "INSERT INTO tablename       (col1_csv, col2_csv)
values ('$data[0]', '$data[1]');
mysql_query($query) or die(mysql_error    ());
}
 fclose($handle);
?>

but it does not say that i can skip a few rows before importing.

Skip a few rows? Inside the CSV you are uploading or you have a record there that you'll need to overwrite so that it does not appear twice. If that is the case you can apply DUPLICATE in the mysql query.

no, for example the data that needs to be imported will start in row number 10 since rows 1-9 are insignificant text.

thanks a lot for the help, guys..

uploading csv file into mysql through php - Stack Overflow

php mysql csv
Rectangle 27 1

As @drew wisely mentioned in his comments above, you are missing single quotes around the file name in your LOAD DATA statement, which can mess things up. You may also be missing a few other things which would prevent it from working.

$uploaded = $_FILES["fileupload"]["name"];
`$try = mysqli_query($connect,"LOAD DATA INFILE IGNORE '$uploaded' INTO TABLE allrecords FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY ',,,\r\n'");`

Here is that LOAD DATA command as separate (readable) query:

LOAD DATA INFILE IGNORE '$uploaded'
INTO TABLE allrecords
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY ',,,\r\n'

Note carefully that the double quote in OPTIONALLY ENCLOSED BY has to be escaped so PHP doesn't choke.

And as another aside comment, LOAD DATA is fairly primitive with regard to what it can do handling data. It is intended as a workhorse to bring in raw data to MySQL. For complex filtering and manipulations of your raw data, you are probably better off doing the work in MySQL proper.

what did you mean better off doing the work in MySQL proper? Did you mean INSERT each rows one by one in a loop? I was going to do that first but I thought it would make the process run very slow?

I mean that if you have complex rules for selectively retaining/excluding certain records, you are better off just reading in everything in one shot and then cleaning up the table inside MySQL where you have a wealth of tools to help you do that.

$try = mysqli_query($connect,"LOAD DATA INFILE IGNORE '$uploaded' INTO TABLE allrecords FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ',,,\r\n'");
Parse error: syntax error, unexpected '' LINES TERMINATED BY '' (T_CONSTANT_ENCAPSED_STRING) in /home/hidden/public_html/subfolder/loader.php on line 41

PHP MySQL LOAD DATA INFILE from CSV File Upload - Stack Overflow

php mysql csv
Rectangle 27 1

<form action="upload_target.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file"><br>
<input type="submit" name="submit" value="Submit">
</form>
upload_target.php
if ($ext == "csv" && $_FILES["file"]["error"] == 0)
{
    $target = "upload/" . $_FILES["file"]["name"];
    move_uploaded_file($_FILES["file"]["tmp_name"], $target);

    if (($handle = fopen($target, "r")) !== FALSE) 
    {
        while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) 
        {
            print_r($data);
        }

        fclose($handle);
    }
}

Very basic and with very few checks / validations. The print_r($data) contains one line of your csv that you can now insert in your database.

However, I would recommend using PDO or MySQLi for that task, since the mysql functions of PHP will be deprecated in the future.

php - how to upload a csv file and update the mysql db? - Stack Overfl...

php mysql file-upload csv
Rectangle 27 1

First, your form MUST have the enctype set, as follows:

<form enctype="multipart/form-data"  action="index.php" method="post">

Otherwise, it will not accept file uploads.

Once you've done that, then you can access the file using the $_FILES variable. After the file has been uploaded, then you can access it like so:

if (isset($_FILES["scv"])) {
    $file = $_FILES["scv"];
    $file_name = $file["name"];
    $ext = pathinfo($file_name, PATHINFO_EXTENSION);
    if ($ext!="CSV" && $ext!="TXT") {
        die('The file must be csv or txt format.');
    }
    $saveto_path_and_name = '/path/to/file.csv'; // Where you want to save the file
    move_uploaded_file($file["tmp_name"], $saveto_path_and_name);
}

Once you've saved the file, you then can open it and import it. That's not trivial to do, but here's some primer code:

// Open the file for reading
$handle = @fopen($saveto_path_and_name, "r") or die(__("Unable to open uploaded file!", "inventory"));
// Grab the first row to do some checks
$row = fgets($inv_file, 4096);
// See if it's comma or tab delimited
if (stripos($inv_row, "\t")) {
    $sep = "\t";
} else {
    $sep = ",";
}

while ( ! feof($handle)) {
    $rowcount = 0;
    // Get the individual fields
    $inv_fields = explode($sep, $inv_row);
    $fields = array();
    // Iterate through the fields to do any sanitization, etc.
    foreach ($inv_fields as $field) {
        // Highly recommended to sanitize the variable $field here....
        $fields[] = $field;
        $rowcount++;
}
    // This is where you would write your query statement to insert the data
    // This is just EXAMPLE code.  Use the DB access of your choice (PDO, MySQLi)
    $sql = vsprintf('INSERT INTO `table` (`column`, `column2`, ...) VALUES (%s, %d, ...)', $fields);
    // Get the next row of data from the file
    $row = fgets($inv_file, 4096);
}

php - how to upload a csv file and update the mysql db? - Stack Overfl...

php mysql file-upload csv
Rectangle 27 1

i can import in phpmyadmin but then this is a gui for the user to import the data into the db. i am a beginner in php and mysql so not so sure how to go about it.

as long as the csv values (seperated by comma) match exactly to the fields in the db_table, then it will import directly. Otherwise, you will have data in the incorrect places. Remember that if a column is empty to have it as ,, in the csv file.

yes, understand that but hw if col1, col2, col3 should be imported to table1 and col4, col5, col6 should be imported into table2 using a csv file?

they would need to be split up into different tables. use excel to open the csv and just split the columns up

@mlishn: Surely the point of scripting tasks like this is to prevent having to jump through those kind of hoops in excel (other spreadsheet programs are available :-))?

php - upload and import csv file into multiple table in mysql - Stack ...

php mysql csv import
Rectangle 27 1

As the another variant proposed above, you can read your CSV line-by-line and explode each line into fields. Each field will corresponds one variable.

$handle = fopen("/my/file.csv", "r"); // opening CSV file for reading
if ($handle) { // if file successfully opened
    while (($CSVrecord = fgets($handle, 4096)) !== false) { // iterating through each line of our CSV

        list($field1, $field2, $field3, $field4) = explode(',', $CSVrecord); // exploding CSV record (line) to the variables (fields)

        // and here you can easily compose SQL queries and map you data to the tables you need using simple variables

    }
    fclose($handle); // closing file handler
}

It is better to use fgetcsv php.net/manual/en/function.fgetcsv.php instead of fgets. in this case list and explode can removed.

@BerndOtt, you are right! :), but it will not reduce the code row if you need to map data to variables (bec. you will get list (array)). But if its enough to use just array - your proposition is better!

I would say: it's about reading csv files. The fgetcsv cares about escaping and maybe about that RFC 4180. The Explode function does not. The array is nice, because you can use it directly with some db (e.g. sqlsvr) connectors a parameter for preventing sql injections.

php - upload and import csv file into multiple table in mysql - Stack ...

php mysql csv import
Rectangle 27 1

Table1 has 3 fields: name, age and sex. Table2 has 2 fields: haircolour, shoesize. So your CSV could be laid out like:

john smith,32,m,blonde,11
jane doe,29,f,red,4
anders anderson,56,m,grey,9

For the next step you will be using the function fgetcsv. This will break each line of the csv into an array that you can then use to build your SQL statements:

if (($handle = fopen($mycsvfile, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        // this loops through each line of your csv, putting the values into array elements
        $sql1 = "INSERT INTO table1 (`name`, `age`, `sex`) values ('".$data[0]."', '".$data[1]."', '".$data[2]."')";
        $sql2 = "INSERT INTO table2 (`haircolour`, `shoesize`) values ('".$data[3]."', '".$data[4]."')";
    }
    fclose($handle);
}

Please note that this does not take any SQL security such as validation into account, but that is basically how it will work.

php - upload and import csv file into multiple table in mysql - Stack ...

php mysql csv import
Rectangle 27 1

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE tmp_table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
;

Then calculate the id in the insert statement.

INSERT INTO questions (id, cat, question, a1, a2, a3, a4)
SELECT 
cat - 1 + 1000 + sq.number as id,
cat, question, a1, a2, a3, a4
FROM tmp_table_name t
JOIN (SELECT cat, count(*) as number FROM tmp_table_name GROUP BY cat) sq
ON t.cat = sq.cat

P.S.: Column names like a1, a2, a3 and a4 (I assume a is short for answer?) smells like very bad database design. Put the answers in a separate table and in one column.

Table questions:
id
cat
questionId
question
answers
Table answers:
questionId
answer

The tables are linked via questionId

php - Input CSV File and upload it to MySQL DB - Stack Overflow

php mysql csv mysqli
Rectangle 27 1

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE tmp_table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
;

Then calculate the id in the insert statement.

INSERT INTO questions (id, cat, question, a1, a2, a3, a4)
SELECT 
cat - 1 + 1000 + sq.number as id,
cat, question, a1, a2, a3, a4
FROM tmp_table_name t
JOIN (SELECT cat, count(*) as number FROM tmp_table_name GROUP BY cat) sq
ON t.cat = sq.cat

P.S.: Column names like a1, a2, a3 and a4 (I assume a is short for answer?) smells like very bad database design. Put the answers in a separate table and in one column.

Table questions:
id
cat
questionId
question
answers
Table answers:
questionId
answer

The tables are linked via questionId

php - Input CSV File and upload it to MySQL DB - Stack Overflow

php mysql csv mysqli
Rectangle 27 1

See. I change it, but when I click the upload it returns blank page and still the csv is not saving in the database

@User014019 There is may be some error happening. Add error_reporting(E_ALL); ini_set('display_errors'); on top of your script and execute it again. If there are any errors, you will see them.

I add that little code on top, still im getting this 404: File Not Found after I upload

@User014019 What was the problem?

How to upload csv file using php into mysql? - Stack Overflow

php mysql csv fgetcsv
Rectangle 27 3

I was able to successfully imported csv file and stored it in the mysql database.

Here are the the codes (actually its almost the same as my question with some slight changes with great effect):

index.php
<form action="csv_import.php" method="POST" enctype="multipart/form-data"  >
 <input type="file" name="csv_file" />
 <input type="submit" name="upload" value="Upload" />
</form>
csv_import.php
<?php
 if ($_FILES['csv_file']['error'] > 0) {
  echo "Error: " . $_FILES['csv_file']['error'] . "<br />"; 
 }else{ 
  if (($handle = fopen($_FILES['csv_file']['tmp_name'], "r")) !== FALSE) {

   $dbconn = mysql_connect("localhost", "root", "") or die("Couldn't connect to server!");
   mysql_select_db("csv_test") or die("Couldn't find database!");

   $ctr = 1; // used to exclude the CSV header

   while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    if ($ctr > 1) mysql_query("INSERT INTO ninja_exer (name, village, country) VALUES ('$data[1]', '$data[2]', '$data[3]')");
    else  $ctr++;
   }
   fclose($handle);
  }
 }
?>

csv - How to get full filepath when uploading files in PHP? - Stack Ov...

php csv fopen filepath fgetcsv
Rectangle 27 1

tmp_name in array of file is always same not dependent on name of input type of file.

Just replace this below line: $handle1 = fopen($_FILES['filename1']['tmp_name'], "r");

php - uploading 2 file csv into mysql table with single input ? it's p...

php mysql csv
Rectangle 27 1

You are using tmp_name1 on $handle1 Change it to $_FILES['filename1']['tmp_name']

what error you are getting??

php - uploading 2 file csv into mysql table with single input ? it's p...

php mysql csv