Importing CSV data using PHP MySQL

Question

I m having a bit of a problem trying to import data from a CSV and have a couple of questions on it that I haven t managed to solve myself yet   First off here s my code to help put things in perspective  tidied it up a bit  removing CSS and DB connection     lt body gt   lt div id  container  gt   lt div id  form  gt    lt  php  deleterecords    TRUNCATE TABLE tablename     empty the table of its current records mysql query  deleterecords      Upload File if  isset   POST  submit            if  is uploaded file   FILES  filename    tmp name               echo   lt h1 gt      File      FILES  filename    name      uploaded   successfully       lt  h1 gt            echo   lt h2 gt Displaying contents  lt  h2 gt            readfile   FILES  filename    tmp name                  Import uploaded file to Database      handle   fopen   FILES  filename    tmp name     r         while    data   fgetcsv  handle  1000            FALSE             import  INSERT into importing text number values   data 0     data 1               mysql query  import  or die mysql error                fclose  handle        print  Import done      view upload form   else        print  Upload new csv by browsing to file and clicking on Upload lt br   gt  n        print   lt form enctype  multipart form-data  action  upload php  method  post  gt         print  File name to import  lt br   gt  n        print   lt input size  50  type  file  name  filename  gt  lt br   gt  n        print   lt input type  submit  name  submit  value  Upload  gt  lt  form gt          gt    lt  div gt   lt  div gt   lt  body gt    It s basically an adaptation of an example I have found after many many attempts at various methods   My CSV has two columns of data  the first one being text and the second is integers The table in the database also has two columns  the first called  text  and the second  number   So the questions I have are    the text being uploaded is just being displayed as 0 in every field and i m not sure why I keep reading about data ending up enclosed in     if that happens how would I sort it  how can I ignore the first X lines of the CSV for headers etc  is the data format changed throughout this process or is it ready for me to use in a graph  e g  would a decimal stay a decimal once placed in the database    I think that covers everything  thanks in advance for any help   EDIT   Just done a test of 10 000 record uploading and got the error     Fatal error  Maximum execution time of 30 seconds exceeded    any thoughts

User · Accepted Answer

I answered a virtually identical question just the other day: Save CSV files into mysql database

MySQL has a feature LOAD DATA INFILE, which allows it to import a CSV file directly in a single SQL query, without needing it to be processed in a loop via your PHP program at all.

Simple example:

<?php
$query = <<<eof
    LOAD DATA INFILE '$fileName'
     INTO TABLE tableName
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (field1,field2,field3,etc)
eof;

$db->query($query);
?>

It's as simple as that.

No loops, no fuss. And much much quicker than parsing it in PHP.

MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope that helps

User · Answer

i 0  while    data   fgetcsv  handle  1000            FALSE    if  i gt 0        import  INSERT into importing text number values     data 0         data 1            mysql query  import  or die mysql error        i 1

User · Answer

Database Connection    try            conn   mysqli connect  servername   username   password   db             echo  Connected successfully          catch  exception  e            echo  Connection failed       e- gt getMessage            Code to read CSV file and upload to table in database       file   fopen  filename   r                while    getData   fgetcsv  file  10000            FALSE                     sql    INSERT into db table                     values           getData 1             getData 2             getData 3             getData 4             getData 5             getData 6                           result   mysqli query  conn   sql                   if   isset  result                         echo   lt script type   text javascript   gt                              alert   Invalid File Please Upload CSV File                               window location     home do                              lt  script gt                      else                       echo   lt script type   text javascript   gt                          alert   CSV File has been successfully Imported                              window location     home do                        lt  script gt                                                fclose  file

User · Answer

letsay  infile   a csv   file needs to be imported   class blah    static public function readJobsFromFile  file                    if    handle   fopen  file   r        FALSE                 echo  readJobsFromFile  Failed to open file   file  n           die              header true       index 0      while    data   fgetcsv  handle  1000            FALSE                    ignore header         if   header    true                         header   false              continue                     if   data 0         amp  amp   data 1            u have oly 2 fields                       echo  readJobsFromFile  No more input entries n               break                                                           a        trim  data 0             b     trim  data 1                                  if  check if exists  SELECT count    FROM Db table WHERE a   a  AND b   b        true                              index                continue                                      sql    INSERT INTO DB table SET a   a    b   b              mysql query  sql  or die  readJobsFromFile      mysql error                          index               fclose  handle               return  index    no  of fields in database     function check if exists  sql     result   mysql query  sql  or die   sql --    mysql error     if    result         message     check if exists  Invalid query      mysql error       n        message     Query       sql      die  message       row   mysql fetch assoc   result    count    row  count       if   count  gt  0      return true  return false      infile a csv   blah  readJobsFromFile  infile       hope this helps

User · Answer

set time limit 10000     con   mysql connect  127 0 0 1   root   password    if    con        die  Could not connect      mysql error          mysql select db  db    con     fp   fopen  file csv    r     while   feof  fp        if    line   fgetcsv  fp  1000                    continue            importSQL    INSERT INTO table name VALUES     line 0         line 1         line 2             mysql query  importSQL  or die mysql error           fclose  fp   mysql close  con

User · Answer

i think the main things to remember about parsing csv is that it follows some simple rules   a it s a text file so easily opened b  each row is determined by a line end  n so split the string into lines first c  each row line has columns determined by a comma so split each line by that to get an array of columns  have a read of this post to see what i am talking about   it s actually very easy to do once you have the hang of it and becomes very useful

[php] Importing CSV data using PHP/MySQL

Examples related to php

Examples related to mysql

Examples related to database

Examples related to csv

Examples related to import