Last updated 11 November 2008. Created on 22 March 2008.
Edited by rfay. 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.

Looking for support? Visit the forums, or join #drupal-support in IRC.


zydoon’s picture

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;
cutecyborg’s picture

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