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.

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

Leave a Reply

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