How to easily parse csv file to use in node.js sequelize database migration strategy?

Question

I've got the migration strategy via sequelize-cli set up and running so the table can be constructed properly. It works using sequelize db:migrate and it creates the table just fine. And sequelize db:migrate:undo will delete the table.

I've also included code (per the SE comment here) to insert a couple of records. That works too. Here's the code for all that:

'use strict';

module.exports = {
  up: function(queryInterface, DataTypes, done) {
    return queryInterface.createTable('survey', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      state: {
        type: DataTypes.TEXT
      },
      age: {
        type: DataTypes.INTEGER
      },
      race: {
        type: DataTypes.TEXT
      },
      gender: {
        type: DataTypes.TEXT
      },
      education: {
        type: DataTypes.TEXT
      },
      q1: {
        type: DataTypes.INTEGER
      },
      q2: {
        type: DataTypes.INTEGER
      },
      .
      .
      .
      q24: {
        type: DataTypes.INTEGER
      },
      q25: {
        type: DataTypes.INTEGER
      }
    }).then(function() {
      queryInterface.sequelize.query("INSERT INTO survey (state, age, race, gender, q1, q7, q24) VALUES ('Texas', 42, 'white', 'female', 5, 4, 3), ('Louisiana', 19, 'hispanic', 'male', 1, 2,5)");
      done();
    });
  },

  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('survey');
  }
};

But I have a CSV file with over 3000 records that needs to be used as the seed data. It's in the db folder and called survey.csv. I also have a seeders folder that I think was created automatically by a sequelize init or sequelize migration:create. It's currently empty.

I could use the concatenate function in Excel to create the ordered n-tuples of data to insert and just paste those 3000 lines into promise part of up, but that seems ridiculous.

I found this reply for a similar question, but in Rails:

You need the CSV library. From the docs:

arr_of_arrs = CSV.read("path/to/file.csv")

This will give you a 2D array which you can process as you like. CSV similar to IO.read, but with a few extras such as header parsing.

It looks like there's a handful (or more) csv node packages, but golly, they're so complicated (like the simply-named csv).

Is there a simpler csv parser (like the one for Rails) that I can use to facilitate the dumping of the csv into the database? Or another method that doesn't require a bazillion lines of superfluous code copied from a spreadsheet?

score 0 · Answer 1 · answered May 09 '23 at 20:53

To create a seed file using Sequelize.js to seed data from a CSV file, you can follow these steps:

Install the necessary packages:

npm install --save sequelize sequelize-cli mysql2 csv-parser

This will install Sequelize, Sequelize CLI, the MySQL2 driver for Sequelize, and the CSV parser.
Create a Sequelize model for your table. For example, if your CSV file contains data about books, you can create a model like this:

npx sequelize-cli model:generate --name Book --attributes title:string,author:string,year:integer

This will create a Book model with title, author, and year attributes.
Run the migration to create the table in the database:

npx sequelize-cli db:migrate
Create a seed file in the seeders directory using the Sequelize CLI

npx sequelize-cli seed:generate --name seed-books

This will create a new seed file in the seeders directory.

Edit the seed file to read the CSV file, parse its contents, and insert them into the database using Sequelize. Here's an example:

const fs = require('fs'); const csv = require('csv-parser'); const Book = require('../models/book');

module.exports = {
  up: (queryInterface, Sequelize) => {
    const results = [];
    return new Promise((resolve, reject) => {
      fs.createReadStream('./data/books.csv')
        .pipe(csv())
        .on('data', (data) => {
          results.push(data);
        })
        .on('end', () => {
          Book.bulkCreate(results)
            .then(() => {
              resolve();
            })
            .catch((error) => {
              console.error(error);
              reject(error);
            });
        });
    });
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Books', null, {});
  },
};

This code reads the CSV file from the ./data/books.csv location, parses its contents using the csv-parser package, and inserts the data into the Book table using the bulkCreate method of Sequelize. The up function is used to insert the data into the database, and the down function is used to delete the data if needed.

Run the seed to insert the data into the database:

npx sequelize-cli db:seed --seed seeders/seed-books.js

This will run the up function of the seed-books seed file and insert the data into the database.
Test the data by querying the database to ensure that the data from the CSV file was inserted correctly.

Note: In this example, I assume that the CSV file is located in a data directory at the root of your project. You'll need to adjust the file path as needed for your own project.

How to easily parse csv file to use in node.js sequelize database migration strategy?

1 Answers1