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.
July 5th, 2009 at 12:54
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
July 18th, 2009 at 03:33
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.
July 18th, 2009 at 12:53
Send me your file to i1t2b3(arobaz)gmail.com
July 18th, 2009 at 16:20
Hi Skakunov,
Which file would you like to have?
July 18th, 2009 at 22:08
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?
July 18th, 2009 at 22:40
I have one. Run this command:
to make your sessions folder writable.
July 19th, 2009 at 02:21
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.
July 22nd, 2009 at 02:14
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.
July 22nd, 2009 at 02:20
Francis, have downloaded a fresh version of the project? I have commited an update 2 days ago
August 17th, 2009 at 15:10
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.
September 4th, 2009 at 17:03
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
September 25th, 2009 at 15:38
I’m have the same problem as Francis (post 48).
Is there already a solution for this by anyone?
September 25th, 2009 at 17:12
Ok, guys, will check tonight.
September 28th, 2009 at 00:19
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).
October 9th, 2009 at 02:09
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!
October 10th, 2009 at 14:56
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.
October 10th, 2009 at 15:02
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.
October 10th, 2009 at 15:40
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.
October 20th, 2009 at 02:58
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!
January 1st, 2010 at 00:52
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
January 4th, 2010 at 23:54
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
February 10th, 2010 at 22:41
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.
February 10th, 2010 at 22:52
There is an example.
You need to set the class field.
February 14th, 2010 at 18:03
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.
February 21st, 2010 at 04:19
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!
February 21st, 2010 at 05:28
The link is NOT working
http://code.assembla.com/quick_csv_mapping_import goes to a 404 page, please help!
Thanks.
February 21st, 2010 at 21:02
I think you just need to register at Assembla.
February 25th, 2010 at 20:04
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
February 28th, 2010 at 19:07
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