Jan 14
Several years ago I created PHP class to import CSV files to a database table very quickly ( 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.
“” is a PHP example application that imports CSV file to a database table with visual mapping of CSV columns to table columns.
Of course, you can get a copy of the application source – just go to the .
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:
- Open Quick_CSV_import.class and find “
LOAD DATA INFILE“ - change it to “
LOAD DATA LOCAL INFILE“
It’s connected with permissions at your Linux server. Thanks to for the solution.
January 15th, 2009 at 00:07
Hi,
I am getting the following error:
‘Field separator argument is not what is expected; check the manual’
Is there a manual?
January 15th, 2009 at 00:14
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.
January 20th, 2009 at 23:30
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
February 26th, 2009 at 16:10
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)
February 27th, 2009 at 19:40
how many data register could import your aplication? Do you think it can handle about 1 million or more?
February 27th, 2009 at 19:42
I think it can.
One day I imported 50 Mb data file in 0.16 seconds.
February 27th, 2009 at 20:07
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?
February 27th, 2009 at 20:14
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?
March 2nd, 2009 at 18:09
[...] 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/ [...]
March 5th, 2009 at 13:03
I tried downloading from the repository and running the code but I don’t get any field mapping in step 2.
March 5th, 2009 at 13:24
2 noel: OK, what’s happening? Any errors? BTW, check my comment above.
March 5th, 2009 at 17:11
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);
March 5th, 2009 at 17:25
2 noel: is you PHP in safe mode? Maybe that’s the root of problem?
March 5th, 2009 at 17:41
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”.
March 5th, 2009 at 17:43
I tried with both Safe_mode=off and on but still the same.
March 5th, 2009 at 17:46
That’s the problem. Try to find “LOAD DATA INFILE” statement and add LOCAL keyword like: “LOAD DATA LOCAL INFILE”
March 5th, 2009 at 17:54
It worked! Thank you very much.
I found that line in Quick_CSV_import.class under function import.
March 5th, 2009 at 18:10
My pleasure.
Added this solution to the post.
March 7th, 2009 at 06:49
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.
March 7th, 2009 at 06:52
Sorry I did not finish my question, What Happened? Why I don`t get to work correctly your application
March 9th, 2009 at 16:12
I don’t get to choose a database or table on step 1 there is no table or databases in the dropdown select box
March 9th, 2009 at 16:21
Dixie, send me a screenshot at i1t2b3(at)gmail.com
March 9th, 2009 at 19:14
Ok, Dixie, try to find and set to
ONthis setting in your php.ini and restart your web-server:short_open_tag = OnMarch 9th, 2009 at 20:17
Thats it thanks I can get to next page now. Is there a quick way to create the table columns for the csv file
March 9th, 2009 at 20:23
I’m not sure I follow, what do you mean?
March 9th, 2009 at 20:38
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
March 10th, 2009 at 07:04
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
March 10th, 2009 at 23:01
No, currently you must create a table in advance.
Actually, what you want can be done, but this application doesn’t allow this currently.
March 11th, 2009 at 17:24
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.
March 11th, 2009 at 18:09
I used main import class from my previous project, so it tryis to create an unique table name if it’s empty:
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:
3. Submit it here.
March 11th, 2009 at 18:20
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
)
March 11th, 2009 at 18:32
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
April 3rd, 2009 at 21:18
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
April 6th, 2009 at 03:57
I am also getting the same problem as Graham. Anything?
May 12th, 2009 at 05:16
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.
May 22nd, 2009 at 13:32
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
May 22nd, 2009 at 13:43
Sure. It seems you have PHP3. My advise is to upgrade it. If that’s not possible, remove
publicbefore functions names and replacepublicbyvarfor variables (in the beginning of the class).May 23rd, 2009 at 16:15
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
May 27th, 2009 at 17:54
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
May 27th, 2009 at 18:59
Read two comments above.