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.
Installation of the Plugin
- Go to the Add New plugins screen in your WordPress admin area
- Click the upload tab
- Browse for the plugin file (wp-csv-to-db.zip)
- Click Install Now and then activate the plugin
Usage of the Plugin
Step 1 (Select Database Table):
- All WP database tables will be queried and listed in the dropdown box.
- Select the table name which will be used for the query.
- Once the table is selected; the “Table Preview” will display the structure of the table.
- By structure, this means all column names will be listed in the order they appear in the database.
- This can be used to match the .csv file prior to execution; and verify it contains the same structure of columns.
Step 2 (Select Input File):
- The option will be used to locate the file to be used for execution.
- A direct url to a .csv file may be entered into the text field.
- Alternatively, the “Upload” button may be used to initiate the WordPress uploader and manager.
- From here, the file can be uploaded from a computer or selected from the media library.
- The “Number of .csv file Columns” will populate when the Input File field contains a valid .csv file.
Step 3 (Select Starting Row):
- The .csv file will contain rows, which get converted to database table entries.
- This option will allow customization of the starting row of the .csv file to be used during the importation.
- Row 1 is always the top row of the .csv file.
- For example: If the .csv file contains column headers (column names), it would most likely be desirable to start with row 2 of the .csv file; preventing importation of the column names row.
Step 4 (Disable “auto_increment” Column):
- This option will only become available when a database table is selected which contains an auto-incremented column.
- If importing a file which already has an auto-incremented column… this setting most likely will not be needed.
Step 5 (Update Database Rows):
- By default, the plugin will add each .csv row as a new entry in the database.
- If the database uses a primary key (auto-increment) column, it will assign each new row with a new primary key value.
- This is typically how entries are added to a database.
- However; if a duplicate primary key is encountered, the import will stop at that exact point (and fail).
- If this option is checked, the import process will “update” this row rather than adding a new row.
Download Latest Version
Download the CSV to DB plugin.
How to Use This Plugin to Bulk Upload WP eStore Products
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.
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.
Check out our WordPress plugins page for more cool WordPress plugins.
Comments (76 responses)
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?
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.
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.
@Kay, thank you for sharing this. I have added this to the plugin and updated it.
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.
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.
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.
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
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.
@Michael, Looks like you are using it for eStore plugin. Can you please follow the instruction on this page?
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).
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.
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.
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.
This is lovely! But it may need some work for WP 3. My site is having trouble since installing it.
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?!
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.
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!!
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.
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.
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 ?
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.
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.
just correcting the last phrase:
Could you please tell me how and where I can get those details about wp-ecommerce tables? Thanks!
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!
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.
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.
“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?
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?
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?
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!
Basically some server’s block the fopen (file open) functionality for URLs. You can use the relative URL and it should work fine.
So upload the CSV file in the “uploads” directory of the plugin then use the following in the “Specify the file URL” field:
“products.csv” is the name of the CSV file in this example.
I tried to upload the CSV and got this code:
Warning: fopen(http://My Website.com/wp-content/plugins/wp-csv-to-db/uploads/AD products.csv) [function.fopen]: failed to open stream: HTTP request failed! HTTP/1.1 404 Not Found in /home/nmoller/public_html/wp-content/plugins/wp-csv-to-db/wp-csv-to-db.php on line 27
I tried to upload it a couple different ways and the same thing.
I read this suggestion but wasn’t sure what it meant:
Thanks for the help.
Okay that kind of indicates that the cells have unusual values or the database columns are not matching.
Can you share the value you are using in the “Column name” field and one row of the data from the CSV file?
Thanks for the reply, yeah I’ve tried it with just one entry into a table with only one column and I’m getting the same thing, the page loads until just after the end of the left hand menu then nothing else, so no footer either.
Maybe you have too many entries that’s throwing the error so it’s banking out.
Can you try with a few entries first to see if it goes in fine?
Thanks, I’ll give it a whirl. Currently I’m getting a problem importing to the database, no error code or anything, just a blank screen loads up. Any ideas what could be causing it to do this?
Nope, it won’t check for duplicates (you can tweak the code to add this checking).
I have a database of gigs listings that I want to be able to add to, but not duplicate. Will this plugin check for duplicate entries?
thank you. i used this plugin imported more than 16000 records into the DB
thnx admin for getting back to me.
I tried that and that’s why i got the fopen() error. I FTP’d the file.
So i guess my server is the cause of the above error 🙁
Hi Fiz, looks like your server does no allow PHP fopen functionality. You can upload the file to your server using a FTP program then use this plugin to import the content into the database. Just specify the URL of the file in the “Specify the file URL ” field after you upload it.
I tried uploading the csv file to my server and got these errors:
Warning: fopen() [function.fopen]: php_network_getaddresses: getaddrinfo failed: Name or service not known in /wp-content/plugins/wp-csv-to-database/wp-csv-to-db.php on line 27
Im tryin to upload the csv file and it’s coming up with
‘There was an error uploading the file, please try again!’
I’ve converted all ‘ and ” to \’ and \”
what else do i need to do?
@Ed, There seems to be bug in the code for when you don’t specify the “column names”. I have updated it… please download the latest version from this page and give it a go.
When I hit the button import to database I see the following error,
INSERT INTO wp_name_table () VALUES (‘Auto Reinigings Produckten zuur ;Velgen reiniger;801110;Velgen Reiniger 750ml ;? 4′,’60;50;0′,’8;0;0’)
@Paul, It only adds the data to the database so your existing data stays intact.
….how about an ‘undo’ option 🙂
Does this add to or overwrite existing items in the database?
(Having nightmare visions of someone wiping their database by testing a single entry, or ending up with double everything….)
Hey Darren, you always have to come up with good ideas, don’t you? 🙂
Bulk-export feature sounds very useful… I will put it on my to do list.
Wow, so simple and yet so useful! I bet people are going to be thinking of all sorts of uses for this plugin.
Do you have any plans to add bulk-export functionality? Once you have your products uploaded, you might want to edit descriptions etc. It’d be great to be able to bulk-export the data, update it, and then re-upload them.