How to import csv data into wordpress database using phpmyadmin?

3 comments

1. I have done a large csv import using phpmyadmin

   1. Get the last post id from the (table-prefix)_post table and SET as @PostID.

      SET @PostID := (SELECT ID FROM `(table-prefix)_posts` ORDER BY ID DESC LIMIT 0, 1);
      

   2. Create a temp table and insert values.

      CREATE TEMPORARY TABLE temptable ( col1 VARCHAR(255) NOT NULL,
                                  col2 VARCHAR(255) NOT NULL,
                                  col3 TIME NOT NULL,
                                  col4 VARCHAR(255) NOT NULL,
                                  col5 TIME NOT NULL,
                                  col6 VARCHAR(10000) NOT NULL,
                                  col7 VARCHAR(255) NOT NULL);
      
      LOAD DATA LOCAL INFILE '/cal.csv'
      INTO TABLE temptable 
      FIELDS TERMINATED BY ',' 
      ENCLOSED BY '"' 
      LINES TERMINATED BY 'n'
      IGNORE 1 LINES;
      

   3. Add post id into temp tables using AUTO_INCREMENT + @PostID , so you can add details into linked tables like (table-prefix)_postmeta table

      ALTER TABLE temptable ADD Postid INT NOT NULL  AUTO_INCREMENT PRIMARY 
      KEY FIRST;
      UPDATE temptable SET Postid = (Postid + @PostID);
      

   4. Then inset data into the (table-prefix)_post table

      INSERT INTO `(table-prefix)_posts`(`ID`,
              `post_author`,
              `post_content`,
              `post_title`,
              `post_name`,
              `post_excerpt`,
              `post_type`,
              `post_status`,
              `comment_status`
              ,`ping_status`,
              `post_date`)
       SELECT Postid,
              1,
              col6,
              col1,
              LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col1, '.', ''), '@', '-'), '&', '-'), '/', '-'), ' ' , '-' ),'---','-')),
              LEFT (col6, 200),
              'events',
              'publish', --to make draft set to 'draft' 
              'closed', 
              'closed',
              STR_TO_DATE(col2,'%d/%m/%Y %r')
      FROM temptable;
      

   5. Then add to any other tables

      INSERT INTO `(table-prefix)_postmeta`(`Post_id`,`meta_key`,`meta_value`)
      SELECT Postid,'meta-box-startdate',TIMESTAMP(STR_TO_DATE(col2,'%d/%m/%Y %T'), col3)
      FROM temptable;
      

2. The fact that there are lots of posts, or that the posts have lots of content, really shouldn’t be a problem: that’s what bulk importing is for.

   The HTML code in the posts also shouldn’t be an issue as long as the CSV is correct: it ought to have quoted the code so that it still parses correctly.

   However, you’ll need to create an appropriate table with appropriate column types, so that you can import the data as it should be. The CSV won’t contain details about column types.

   Most of the column types will probably be obvious, but you might want to think about using a type of TEXT (or even LONGTEXT) for the post content itself, if the posts can get very long.

3. To import csv to MySQL we have Load Data Infile syntax. As the first answer conveys, you need to have table structure in accordance with the csv file you want to load, then the csv fileds are mapped with the table columns while you exec the command.

   There is a blog post which explains different use-cases for importing CSV to MySQL you might want to refer (also check comments as well).

   Hope this helps.

Comments are closed.