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.

151 Responses to “Quick CSV import with visual mapping”

  1. Riyaad Says:

    Hi,

    I am getting the following error:

    ‘Field separator argument is not what is expected; check the manual’

    Is there a manual?

  2. Skakunov Alexander Says:

    Hello Riyaad!

    Thanks for your interest!

    It’s error from MySQL side. Little investigation shows that MySQL is rather exacting if separator is concerned :) It seems you have a kind of custom separator.

    Try to define separator manually and carefully (don’t use auto-detect for your case).

    If it doesn’t help, try to open your file in notepad and replace your custom separator by comma or semicolon.

    Hope it helps.

  3. I want to be free » Blog Archive » 32 tips to speed up your queries Says:

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

  4. andras Says:

    Hello Alexander,
    Thanks for your class. It could be really helpfull for me.
    I have slightly modified it in order to use it with short_open_tag turned “Off” in your php.ini (which is often the default by now).
    You can download this version here if you like :
    http://www.jmandre.net/files/quick_csv_mapping_import.zip
    (only one file has been changed, plus readme.txt where I explain the change)

  5. dorian andres aldana Says:

    how many data register could import your aplication? Do you think it can handle about 1 million or more?

  6. Skakunov Alexander Says:

    I think it can.
    One day I imported 50 Mb data file in 0.16 seconds.

  7. dorian andres aldana Says:

    it does not work for me, i imported the file csv, when i go to the second step, i cannot define the fields mapping, the aplication send me back to the first step, why?

  8. Skakunov Alexander Says:

    The application store a step in session, so I think something is wrong with it:
    – check sessions are supported (though it’s a native PHP feature)
    – check cookies are enabled
    – check session store folder is writable and have enough room (usually it’s /tmp)
    – any other sessions related issues.

    Does your file have a header?

  9. Weblog de Paul Bernal » Importar datos CSV en aplicaciones PHP Says:

    [...] Esta aplicación ayuda a importar archivos CSV hacia una tabla de base de datos , permitiendo definir las columans del CSV que serán mapeadas hacia las columas de la tabla de la base de datos. Esta aplicación puede incluso sugerir automáticamente el caracter separador además de la coma. http://i1t2b3.com/2009/01/14/quick-csv-import-with-mapping/ [...]

  10. noel Says:

    I tried downloading from the repository and running the code but I don’t get any field mapping in step 2.

  11. Skakunov Alexander Says:

    2 noel: OK, what’s happening? Any errors? BTW, check my comment above.

  12. noel Says:

    I only got a warning “Warning: set_time_limit() [function.set-time-limit]: ”

    In actstep2, I added a var_dump on $arr_headers so see if it is returning something, but I only got a null array.

    $arr_headers = CSV::get_header_fields( $db, $options['file_name'], $options['encoding'], $options['field_separate_char'], $options['field_enclose_char'], $options['field_escape_char'] );

    var_dump($arr_headers);

  13. Skakunov Alexander Says:

    2 noel: is you PHP in safe mode? Maybe that’s the root of problem?

  14. noel Says:

    It seems like the problem is during get_header_fields. It calls the load_line and then calls convert_line.

    In convert line, I’ve added a

    var_dump($fQuickCSV);die();

    after the import.

    It actually has an error “The file ‘/var/www/web4/web/keyword/temp/csv/csvpkA8QL’ must be in the database directory or be readable by all”

    The permission for this newly created temporary file “csvpkA8QL” is 600 and is owned by user “apache”.

  15. noel Says:

    I tried with both Safe_mode=off and on but still the same.

  16. Skakunov Alexander Says:

    The permission for this newly created temporary file “csvpkA8QL” is 600 and is owned by user “apache”

    That’s the problem. Try to find “LOAD DATA INFILE” statement and add LOCAL keyword like: “LOAD DATA LOCAL INFILE”

  17. noel Says:

    It worked! Thank you very much.

    I found that line in Quick_CSV_import.class under function import.

  18. Skakunov Alexander Says:

    My pleasure.
    Added this solution to the post.

  19. Dorian Andres Aldana Says:

    I´ve followed your indication, my session variables work for me in my own applications, the cookies are enabled, an the folders are writable; I realized that when I´m gonna fill the form in the fields “Enclose char”,”separated chars” and “Escape char” I have to let them in blank to get the application works in the second step, it does not matter if I choose Use CSV header it works in both ways, but if my csv has ” or Inverted commas , the file is imported with it, a lot of thanks.

  20. Dorian Andres Aldana Says:

    Sorry I did not finish my question, What Happened? Why I don`t get to work correctly your application

  21. dixie Says:

    I don’t get to choose a database or table on step 1 there is no table or databases in the dropdown select box

  22. Skakunov Alexander Says:

    Dixie, send me a screenshot at i1t2b3(at)gmail.com

  23. Skakunov Alexander Says:

    Ok, Dixie, try to find and set to ON this setting in your php.ini and restart your web-server:

    short_open_tag = On

  24. dixie Says:

    Thats it thanks I can get to next page now. Is there a quick way to create the table columns for the csv file

  25. Skakunov Alexander Says:

    Is there a quick way to create the table columns for the csv file

    I’m not sure I follow, what do you mean?

  26. dixie Says:

    Can the script create the table columns in the database using the headers in the csv file. Or is there another quick way to do this or almost automatically. Thank you

  27. Graham Says:

    I also cannot get past step and have tried all the fixes mentioned here. My log file gives the following:
    LOAD DATA LOCAL INFILE ‘C:\\www\\vhosts\\localhost\\csv\\temp\\csv\\csv4C.tmp’ IGNORE INTO TABLE `temp_09_03_2009_11_11_24_58` FIELDS TERMINATED BY etc…
    my table name is gb_site and so I think this is wrong.
    I then get
    Field separator argument is not what is expected; check the manual

  28. Skakunov Alexander Says:

    No, currently you must create a table in advance.
    Actually, what you want can be done, but this application doesn’t allow this currently.

  29. Graham Struwig Says:

    Please help, I am new to php/mysql. I have tried all the options and fixes mentioned above and have also tried on three different websites, both localhost and online. I cannot get past step two. The window opens but does not give me the mapping options. When I say ok, it goes back to step one. My log file reads:

    [11-Mar-2009 17:17:54] LOAD DATA LOCAL INFILE ‘C:\\www\\vhosts\\localhost\\csv\\temp\\csv\\csv353.tmp’ IGNORE INTO TABLE `temp_11_03_2009_17_17_54_62` FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\\\”‘ ESCAPED BY ‘\\\\’ (`column1`,`column2`,`column3`,`column4`)

    I think the problem is, the table in my database is called ‘test’ and not `temp_11_03_2009_17_17_54_62` but dont know why it is doing this.

  30. Skakunov Alexander Says:

    I used main import class from my previous project, so it tryis to create an unique table name if it’s empty:

    if( empty($this->table_name) )
          $this->table_name = "temp_".date("d_m_Y_H_i_s");

    I think here must be an exception, not a new table name creation.

    Anyway, try to dump session at second step: to do that,
    1. go to actions/actStep2.php
    2. paste this after PHP opening tag:

    echo '<pre>'; 
    print_r($_SESSION['data']);
    exit;

    3. Submit it here.

  31. Graham Struwig Says:

    Thank you.

    I get

    Array
    (
    [file_name] => C:\www\vhosts\localhost\csv\temp\csv\php356.tmp
    [use_csv_header] => 1
    [field_separate_char] =>
    [field_enclose_char] => \”
    [field_escape_char] => \\
    [encoding] => utf8
    [table] => test
    )

  32. Skakunov Alexander Says:

    OK, seems to be OK. Remove this dump from code, refresh your page and send me a screenshot of what you get in your browser to i1t2b3(at)gmail.com

  33. richard Says:

    Hi -
    Working well but when try to import a date 4/3/2009 to a mysql date field does not work get 0000-00-00 all other fields are OK -
    What am I missing :-)
    thanks
    R

  34. Jon B. Says:

    I am also getting the same problem as Graham. Anything?

  35. Dave Says:

    Just what I am looking for, but I am having the same problem as Jon B and Graham. I can see where the .csv file is imported to the temp directory, but it appears that actStep2.php simply can not find the files. I am using Uniform Server to run php on a dedicated xp box. Uniform Server uses port 3321 for mySQL and port 8101 for Apache. I have config.inc set for host=localhost:3321 and step1 sees the database and tables, but I am thinking that the problem is that the later steps can’t find the files? Any help would be appreciated.

  36. Rawi Says:

    Hi,

    I just tried to use your script to import csv file. I am getting this error

    Parse error: syntax error, unexpected T_STRING, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or ‘}’ in /homez.145/gappassu/www/quick_csv_mapping_import/classes/CSV.class.php on line 7

    Could you please help me to resolve this issue?

    Best regards
    Rawi

  37. Skakunov Alexander Says:

    Sure. It seems you have PHP3. My advise is to upgrade it. If that’s not possible, remove public before functions names and replace public by var for variables (in the beginning of the class).

  38. Rawi Says:

    Hi.
    Thanks for your help. I have tested your script on a hosting provider where the php5 wasn’t enabled by default. I have enabled php5 by using .htaccess file with SetEnv PHP_VER 5
    php flag.

    Thank you so much for you help. Now the script is working fine. It was very help full for me as I am a newbie in PHP.

    Best regards
    Rawi

  39. Lucas Says:

    Hi,
    I am getting this error when I upload the files

    Parse error: syntax error, unexpected T_STRING, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or ‘}’ in importar_csv/classes/CSV.class.php on line 7

    Thanks!
    Francisco

  40. Skakunov Alexander Says:

    Read two comments above.

  41. Useful Tips To Speed Up Your MySQL Queries | guidesigner.net Says:

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

  42. Francis Says:

    Hi,

    I have a problem with [Step 2 out of 3] – Define fields mapping.

    I see a table Table | column | CSV header | CSV example | and a button with the text ‘I am ready…’, but that’s everything.

    When I click on the button the script goes back to step 1.

  43. Skakunov Alexander Says:

    Send me your file to i1t2b3(arobaz)gmail.com

  44. Francis Says:

    Hi Skakunov,

    Which file would you like to have?

  45. Billy Says:

    I am getting the following error messages:

    Warning: session_start() [function.session-start]: open(/usr/home/billing-services/public_html/sudo/csv/temp/sessions//sess_fb9a43dcc2bc81ec575507a060995029, O_RDWR) failed: Permission denied (13) in /usr/home/billing-services/public_html/sudo/csv/preload.inc on line 19

    Warning: session_start() [function.session-start]: Cannot send session cookie – headers already sent by (output started at /usr/home/billing-services/public_html/sudo/csv/preload.inc:19) in /usr/home/billing-services/public_html/sudo/csv/preload.inc on line 19

    Warning: session_start() [function.session-start]: Cannot send session cache limiter – headers already sent (output started at /usr/home/billing-services/public_html/sudo/csv/preload.inc:19) in /usr/home/billing-services/public_html/sudo/csv/preload.inc on line 19

    Warning: Cannot modify header information – headers already sent by (output started at /usr/home/billing-services/public_html/sudo/csv/preload.inc:19) in /usr/home/billing-services/public_html/sudo/csv/preload.inc on line 20

    Anyone have any idea why?

  46. Skakunov Alexander Says:

    I have one. Run this command:

    chmod 777 /usr/home/billing-services/public_html/sudo/csv/temp/sessions/

    to make your sessions folder writable.

  47. Eric Stone Says:

    I am having the same problem as billy. I just made the chmod 777 change to the sessions folder. The errors go away which is good. However, there is still no dsiplay of the fields being imported on step 2 and I get redirected back to step 1. I have also changed the permissions on the temp folder as well.

  48. Francis Says:

    I’m having the same problem as Eric and Billy :-(

    I see a table Table | column | CSV header | CSV example | and a button with the text ‘I am ready…’, but that’s everything.

  49. Skakunov Alexander Says:

    Francis, have downloaded a fresh version of the project? I have commited an update 2 days ago

  50. Quenerapu Says:

    Well I get fine into step 2, but step 2 looks exactly as Francis wrote. “Quick CSV Import with Mapping” downloaded today. Latest version. Ubuntu. chmod 777 for sessions done. Thanks in advance por your help Alexander. :)

  51. Eugene Says:

    I’m having problem with setting up. I can see all the checkboxes and fields but on the top of the page there are warning messages like these. And the drop down list is not populated as well.

    Warning: trim() expects parameter 1 to be string, resource given in C:\wamp\www\CSV\classes\DB.class.php on line 290

    Warning: trim() expects parameter 1 to be string, resource given in C:\wamp\www\CSV\classes\DB.class.php on line 300

  52. Rick Says:

    First error I had was”Parse error: syntax error, unexpected T_STRING, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or ‘}’ in /public_html/bmd/csv/classes/CSV.class.php on line 7″ and I am running PHP4/5 not PHP3. Even so, I deleted “public static” from all functions and replaced “public” with “var” for variables and now I get the following error “Parse error: syntax error, unexpected T_NEW in /public_html/bmd/csv/classes/Quick_CSV_import.class.php on line 47″. What is the matter here and how can I fix this?

  53. Jelmer Says:

    I’m have the same problem as Francis (post 48).
    Is there already a solution for this by anyone?

  54. Skakunov Alexander Says:

    Ok, guys, will check tonight.

  55. Skakunov Alexander Says:

    Well, guys, I commited a fresh version – download it, try again and report errors to me here.

    P.S. In next versions I am going to implement automatical table creation by CSV columns names (suggest by dixie in comment 24).

  56. greg Says:

    Hi guys, greetings to Alexander, thank you for the good job. I am a CS student from greece and i’m working on an application, it is needed to create th tables and their columns automatically. I ‘ve read about on your comment (53). I’m looking forward for it,but i’m trying too, using your classes as core, so if i ‘ll achieve it, i’ll inform you to be embedded in the project.

    If you are interested for my work so far or you have made any progress on it, you got my mail!

  57. Wouter Says:

    Hi Skakunov, a nice addition would be to be able to download the CSV from an URL (for example: affiliate feeds are usually setup to work via a url for downloading the csv file).

    This would then be an option besides uploading a file.

    Thanks for the great work by the way.

  58. Wouter Says:

    How can I download the whole set of files from the repository? I can only see a download option when I am in a single file page.

  59. Skakunov Alexander Says:

    Wouter: thanks for the suggestion! This came to my mind a couple of days ago as well ;] Although I think giving a URL is not a good practise (my script is not supposed to download files), providing a direct CSV file path can be useful by avoiding uploading issues.

    As for Assembla interface – yes, it’s worse now without download zip archieve. Try to use TortoiseSVN and make svn import operation.

  60. Jay Says:

    How do you import a tab-delimited file from the user interface? I’ve also had no luck trying to hack your code to stuff a tab as the delimiter. The default for LOAD DATA INFILE is a tab-delimited file, so this should be easy.

    As previously requested, automatic table creation by column names would be great!

  61. Joe G. Says:

    Hi Skakunov,
    This is a great tool for my workbelt, but I have a question, when i upload the same file to the same database table, it duplicates the entries in the csv file. Is there a way to perform “If ‘primarykey’ exists, update, else insert” instead of always inserting a new row? What I am looking to do is update inventory, so since the information is already there, I need only change the quantity, but it needs the item sku to reference obviously. Thanks in advance if you can answer this and thanks again for a great program!
    -Joe

  62. Joe G. Says:

    I was able to do what i was looking for above by replacing IGNORE INTO TABLE on line 101 in file /classes/Quick_CSV_import.class.php with REPLACE INTO TABLE

    This worked for me as long as there is a primary key where I needed it…not sure if this is safe for general use, but it was for me.
    -Joe

  63. Andy Says:

    Thank you. This is a great tool.

    My only problem is I can’t figure out how to get the csv loaded into my current table. I’m not sure where to add the table name.

  64. Skakunov Alexander Says:

    There is an example.
    You need to set the class field.

  65. Andy Says:

    Thanks Skakunov, I was able to set the class field. The last thing I’m not able to work out is how to set the when the line/row terminates. One of my columns is a text and often has commas that show up so the import is cutting of the text field.

    Is there a way with this script to ignore these commas? With php myadmin I’m able to do this by setting “Lines terminated: Auto”

    Thanks.

  66. Phil Says:

    Hi, I’m trying to download the visual mapping but get a PAGE ERROR when trying to view http://code.assembla.com/quick_csv_mapping_import

    Can you please email me the code?

    Thanks so much, Quick CSV Import rocks!

  67. Phil Read Says:

    The link is NOT working ;)

    http://code.assembla.com/quick_csv_mapping_import goes to a 404 page, please help!

    Thanks.

  68. Skakunov Alexander Says:

    I think you just need to register at Assembla.

  69. charlie Says:

    Hey, thank you for the great script. I have a following scenario and i want to use your script to import data located in “csv file” on my local drive in to an “existing” table (i set the var $table_name=community_fields_values) in my database. This is the sql query i want to run. can you please help me where i should i put this query in your script.

    ******************

    $sql = “insert into jos_community_fields_values set
    userid=$record->userid , field_id = 9, value=$record->Address”;

    $db->query($sql);

    $sql = “insert into jos_community_fields_values set
    userid=$record->userid , field_id = 11, value=$record->City”;

    $db->query($sql);

    $sql = “insert into jos_community_fields_values set
    userid=$record->userid , field_id = 19, value=$record->Zip / Postal Code”;

    $db->query($sql);

    $sql = “insert into jos_community_fields_values set
    userid=$record->userid , field_id = 10, value=$record->State”;

    $db->query($sql);

    **********************

    thanks,
    Charlie

  70. Michael G Says:

    Hi Skakunov,
    Is there a direct link to download the application? The URL provided above is an incomplete URL:
    http://code.assembla.com/quick_csv_mapping_import

    Thanks,

    – michael

  71. wadii Says:

    Cannot retrieve fields of the selected table
    this error shows up when i execute this code.
    please tell me why ,it is very important

  72. MySQL Database optimisation | Sven Welzel - blog.sven.co.za - www.svenwelzel.com Says:

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

  73. paulr Says:

    Nice tool, but I have two problems.

    1) Can you please provide a tarball/zip or some other package of the latest version? I can’t install an svn client as I don’t have admin rights on my machine.

    2) I’m getting the same problem as Richard in 33. i.e. importing dates in 31/12/2009 format sets the db field to 00/00/0000. Is there a fix for this?

    Thanks

  74. V.J. Winters Says:

    Hi there; great script.

    I am having difficulty with STEP 3 of the import script. It seems as though the mapping functionality is not working properly and as a result the CSV data is imported into the database in the original column order.

    The program also throws a warning:
    Warning: Invalid argument supplied for foreach() in classes/Quick_CSV_import.class.php on line 82

    Perhaps because there are more CSV columns than columns in the MySQL table?

  75. 32 Tips To Speed Up Your MySQL Queries « adehandis.co.cc Says:

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

  76. Chand Says:

    Here is the following warnings that I got
    Warning: trim() expects parameter 1 to be string, resource given in D:\xampp\htdocs\sample\quick_csv_mapping_import\classes\DB.class.php on line 290

    Warning: trim() expects parameter 1 to be string, resource given in D:\xampp\htdocs\sample\quick_csv_mapping_import\classes\DB.class.php on line 300

    After investigating I found this code which is looking okay.

    if (trim($this->fResult)!= “”)

    And also it is not listing tables from DB where we had to select encoding and table at first screen.

    Any help will be really appreciated thanks for your time and hard work.

    Best Regards
    Chand

  77. Cristian Says:

    Hi Guys,

    I have a quick question, regarding this script. I keep getting an error after trying to uopload the file. THe message that I am getting is “Malformed packet”. Any ideas.

    Thanks,
    C.

  78. sam Says:

    Hope this may help some of you.

    I’m also getting the trim() expects parameter 1 to be string error which started after installing wamp. Googling suggests is due to php versions 5.3. Maybe related info here : http://php.net/manual/en/function.strlen.php

    Anyway, I turned off lines 290 and 300 in DBclass.php by commenting them
    //if (trim($this->fResult)!=”")
    …………..
    //if (trim($this->fResult)!=”")

    That seems to make it work, but not sure if any errors will result. Anybody who is better at PHP troubleshooting, please help out to fix this the proper way.

  79. Jonathan Says:

    Thank you so much for this app, it works wonderfully! I’m trying to set it up so that it will automatically insert a username (that i have stored in a cookie) with every uploaded file. I want a record of who uploaded what to be added to the database so i can control editing and removing functions from my table. any advice?

  80. Jonathan Says:

    err rereading my post i realize i was a bit unclear. I don’t want to insert the username with each file, but with each row in that file, so if the csv had the headers – name, age, email – the database would recieve – name, age, email, username – for each row.

    thanks!

  81. Jonathan Says:

    figured out the above problem, I just wrote a script that edited the csv to include the username fields before uploading to the server, now i’m working on checking the database for repeat entries before committing the INSERT. Any advice?

  82. Jordan Says:

    Hey Skakunov! First off, I have to say, great program! I haven’t actually got it working 100% yet though. I am getting an error at the bottom of the screen before I go to step 3 that says:

    Warning: Unknown: failed to open stream: No such file or directory in Unknown on line 0

    Fatal error: Unknown: Failed opening required ‘common_footer.inc’ (include_path=’/csv_uploader/classes/’) in Unknown on line 0

    Then after I click submit to go to step 3, I get the following error (although it actually puts the data into the database):

    Warning: Invalid argument supplied for foreach() in /csv_uploader/classes/Quick_CSV_import.class.php on line 79

    Any ideas?

    Once I get it working I plan on modifying the script so that it will check the data in the .CSV file against the data that is already in that database table. Then all duplicate entries will be ignored so that there are no duplicate entries added. Then it would also add the unique entries to a different database table.

    I really appreciate any help or advice that you can give.

    Thanks!

    Jordan

  83. Johan Says:

    I have the same problem as sam 76 and chad 75, and no one answered these questions, harassment have no solution? Any would be very well recived allude, of course thank you very much ….
    I am from Colombia, excuse my bad English ….

  84. Skakunov Alexander Says:

    Johan, well, Sam (#76) found a solution which is good enough. The DB library used by the class is quite old, so just please make the suggested fix.

  85. Johan Says:

    thanks,then I just leave those lines commented out and ready …?
    good, simple but effective …

    Thank you very much again …

    Salu2 desde Colombia…

  86. Johan Says:

    Hello …
    Me again, now the problem is that when I’m in step two, shows me a message like this:

    Array
    (
    [file_name] => C: \ wamp \ www \ quick_csv_mapping_import (Not it works) \ temp \ csv \ php60CF.tmp
    [use_csv_header] => 1
    [field_separate_char] =>
    [field_enclose_char] =>
    [field_escape_char] => \
    [encoding] => utf8selected = “selected”
    [table] => voloracion_riesgos
    )

    Another thing, I tried the application of the other guy with the correction to the hiso, and runs fine but when it encounters the stresses on the csv, I go up only fragments of data, I mean only what has been before the accents … This is a solution … ?

    Pt: I put in comment lines 290 and 300 like you told me.

    From already thank you very much …

  87. Quickly importing data from CSV file into PHP | codeleaks.anonkuncoro.com Says:

    [...] You may learn more about this application in the following page: http://i1t2b3.com/2009/01/14/quick-csv-import-with-mapping/ [...]

  88. Sof Says:

    Hi and thx for this job !
    However i have errors i don’t understand

    First I had to comment the line 290 in DBclass.php too.

    But the most important I can’t connect to database :

    Database user dbusername must have SELECT permission

    I have this error even with root user. Sessions and mysql support are activated.

    Thx for help.

  89. Carl Lane Says:

    Skakunov,

    I am having difficulty with STEP 3 of the import script. It seems as though the mapping functionality is not working properly and as a result the CSV data is imported into the database in the original column order.

    The program also throws a warning:
    Warning: Invalid argument supplied for foreach() in classes/Quick_CSV_import.class.php on line 79

    Do you know a fix to the error ??

  90. Mario Romero Says:

    Hey! Great script, thank you!!
    To (73) V.J. Winters…

    I have the same problem as yours. It solved changing this line in actions/actStep2.php:

    “$_SESSION['data']['mapping'] = $attributes['mapping'];”

    for this one:

    “$_SESSION['data']['mapping'] = $_REQUEST['mapping'];”

  91. Victor Says:

    I’m having problem with setting up. I can see all the checkboxes and fields but on the top of the page there are warning messages like these. And the drop down list is not populated as well.

    Warning: trim() expects parameter 1 to be string, resource given in C:\wamp\www\CSV\classes\DB.class.php on line 290

    Warning: trim() expects parameter 1 to be string, resource given in C:\wamp\www\CSV\classes\DB.class.php on line 300

    Please Help

  92. MOD Says:

    Hi,
    thanks for your script that’s what i needed, but it doesn’t work for me, first a had the trim error, and beneath it i had a user root can’t get SELECT access something like that …so i commented the trim lines…
    Now in the form , my values are : “<?= " it evens shows in html code we can see the php in the values …
    So i changed <? to <?php but still the same ? would appreciate a little help :)

  93. MOD Says:

    Re,

    So i don’t have the <? problem i replace all of it correctly using if instead of ternairs condition ..

    But at the bottom of the page on step 1 and 2 i got and Error in bold :s don't know where it comes from !

  94. Freddy8 Says:

    Hi,

    Im really lost. I’ve tried all reccomended fixes, but cant get this working. I get the same error message that the others were getting.

    [10-Jun-2011 03:51:56] LOAD DATA LOCAL INFILE ‘/home/ibuyands/public_html/fatkid.com.au/temp/csv/csvKnGT6n’ REPLACE INTO TABLE `temp_10_06_2011_03_51_56_74` FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\\\”‘ ESCAPED BY ‘\\\\’ (`column1`,`column2`,`column3`,`column4`)
    [10-Jun-2011 03:51:56] Field separator argument is not what is expected; check the manual

  95. zorbeck Says:

    “The used command is not allowed with this MySQL version”

    This is the error message I obtained during step1…

    FYI, the system I am using as follows :
    Linux DiskStation 2.4.22-uc0 #1613 Fri Apr 8 10:17:16 CST 2011 ppc
    Apache/2.2.16 (Unix) PHP/5.3.3
    mysql API version: 5.1.49
    mysql version : Server version: 5.1.49
    phpMyAdmin: Version information: 3.2.5

    Any idea on how to solve that ?

  96. Daniel Says:

    Hello,

    Step 2 is not working. I selected a table in step 1. I made a table test with id(INT) and name(VARCHAR).There is no explanation what should be in the table names.
    in step 2 when used the example files, i dont get to see anything.

  97. Daniel Says:

    forgot to mention, i see the the temp/csv map names like: phpKrmPAj. Can i just use the normal csv filesnames there??

  98. Sharing and Learning » 32 Tips To Speed Up Your MySQL Queries Says:

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

  99. Kalicici Says:

    Alexander, You have here a very nice piece of code! I have implemented in on my localhost/laptop (wamp), and it was workiing fine. But since i put it on line for more testing purpose into my project, i am not able to get to step 2 for the field mapping.Once i am on Step page, no field is being displayed. a gave a result within which i saw “[csv_headers] => Array ( )”, which for me means may be $arr_headers is containing a null array. Kindly advise and help please. How to correct?

  100. 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 [...]

  101. 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 [...]

  102. 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 :)

  103. 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)

  104. 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

  105. Hakan Says:

    Great class. Thank you.

  106. 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

  107. 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.

  108. 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

  109. 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

  110. Andreas Says:

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

    Any idea???

  111. 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)

  112. 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.

  113. 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

  114. 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 [...]

  115. 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.

  116. 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

  117. 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 ….

  118. 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

  119. 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.

  120. 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

  121. LuckyCoder8 Says:

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

  122. Hardik Says:

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

  123. 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????

  124. 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

  125. 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?

  126. 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.

  127. 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.

  128. Pewe Says:

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

  129. Skakunov Alexander Says:

    Shoot.

  130. 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.

  131. 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.

  132. 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.

  133. Tushar Says:

    hi..

    i got this error.

    Database user root must have SELECT permission..

    Please help.

  134. 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”

  135. jaydip Says:

    Disallowed Key Characters. error on second step

  136. 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

  137. 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

  138. moorthi Says:

    how to remove duplicate values?

  139. moorthi Says:

    how to echo insert data to table query?

  140. 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!

  141. jfd Says:

    actually, sorry i worked it out! never mind!

  142. 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

  143. joseph Says:

    any luck with the Trim errors?

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

    for step 1.

  144. 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

  145. 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!!!???

  146. 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?

  147. Jsteban Says:

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

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

  148. 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

  149. 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)!="")

  150. 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

  151. james Says:

    Good day

    i have this error when i get to step2. Need your help

    Warning: fopen(C:\xampp\htdocs\quickimport\trunk\temp\csv\csv730.tmp): failed to open stream: Permission denied in C:\xampp\htdocs\quickimport\trunk\classes\functions.php

    CSV::convert_line returned 0 rows which is not good

Leave a Reply


8 × 1 =

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