Quick CSV import with visual mapping

development, php, php classes Add comments

Several years ago I created PHP class Quick_CSV_Import to import CSV files to a database table very quickly (LOAD DATA INFILE statement). I found that the class became quite popular in India due to feedback received :]

Now I want to share a little application on the basis of that class.

Quick CSV Import with Mapping” is a PHP example application that imports CSV file to a database table with visual mapping of CSV columns to table columns.

CSV Mapping Master - click to download

Of course, you can get a copy of the application source – just go to the SVN repository.

Don’t forget to send me your feedback and donations ;]

P.S. If you are having problems with going to step 2 while using the app, try the following:

  1. Open Quick_CSV_import.class and find “LOAD DATA INFILE
  2. change it to “LOAD DATA LOCAL INFILE

It’s connected with permissions at your Linux server. Thanks to Noel for the solution.

156 Responses to “Quick CSV import with visual mapping”

  1. 32 thủ thuật tăng tốc cho các câu truy vấn MySQL | LONGF Says:

    […] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times […]

  2. omran Says:

    To 82,89
    Hi all,
    Thanks for this amazing class!
    Regarding the following error:
    “Warning: Invalid argument supplied for foreach() in /csv_uploader/classes/Quick_CSV_import.class.php on line 79”
    I found that array_merge (in preload.inc) is not working properly. To fix it I have just replaced the line:
    $_SESSION[‘data’][‘mapping’] = $attributes[‘mapping’];
    by
    $_SESSION[‘data’][‘mapping’] = $_POST[‘mapping’];
    Hope this will helps you guys 🙂

  3. omran Says:

    Oups sorry I forgot to say that you have to replace:
    $_SESSION[‘data’][‘mapping’] = $attributes[‘mapping’];
    by
    $_SESSION[‘data’][‘mapping’] = $_POST[‘mapping’];
    In actStep2.php (Line number 7)

  4. scrieler Says:

    Thanks for this nice work, it’s a good import basis.

    Sof: I got ‘Database user dbusername must have SELECT permission’ too, there is any bug in the permissions check at line 63 in file preload.inc
    you can avoid this message if you comment out this check, simply comment out the foreach( $necessaryPermissions as $perm ) loop

  5. Hakan Says:

    Great class. Thank you.

  6. jose Says:

    hello, great project, marketpress try, but never work out, make and edit time zone, ring, etc. .. but never succeed. I am doing wrong? thank you very much for the help.
    now active can test gzn.es/csvisual
    multistore

    Warning: date(): It is not safe to rely on the system’s timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected ‘Europe/Berlin’ for ‘CET/1.0/no DST’ instead in ../csvisual/classes/CSV.class.php on line 55 The used command is not allowed with this MySQL version

  7. Greg Monroe Says:

    Thank you for the script!

    Is there any way to specify a db field & value through the script rather than it being inside the csv file? The data that I’m importing needs to be assigned to a user in my system so I have a user_id db field that I need to set for each record. It will be the same for every import. I can’t figure out how to do this. Thanks in advance.

  8. jhon Says:

    hola amigo soy peruano. me encontre con el siguiente error

    Fatal error: Call to undefined function get_magic_quotes_gpc() in C:\AppServ\www\csv\preload.inc

    me podrias ayudar
    help XD

  9. Bankim Says:

    Hi Alexander!

    Thanks a ton for your contribution. I am trying to use your code; but when sate up all the code i am getting this error. “Database user root must have SELECT permission”. But in my php server mysql is enabled extension=php_mysql_mysqlnd.dll
    extension=php_mysql.dll
    extension=php_mysqli_mysqlnd.dll
    extension=php_mysqli.dll

    then also getting this message. please help me out.

    Thanks and regards,

    Bankim

  10. Andreas Says:

    I get this error message: Database user root must have SELECT permission

    Any idea???

  11. KB Boyd Says:

    This looks very promising! Thank you for sharing!

    The preload routines are choking on my database user’s permissions. The “SHOW GRANTS FOR CURRENT_USER” I have is pasted in below. Not sure if it is readable. Email me if you would like a text file of these results.

    I can fix my copy of your preload.inc to suit my situation but you may want to consider altering that routine.

    Thanks again! Peace.

    +—————————————————————————————————————————————————————————————————————————————————-+
    | Grants for myUser@xx.xx.xxx.x/xxx.xxx.xxx.x |
    +—————————————————————————————————————————————————————————————————————————————————-+
    | GRANT USAGE ON *.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ IDENTIFIED BY PASSWORD ‘hashhere’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name1`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name2`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name3`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name4`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name5`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name6`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `theDBConnectedTo`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name8`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db_name9`.* TO ‘myUser’@’xx.xx.xxx.x/xxx.xxx.xxx.x’ |
    +—————————————————————————————————————————————————————————————————————————————————-+
    10 rows in set (0.00 sec)

  12. N Says:

    This code is pretty broken right now – I spent quite a bit of time trying to get it to work with little result.
    1. DB queries were failing due to a trim($result) being called in db.class
    2. When I finally found that error, Step 3 failed and refreshed back to step 1 with no user feedback.

  13. Marino Says:

    Hola
    estoy probando el script y me obtengo este error:
    Warning: trim() expects parameter 1 to be string, resource given in D:\web\esfamilia\classes\DB.class.php on line 290
    Database user root must have SELECT permission

    es raro por que el usuario root tiene todos los privilegios, que debo hacer para superarlo. Gracias

  14. MarketPress Product Importer | Best Plugins - wordpress – widgets – plugin 2012 Says:

    […] CSV import directly into the plugin, but for now, I’m using CSVisual, an edited version of Quick CSV import with Visual Mapping by Alexander […]

  15. Hamid Says:

    Correction for (//if (trim($this->fResult)!=””)
    $this->fResult is an array so replace by :
    if (!empty($this->fResult))
    it will work perfectly and errors for privileges will also disapear.

  16. grass Says:

    for trim() function problem on DB.class line 290 and line 300, i just not use that function in DB.class by removing it, and the app works fine, i also commented line 50 to 77 in preload.inc

  17. sid Says:

    this is axtually works but i want sum addtions its do not rumove duplicate values so i want to remove duplicates from it ….

  18. sid Says:

    suppose if any case i import a wrong csv file so if i want to delete such files , how do i do this.i want to delete current imported wrong file. plz help me out

  19. Roli Says:

    Hi, like this program. Runs okay on localhost, but doe’s not on on my website. Got a warning and a fatal error:
    -> Warning: trim() expects parameter 1 to be string, resource given in csv_import/classes/DB.class.php on line 290
    -> Database user xxxxx must have SELECT permission
    How comes – the db_user has all privileges!
    Any idee would be appreciated.

  20. Petra Says:

    Hi there,

    i get this error by starting the script:
    Warning: trim() expects parameter 1 to be string, resource given in /var/www/web12/html/nexx/navigator/classes/DB.class.php on line 290 Database user web12 must have SELECT permission
    Database user web12 must have SELECT permission

    Please help
    Thank you

  21. LuckyCoder8 Says:

    What file we got to download to use this seemsly good software !! thanks!

  22. Hardik Says:

    I get the error “Cannot retrieve headers columns of the CSV file”

  23. Brett Says:

    I am trying to hard-code data for one of my columns, Campaign_ID.

    Here is my code from Quick_CSV_Import.class.php (the last line is my code):

    $sql = “LOAD DATA INFILE ‘”.@mysql_escape_string($this->file_name).
    “‘ IGNORE INTO TABLE `”.$this->table_name.
    “` FIELDS TERMINATED BY ‘”.@mysql_escape_string($this->field_separate_char).
    “‘ OPTIONALLY ENCLOSED BY ‘”.@mysql_escape_string($this->field_enclose_char).
    “‘ ESCAPED BY ‘”.@mysql_escape_string($this->field_escape_char).
    “‘ LINES TERMINATED BY ‘”. $this->line_separate_char .
    “‘ “.($this->use_csv_header ? ” IGNORE 1 LINES ” : “”) .”(“.implode(“,”, $fields).
    “‘ SET Campaign_ID = ‘”.$this->campaign_id;

    The problem is that i keep getting this error:


    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” SET Campaign_ID = ‘7’ at line 1″

    Can anyone help me????

  24. Loi Says:

    Ignore my last message, the fix works!!!!!!!!!

    “$_SESSION[‘data’][‘mapping’] = $attributes[‘mapping’];”
    for this one:
    “$_SESSION[‘data’][‘mapping’] = $_REQUEST[‘mapping’];”
    in actStep2.php line 7

  25. Loi Says:

    If I want the actual file name, where do I need to look in your code? If I look at $options[‘file_source”], then I get the temp directory and I don’t want that. could you help me please?

  26. Anand Says:

    Hi,

    I’m getting error after uploading and selecting table:
    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\wamp\www\testingpurpose\csvimporter\classes\functions.php on line 103
    Array

    On step2.

    Can anyone please provide me the fix or updated latest files?

    Thanks in advance.

  27. Anand Says:

    Hi,

    I cleared the issue on first step. And got the Mapping list in second step.

    But after I mapped fields click on submit it is redirecting me to step 11.

    Why? and what to do in this step.
    Please provide the steps asap.

    Thanks in advance.

  28. Pewe Says:

    Are any experienced developers still watching this article??
    I have a query about importing date/timestamp and need some help.

  29. Skakunov Alexander Says:

    Shoot.

  30. Pewe Says:

    One of the fields of the csv file is a date.
    I was wondering if you might be able to explain how this could be dealt with.
    The database field is is in the format ‘2013-03-20 14:17:00′ and the csv format is ’20/03/2013 14:17:12’.

    Any help would be appreciated.
    Thanks.

  31. Pete Says:

    Hi, anyone out there 🙂

    This is a great script!!! I’ve tried it locally on an old version of PHP which is fine but my host is more up to date.

    I get the error “File uploads will not work properly, without a PHP version > 4.1.0”

    Has anyone got a fix for this?
    Any help would be appreciated.
    Thanks.

  32. Karlsoon Says:

    I have this problem in step 3:

    Warning: stripslashes() expects parameter 1 to be string, array given in E:\server2go\htdocs\nuevaphpweb\csv\csvmapeado\trunk\preload.inc on line 33

    Warning: session_start() [function.session-start]: Cannot send session cache limiter – headers already sent (output started at E:\server2go\htdocs\nuevaphpweb\csv\csvmapeado\trunk\preload.inc:33) in E:\server2go\htdocs\nuevaphpweb\csv\csvmapeado\trunk\preload.inc on line 39

    Warning: Cannot modify header information – headers already sent by (output started at E:\server2go\htdocs\nuevaphpweb\csv\csvmapeado\trunk\preload.inc:33) in E:\server2go\htdocs\nuevaphpweb\csv\csvmapeado\trunk\preload.inc on line 40

    Anyone solved this error?

    P.D. Excuse me for my English.

  33. Tushar Says:

    hi..

    i got this error.

    Database user root must have SELECT permission..

    Please help.

  34. jaydip Says:

    1)
    Warning: trim() expects parameter 1 to be string, resource given in C:\wamp\www\trunk\classes\DB.class.php on line 290
    2)
    Database user root must have SELECT permission
    this two error in your apps when i am give database name=”my_database”

  35. jaydip Says:

    Disallowed Key Characters. error on second step

  36. moorthi Says:

    how to run Quick CSV import with visual mapping in window localhost xampp server in step by step? and
    the table not showed in dropdown….

    please help me

  37. moorthi Says:

    how to check already available record in table?
    if record is found to show one popup message and click ok add the new record click cancel update the record or not insert the record…

    how do this?

    please help me

  38. moorthi Says:

    how to remove duplicate values?

  39. moorthi Says:

    how to echo insert data to table query?

  40. jfd Says:

    nice script. A little late to the party but is there a way of adding hidden fields that can be mapped manually? ie the same question Brett asked in 123. (can’t see that it’s been answered) I need to assign an upload to a user id. I understand why Brett’s solution doesn’t work and i can’t figure out a way to add it without breaking it. I could do with a solution to this if anyone out there can help me? thanks!

  41. jfd Says:

    actually, sorry i worked it out! never mind!

  42. Hans H Says:

    I only use the old class from Quick_CSV_import class 1.5
    On my local machine import works just fine.
    On my website, using the same cdv file, same database structure, it gives no errors, but just doesn’t add the rows to the db table.
    What am I missing?
    Any suggestions greatly appreciated!
    Thanks

  43. joseph Says:

    any luck with the Trim errors?

    Warning: trim() expects parameter 1 to be string, resource given in

    for step 1.

  44. antony Says:

    Hi.. Thanks for this script it works great.. I simplified it a bit and made it custom for my site needs. I use the script in the public front end of my site, so database fields like “id, user_id, profile_id” cannot be filled in by the fron-end user from their csv file. Where or how can i add this information in manually in the back-end or in the (import() function ) so that it is added into the database automatically with each new entry..
    regards
    Antony

  45. Hurd Says:

    Hi loving what you have done here. I need a way to add 2 values to predefined columns in the db – one for ‘added by’ and another for ‘upload id’. can this be done? am happy to pay if this is a simple implementation. can you help!!!???

  46. medusk Says:

    Is there a quick way to map csv fields to more than one table. I have a csv file and some of the records go in table1, other records should go in table2, and others go in table3. For example, name, email go in table1.users, resumee text goes in table2.resumes etc. Your script has the option to import / insert in only one table when doing the maping. I can do all of the above by calling the csv file fields and insert each in whatever table but only using my own php cooked function. Any thoughts?

  47. Jsteban Says:

    tr : “Database user root must have SELECT permission”??

    someone could solve the problem: “Database user root must have SELECT permission”??

  48. Natejd04 Says:

    For those of you who are having the
    “Database user root must have SELECT permission” and have checked credentials, you can comment that section out.

    In preload.inc – Comment out line 63 thru 76.
    Line 63 starts with: if( !startsWith

  49. Natejd04 Says:

    For those of you who are having this error:
    Warning: trim() expects parameter 1 to be string, resource given in

    You might be able to turn it into an array.
    In DB.Class.php on Line 290
    original: if (trim($this->fResult)!="")
    revision: if (is_array($this->fResult)!="")

  50. Riz Says:

    Im using Amazon EC2 server and RDS for database. I have followed the steps but getting this error

    Warning: trim() expects parameter 1 to be string, resource given in /var/www/html/php/test/csv/classes/DB.class.php on line 290
    Database user test1 must have SELECT permission

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in