Last updated November 11, 2008. Created by rfay on March 22, 2008.
Log in to edit this page.
The parent page explains the details perfectly, but I found it cumbersome to create the SQL insert statements in my spreadsheet, so I decided to go with several "sheets" in my spreadsheet that I could export as text files and then import into the database.
- I started with the spreadsheet containing my data to be imported and added nid and vid columns at the front, and autonumbered them as in the above example.
- Then I added sheets for the four tables that I wanted to create data for (node, content_type_project, node_revisions, and node_comment_statistics)
- In each sheet I used formulas to create the data (not the SQL inserts) from the original first sheet.
- I exported each sheet as TXT. (I was using Google Spreadsheets, and it created a very nice tab-delimited text file. I believe that if you used Excel you would get the exact same result.) Using tab-delimited text files means no worries about quotes and commas, unless of course there are tabs somewhere in your data.
- I imported the text files. There are many ways to import a file of this type, but I did this using PHPmyadmin's import facility, just choosing "CSV" and changing the field delimiter to \t.
- Finally, don't forget to update the sequences table
My resulting XLS file is attached. You may be able to use it as a starting place.
| Attachment | Size |
|---|---|
| Demo Spreadsheet for import to drupal CCK nodes.xls | 176.5 KB |
Comments
Thank you
Hi and Thank you for this!
it works for me flawlessly on Drupal 6 and postgresql 8 using this command one time for every table that needs update, example for the node_comment_statistics table :
COPY node_comment_statistics from '/tmp/node_comments_statistics_table.csv' DELIMITER ',' ;And then, we need to update sequences like this:
ALTER SEQUENCE node_revisions_vid_seq RESTART xxx;ALTER SEQUENCE node_nid_seq RESTART xxx;
Importing Erp Stores
hi , i think you may have the answer to my problem. i have alist of stores i want to import into the erp store content type. but i don't want to mess with the database. how to i format my data in microsoft excel to match the data structure for that content type? thanks alot i'm expecting your reply