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 5th, 2009 at 06:31
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?
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
March 22nd, 2010 at 19:23
Cannot retrieve fields of the selected table
this error shows up when i execute this code.
please tell me why ,it is very important
March 23rd, 2010 at 09:43
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
April 1st, 2010 at 13:07
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
April 25th, 2010 at 03:55
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?
May 9th, 2010 at 02:08
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
June 7th, 2010 at 08:12
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
July 27th, 2010 at 19:12
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.
August 15th, 2010 at 18:23
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.
August 24th, 2010 at 01:34
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?
August 24th, 2010 at 17:44
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!
August 25th, 2010 at 22:53
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?
September 6th, 2010 at 05:55
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
November 25th, 2010 at 15:57
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 ….
November 28th, 2010 at 17:45
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.
November 29th, 2010 at 16:36
thanks,then I just leave those lines commented out and ready …?
good, simple but effective …
Thank you very much again …
Salu2 desde Colombia…
December 24th, 2010 at 17:46
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 …
December 27th, 2010 at 03:42
[...] You may learn more about this application in the following page: http://i1t2b3.com/2009/01/14/quick-csv-import-with-mapping/ [...]
February 15th, 2011 at 11:08
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.
March 12th, 2011 at 06:26
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 ??
March 17th, 2011 at 17:11
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'];”
April 8th, 2011 at 18:31
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
May 25th, 2011 at 17:59
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
May 26th, 2011 at 10:55
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 !
June 10th, 2011 at 12:28
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
August 21st, 2011 at 13:13
“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 ?
October 1st, 2011 at 07:15
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.
October 1st, 2011 at 07:16
forgot to mention, i see the the temp/csv map names like: phpKrmPAj. Can i just use the normal csv filesnames there??
October 10th, 2011 at 07:27
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
November 4th, 2011 at 10:47
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?
November 8th, 2011 at 21:12
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
November 12th, 2011 at 19:00
[...] loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times [...]
December 17th, 2011 at 00:19
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
December 17th, 2011 at 00:33
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)
December 22nd, 2011 at 15:17
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
January 10th, 2012 at 19:57
Great class. Thank you.
January 29th, 2012 at 14:57
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
January 29th, 2012 at 19:26
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.
February 7th, 2012 at 19:28
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
February 17th, 2012 at 13:44
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
March 27th, 2012 at 15:58
I get this error message: Database user root must have SELECT permission
Any idea???
March 31st, 2012 at 23:51
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)
May 7th, 2012 at 10:08
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.
May 18th, 2012 at 18:02
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
May 27th, 2012 at 07:41
[...] 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 [...]
July 24th, 2012 at 00:52
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.
July 26th, 2012 at 07:06
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
September 3rd, 2012 at 14:03
this is axtually works but i want sum addtions its do not rumove duplicate values so i want to remove duplicates from it ….
September 3rd, 2012 at 14:05
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
September 14th, 2012 at 12:40
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.
October 10th, 2012 at 10:57
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
October 24th, 2012 at 23:57
What file we got to download to use this seemsly good software !! thanks!
December 4th, 2012 at 14:05
I get the error “Cannot retrieve headers columns of the CSV file”
December 6th, 2012 at 15:28
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????
December 13th, 2012 at 21:17
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
December 14th, 2012 at 23:30
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?
April 3rd, 2013 at 16:02
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.
April 14th, 2013 at 02:16
Are any experienced developers still watching this article??
I have a query about importing date/timestamp and need some help.
April 14th, 2013 at 12:36
Shoot.
April 15th, 2013 at 04:46
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.