WP CSV to Database Plugin – Import Excel file content into WordPress database (MySQL)
Categories: Wordpress Plugin
WP CSV to Database plugin allows you to insert CSV file content into WordPress database. Useful when you want to bulk upload data from your CSV file (e.g. product details) to the WordPress database (MySQL) in one go.
If you didn’t know what a CSV file is then you can learn more from Wikipedia. You can use Microsoft Excel to enter all your data and then save the file as a CSV file.
Many of my WordPress Shopping Cart users have requested me to add an option so that they can bulk upload their product details into the database from a CSV file (really useful when you have hundreds of products). Hopefully this plugin will satisfy that need and in addition you will be able to use it to bulk upload any data into your WordPress database to save you time and effort.
Installation of the Plugin
- Unzip and Upload the folder ‘wp-csv-to-db’ to the ‘/wp-content/plugins/’ directory
- Activate the plugin through the ‘Plugins’ menu in WordPress
Usage of the Plugin
Access the plugin’s User Interface in the WordPress dashboard under the “Settings” menu after installation and do the following:
- Specify the general options.
- Specify the input file (Upload the CSV file or specify the location of a pre-uploaded CSV file)
- Hit the “Import to DB” button to import the CSV file content into the database table.
Download Latest Version (v1.8)
Download the CSV to DB plugin.
How to Use This Plugin to Bulk Upload WP eStore Products
How to Use This Plugin to Bulk Upload WP eMember members
Special Note
If the cell values in the CSV file have special characters like apostrophe (‘) or quotes (“) then don’t forget to use a backslash before them (e.g Karen\’s book). You shouldn’t need to do this anymore if you are using v1.5 or above.
Video Tutorial
The video tutorial on the following page might also be a good resource for importing data into your WordPress database table:
If you have any issues with the plugin please leave a comment below.
#1 by Rics on August 5, 2012 - 10:18 am
@Ivy
You’re welcome!
It was my duty to explain my experience, especially against Ruhul that has provided us this useful plugin.
I’ve re-read my comment and i’m sorry for a couple of grammatical errors, i did a long comment and i’m not an experienced english speaker/writer!
#2 by Ivy on August 2, 2012 - 9:23 pm
Thank you for explaining the issue you where having in detail and how you fixed. =)
#3 by Riccardo Rics on August 2, 2012 - 5:33 am
Really useful plugin!
Anyway i had to fight a bit to have it working, but mainly for my low knowledge and personal features required.
I write this comment also to avoid people marking the plugin as “not working” or giving a low rate, that’s a top-notch plugin for who, like me, create themes itself and customize wordpress in deep.
Here it is my experience:
I exported a CSV from Microsofts Excel, comma value, but as i don’t know so much excel, i simply exported it without checking all the options.
So i had in output a CSV file with field delimitations defined by semicolons “;”, with windows line endings, and western windows text encoding.
(Discovered then that’s a totally wrong CSV file format for the “CSV to Database” plugin)
After some times that the plugin said “Import succesfull” but in phpMyAdmin i didn’t saw any imported data, i tought about changing the Text Encoding of the CSV to “UTF-8″ and the line endings to “UNIX Line Endings (LF)” as in the past i had this kind of problems with CSV files.
I re-tried the import, message was alway “Import successfull”, but, as before, in phpMyAdmin i didn’t saw any imported data.
So i did some webseraches and i found that the CSV file for the “CSV to Database” plugin must have the fields delimited by commas “,”.
Changed all the delimitations from semicolons “;” to commas “,”:
Bingo!
Now the plugin import successfully imported the data and i verified also in phpMyAdmin that the data was really imported to the database!!
Now the second problem:
Ok, i’ve the CSV, it’s uploaded and that’s fine. But i will sometimes update that Table with a new CSV file, maybe a CSV that have the same rows but with some fields modified, or with some rows added having the other rows unmodified.
What happen if i upload a new CSV?
It will be add the new data in the Table either if there are duplicate fields!
My “poor” solution came immediately in my mind: TRUNCATE and then INSERT!
So i’ve added these following code lines after line 36 of the plugin:
// ADDED TO CLEAN TABLE
$trunk = “TRUNCATE TABLE $table_name”;
$trunked = $wpdb->query($trunk);
// ADDED TO CLEAN TABLE
It’s important to place them before the while cycle:
“while (!feof($file_handle) )”
Otherwise you will have a loop of TRUNCATE and then INSERT resulting in an import of the last row only from the CSV file!
So be careful!
Probably my solution isn’t the best one, but if you need to clear everytime your table and then insert new data from a CSV file…well, that modify could be useful!
Cheers,
Rics
#4 by Mihcael on October 25, 2011 - 7:43 am
For a generic, free tool that allows you to load CSV files into any database, check out CSVexpress.
#5 by admin on September 30, 2011 - 10:02 pm
You gotta format the date correctly. MySQL date values are formatted like the following:
yyyy-mm-dd
The following video tutorial should help:
http://www.tipsandtricks-hq.com/how-to-easily-import-or-export-any-of-your-wordpress-database-content-using-phpmyadmin-3415
#6 by Frank on September 30, 2011 - 2:06 pm
This is an awesome plugin, i just have one question for you. I am using it to import posts to the wp_posts table from a csv file. Everything imports correctly except the date.
In the CSV file, the date is formatted like this:
9/30/2011 10:15
upon import, the DB shows this:
00/00/0000 00:00
any ideas?
#7 by Mike on August 21, 2011 - 11:55 am
Ok,, thank you for the assistance!
#8 by admin on August 21, 2011 - 1:55 am
All comments should go to the “wp_comments” table (its one of the core tables of WordPress).
#9 by Mike on August 20, 2011 - 9:25 pm
@admin, thanks for your reply. It shouldn’t be hard to import the comments into SQL, I have experience doing it. Do all comments plugins for wordpress make use of the same wp_comments table?
#10 by admin on August 20, 2011 - 7:29 pm
@Mike, I don’t know of any comments plugin that lets you import comments but I think all you really need is to insert the comments into the “wp_comments” table in WordPress. This article might help you to where you can do it yourself:
http://www.tipsandtricks-hq.com/how-to-easily-import-or-export-any-of-your-wordpress-database-content-using-phpmyadmin-3415
#11 by Mike C on August 20, 2011 - 2:03 pm
Hi, do you have any suggestions as to a good comments plugin for WordPress that isn’t too terribly difficult to import comments into? I have a few posts from my old Joomla site that have close to 1200 comments and they serve well for credibility purposes. I recently imported 530 reviews from CSV into the SQL table for WP Customer Reviews which was great! I just need to do the same thing for my comments. But just curious if you had any recommendation for a good comments plugin?
#12 by Brent Williamz on August 12, 2011 - 3:19 am
Ahhh…that will help enormously.
I’ll watch that video tutorial now, then delve into mycPanel MyPHPAdmin to create a table. I did manage to find some reference to the description field like you’re saying, & it said make the description field type “longtext”.
Many thanks for all that & I’ll be back…to let you know the outcome. Cheers & avagoodweekend!
#13 by admin on August 11, 2011 - 11:44 pm
Just wanted to add… this tutorial might come in handy for you:
http://www.tipsandtricks-hq.com/how-to-easily-import-or-export-any-of-your-wordpress-database-content-using-phpmyadmin-3415
#14 by admin on August 11, 2011 - 11:43 pm
Okay… when you are importing content into database you need to tell it where to go to (basically, it needs a table that can store that content). So if you are trying to import that data into your database you need to create a table first that can hold that data and has the column structure to do so. For the description field you can use a “text” type which will allow you to put long descriptions without limiting you.
#15 by Brent Williamz on August 11, 2011 - 8:23 am
The merchant datafeed I want to import uses the following standard Shareasale column format >>
ProductID Name MerchantID Merchant Link Thumbnail BigImage Price RetailPrice Category SubCategory Description Custom1-OnSaleDate Custom2-Genre Custom3-People Custom4-Theme Custom5-Popularity LastUpdated status manufacturer partnumber merchantCategory merchantSubcategory shortDescription ISBN UPC
where the Description field per product per row has at least a paragraph of text. The error messages seem to be associated with the amount of text (lots) in this field entry.
As well, I’m uncertain what to put in the Database Table Name in Step 1 Specify General Options when using your plugin, as I’m not importing into your eStore. Do I have to create a table within the database? Although I’ve been heavily into PC technology for decades, database stuff is relatively new to me
I’d attach some screenshots to show you more clearly but there is nowhere to attach files in this comments field.
#16 by admin on August 10, 2011 - 11:15 pm
@Brent, I am not sure what you mean by the description field. The description field of which table? Which table are you trying to import this to? I can’t tell you if there is a limit if I don’t even know which database table you are referring to.
#17 by Brent Williamz on August 10, 2011 - 4:40 am
I’m trying to use this plugin using a standard csv format datafile from a merchant. To trial the import process, I created a new site http://ScienceFictionFantasy.info with a fresh install of WordPress (latest 3.2.1), installed your Importer plugin then took the merchant’s datafeed into Excel & deleted all entries except 5 so I could use a small test file. After saving it as a csv again, I then went through your 3-step process & got error messages that indicated a number of problems partway through the Description field of each Data entry.
Is there a limit to the characters it can handle in each Description field entry? I can email any further detail as required if you tell me what else you require to solve the problem, eg. the datafeed test sample (tiny) or complete file (huge, not tested yet), the error message as a screenshot jpg to look at, my WP Admin login info, what else?
BTW, I couldn’t post this on the forum as you prefer, because we haven’t bought anything yet (after we get this sorted, we’ve got a client that wants to add digital products to their WP website very soon so the eStore plugin looks imminently suitable.
Thanks in advance.
#18 by admin on May 25, 2011 - 9:05 pm
@Ed, The path to file doesn’t seem complete:
/wp-content/plugins/wp-csv-to-db/uploads/LwithTLI04.csv
Did you upload the file using this plugin or did you upload it using the WordPress medial uploader then just used the URL? Also, are you using the version downloaded from this page or an older version of CSV to DB plugin?
#19 by Ed on May 25, 2011 - 5:03 pm
Hi I’m having the same trouble as jay above – same error message:
Error occured while trying to import!
Source file could not be opened!
Error on fopen(‘/wp-content/plugins/wp-csv-to-db/uploads/LwithTLI04.csv’)
No spaces in my filename. Are there any other known issues that might cause this?
#20 by admin on May 3, 2011 - 12:41 am
There seems to be a space in your csv file name “products (2).csv”. PHP can’t open files with space. Rename the file to something like “products-2.csv” then upload and use it.
#21 by Jay on May 2, 2011 - 12:59 pm
Hi
I have tried everything to upload my CSV file but I keep getting the upload error :
Error occured while trying to import!
Source file could not be opened!
Error on fopen(‘/var/chroot/home/content/76/7714576/html/wp-content/plugins/wp-csv-to-db/uploads/products (2).csv’)
I used Excell, Open Office, Works but nothing seems to go in the right direction, what seems to be the problem.
#22 by admin on February 7, 2011 - 12:28 am
@Kay, thank you for sharing this. I have added this to the plugin and updated it.
#23 by Kay on February 6, 2011 - 6:41 am
First off, thanks so much for this plugin. I was dreading how to come up with a way to upload a csv into my database. At first i thought this wasn’t going to work for me…took some tweaking and i got it to work.
So I thought I’d share this addition for anyone that may come across the same problem I did.
Here are two lines of code to add BEFORE LINE 47 ($query_vals .= “,’”.$line_of_text[$c].”‘”;) :
$line_of_text[$c] = utf8_encode($line_of_text[$c]);
$line_of_text[$c] = addslashes($line_of_text[$c]);
The first line will change your special characters like accents. Otherwise i found the word would just end (ie. Bérubé would be entered as B) This fixes so that Bérubé can be entered exactly
The second line will fix the problem of apostrophe (‘) by adding the backslash before them.(e.g Karen’s book is entered as Karen\’s book). You will get an error if you do not have the backslash and try to leave it as Karen’s book.
I had a lot of data so going through to add the backslash was not on option. As well someone else would be doing the uploading and they are not a programmer and would not know to add the slashes and would therefore not be able to upload the file to the database.
Hope this can help someone.
#24 by Cindi Gay on November 24, 2010 - 12:33 am
I work on my website for just a few months each year. I struggled with this last year, and here it is all wrapped up and ready to go. Thanks for the note at the bottom of the edit Product page. This plugin has been some of the best money I have ever spent. You’ve been a step ahead of me all the way.
Thank you for the hard work. Love that I can get an update automatically.
.-= Cindi Gay´s last blog ..Black Friday Wool Sale =-.
#25 by admin on November 23, 2010 - 11:26 pm
Yeah you can use it for any table in WordPress as long as you know the database table structure for the table in question. If you look at the affiliate’s table columns then specify the corresponding data for each column in the CSV file then that’s all you need to do. You will still need to manually create the CSV file which might be a bit of a pain. In situations like this you can just send a mass email to your affiliates and tell them that they will need to create an account from here (specify the signup page’s URL). Creating an affiliate account is pretty easy and takes only a few seconds so people shouldn’t really have issues with that.
#26 by Rebekah on November 23, 2010 - 12:29 pm
Is it possible to use this with the Affiliate Platform plugin? We’re converting a client from another shopping cart system and would love to be able to import her ~200 affiliates. Is it fairly simple? or is AP setup differently in regards to tables and the like? Thanks! ~R
#27 by Adam on August 15, 2010 - 1:51 pm
I had been struggling with uploading even after fixing the infamous fopen conflict. After hours of struggling and trying over and over, I noticed that the file I was trying to upload had been saved as .xls, not .csv. Just in case anyone else has this happen, make certain Excel saves as csv, not xls.
.-= Adam´s last undefined ..If you register your site for free at =-.
#28 by admin on July 5, 2010 - 9:47 pm
@Michael, Looks like you are using it for eStore plugin. Can you please follow the instruction on this page?
http://www.tipsandtricks-hq.com/ecommerce/how-to-bulk-upload-estore-products-from-a-csv-file-775
Don’t even change the product names in the template csv file. Just follow the process step by step and let me know if you get the two test products specified in the template csv file imported into your database (I just checked and it works fine for me).
#29 by admin on July 4, 2010 - 4:07 am
You need to specify the column names in the “Column Names” field of the plugin. In the CSV file you will need to place your data accordingly so it aligns with the column names.
How do you find the column names of your database table?
One way is to log into your PHPMyAdmin from cPanel and browse to the database table that you are after. If you hit the “Browse” or “Structure” you will see it lists all the column names of the table that you copy and paste.
#30 by Cemal on July 3, 2010 - 3:11 pm
How does the plugin know to map the CSV fields to DB fields? Should the CSV be structured in a particular way? If so, in what sequence so that all the data will go to the correct field of the eMember DB.
.-= Cemal´s last blog ..Protected- Member List =-.
#31 by Michael DeLuca on July 2, 2010 - 2:27 pm
It doesn’t seem to be working for me.
I’m using plugin version 1.1 on wordpress 2.8.6 with Safari 5. I’m using a file with only one line, as recommended above for testing. And I’m uploading the file using FTP and specifying the url rather than uploading through the plugin. When I click “Import to DB”, I get a response saying “File content has been successfully imported into the database!” But the uploaded product does not appear in the eStore product manager. Nor does the data show up in the database when I check the table with phpMyAdmin. So it doesn’t look like it actually did anything.
#32 by click radius on June 13, 2010 - 12:44 am
This is lovely! But it may need some work for WP 3. My site is having trouble since installing it.
#33 by Claire on June 11, 2010 - 9:56 pm
Oh dear! Well a good understanding is something I don’t have
If anyone else can help me, I’m all ears! Or maybe we could have a button added to the plugin that says “overwrite” table or “ignore duplicates” like there is in myPHPadmin? A suggestion for future releases perhaps?!
#34 by admin on June 11, 2010 - 9:02 pm
Unfortunately there is no one line tweak that I can give you for this one. It will require a good understand of PHP and MySQL to tweak this.
I can only give you the explanation. when you read the CSV file line by line you will need to make a database query with the key to see if an entry already exists for that key. If it does then you need to delete that and then continue with the insertion.
#35 by Claire on June 11, 2010 - 3:09 pm
I am so happy to have found this plugin. I need it to avoid duplicates though (or actually just deleting the table and replacing with the one I upload would be perfect). How would I tweak the code to make that happen?? I am not a coder so I’d need very explicit directions!!
THANKS!!
#36 by admin on June 2, 2010 - 10:06 am
The plugins uses PHP’s CSV file reading function to read the CSV file so it will have to be a CSV file.
Just to give you an idea… if you can code then anything is possible. You write your own code which reads a plain text file that delimited by “tab” then when you read it you can split it using the “explode” function where it finds the “tab” character. Then you just prepare a statement with the data and insert it into the database.
This plugin do not let you delete records it only adds to the database. Again adding an option like this is not hard either.
#37 by Nicolas on June 2, 2010 - 12:34 am
Dear Tipsandtricks
Thanks for this GREAT idea. I was wondering if it was possible to “erase” a database before importing.
For example, if I have to update prices, I should first erase all the records and then import the updated prices database.
Thanks
.-= Nicolas´s last blog ..Novedades y ideas =-.
#38 by Nicolas on June 2, 2010 - 12:30 am
Plugin seems VERY interesting.
I was wondering if it was possible to import a “tab” file rather than “CSV” file. I have a lot of “commas” to import from my datas (prices, products descriptions,…) so it seems dificult to use CSV type file.
Any idea ?
Thanks
.-= Nicolas´s last blog ..Novedades y ideas =-.
#39 by admin on May 23, 2010 - 12:31 am
Hi Marcus, You can find all these info from your PHPMyAdmin. So if you log into your cPanel and browse to the PHPMyAdmin you will be able to select your WordPress database. There you can sell all the tables you have and the field names and types of each tables.
#40 by Marcus on May 22, 2010 - 9:56 am
Well, I found myself part of the answer. I used the excellent plugin WP-DBManage available at http://lesterchan.net/portfolio/programming/php/ to get the table names. And I’m still researching about getting field names and field types. I’m trying to avoid having to write a page where I’d code a query to the database. Looking for something already done and reliable.
Thanks.
#41 by Marcus on May 22, 2010 - 9:12 am
just correcting the last phrase:
Could you please tell me how and where I can get those details about wp-ecommerce tables? Thanks!
#42 by Marcus on May 22, 2010 - 9:11 am
I find this very useful but I don’t know where I can get he details (field names, field types and table names) of the wp-ecommerce plugin. I want to import products to the database and also the location of the product images. Could you please how I can get those details about wp-ecommerce tables? Thanks!
#43 by admin on May 18, 2010 - 6:03 am
As long as you are uploading into a table in WordPress you can use this plugin. I am pretty sure BuddyPress uses a table inside WordPress database so you should be fine.
#44 by Carol on May 17, 2010 - 10:39 am
Is it possible to use this plug-in to bulk-add users to a BuddyPress site? I have more than 2K users to import, and haven’t found a plug-in that will do this.
.-= Carol´s last blog ..Welcome to The Digital Artist =-.
#45 by admin on April 24, 2010 - 9:36 pm
“I tried FTPing the same file, and I get a long list of errors” – If you have a lot of entries in your CSV file then there is an error in the row then the same error is going to appear again and again (making it a long list). The best way to handle this is to use CSV file with 1 entry and try to import it (this way you will be able to see what the error is correctly). Once you fix the errors and can import one row fine then use that same format to populate the rest of the entries in the CSV file. Does it make sense?
#46 by skaki on April 24, 2010 - 2:02 pm
I keep getting this error when I am trying to upload the .csv file. I tried FTPing the same file, and I get a long list of errors.
“There was an error uploading the file, please try again!”
no details around what the error could potentially be.
what should I do?
.-= skaki´s last blog ..At&t Cell Phone Deals – Motorola, Samsung, Sprint & Nokia =-.
#47 by admin on April 20, 2010 - 4:06 am
It can be easily tweaked to do that but the first concern is security. You should never have an open upload option to your server because the next morning you will wake up and find out that you have lots of junk from all the bots uploading crap. So the question is do you plan to add the upload option on an open page or post?
#48 by George on April 19, 2010 - 2:20 pm
Great plug-in, works great! I was wondering if it could be changed to work on the user side? For Example, rather than having the options, upload form, and import button on the admin side, have it on a page where users can access it and use the features themselves? I’m sure I could figure it out if it is indeed possible, just don’t want to waste time so i thought I would ask if it is possible. Thanks!