Node.js SQL Server: Import CSV

Summary: in this tutorial, you will learn to import data from a CSV file into the SQL Server database using a Node.js program.

This tutorial begins where the Inserting data into a table from Node.js tutorial left off.

We’ll import author data from a CSV file into the Authors table.

Step 1. Open the terminal and install the csv-parse package:

npm install csv-parseCode language: JavaScript (javascript)

The csv-parse package allows you to parse a CSV input into JavaScript arrays or objects.

Step 2. Create a new directory called data within the project directory:

mkdir dataCode language: JavaScript (javascript)

Step 3. Create a new CSV file called authors.csv that contains author data inside the data directory:

FirstName,LastName,BirthDate
Agatha,Christie,1890-09-15
Mark,Twain,1835-11-30
George,Orwell,1903-06-25
Jane,Austen,1775-12-16
Charles,Dickens,1812-02-07
F. Scott,Fitzgerald,1896-09-24
Ernest,Hemingway,1899-07-21
Leo,Tolstoy,1828-09-09
Virginia,Woolf,1882-01-25
J.K.,Rowling,1965-07-31
J.R.R.,Tolkien,1892-01-03
Stephen,King,1947-09-21
Harper,Lee,1926-04-28
H.G.,Wells,1866-09-21
Herman,Melville,1819-08-01
Mary,Shelley,1797-08-30
Oscar,Wilde,1854-10-16
Emily,Bronte,1818-07-30
J.D.,Salinger,1919-01-01
Kurt,Vonnegut,1922-11-11Code language: JavaScript (javascript)

The CSV file has three columns first name, last name, and birth date with 20 authors.

Step 4. Create a new file read-csv.js that stores the code to parse the authors.csv data:

import { createReadStream } from 'fs';
import { parse } from 'csv-parse';

export async function readCSV(filePath) {
  const results = [];

  return new Promise((resolve, reject) => {
    // Create a read stream for the CSV file
    createReadStream(filePath)
      // Use columns option to automatically map CSV headers to object keys
      .pipe(parse({ columns: true }))
      .on('data', (data) => {
        results.push({
          firstName: data.FirstName,
          lastName: data.LastName,
          birthDate: data.BirthDate,
        });
      })
      .on('end', () => resolve(results))
      .on('error', (error) => reject(error));
  });
}Code language: JavaScript (javascript)

How it works.

First, import createReadStream and parse functions from the fs and csv-parse respectively:

import { createReadStream } from 'fs';
import { parse } from 'csv-parse';Code language: JavaScript (javascript)

Second, create a new function readCSV that reads data from a CSV file specified by a file path:

export async function readCSV(filePath) {
// ...Code language: JavaScript (javascript)

Also, export the function using the export statement.

Third, parse the CSV file using the parse function. The { columns: true } argument instructs the parse function to map CSV headers to object keys automatically:

.pipe(parse({ columns: true }))Code language: JavaScript (javascript)

Once having data, we manually create an author object with three keys firstName, lastName, and birthDate that map to the FirstName, LastName, and BirthDate properties respectively:

.on('data', (data) => {
        results.push({
          firstName: data.FirstName,
          lastName: data.LastName,
          birthDate: data.BirthDate,
        });
      })Code language: JavaScript (javascript)

Step 5. Modify the insert-many.js to export the insertAuthors function using the named export:

import sql from 'mssql';
import { config } from './config.js';

const insertAuthors = async (authors) => {
  try {
    // Connect to the database
    await sql.connect(config);

    // Construct the insert query
    let query = 'INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES ';

    // Build the values part of the query
    const values = authors
      .map(
        ({ firstName, lastName, birthDate }) =>
          `('${firstName}', '${lastName}', '${birthDate}')`
      )
      .join(', ');

    // Complete the query
    query += values;

    // Execute the query
    await sql.query(query);

    console.log(`${authors.length} authors were inserted successfully!`);
  } catch (err) {
    console.error('Error inserting authors:', err);
  } finally {
    // Close the database connection
    await sql.close();
  }
};

export { insertAuthors };Code language: JavaScript (javascript)

Step 6. Modify the index.js file that reads data from the ./data/authors.csv file and inserts it into the Authors table:

import { readCSV } from './read-csv.js';
import { insertAuthors } from './insert-many.js';

const filePath = 'data/authors.csv';

try {
  const authors = await readCSV(filePath);
  await insertAuthors(authors);
} catch (err) {
  console.error(err);
}Code language: JavaScript (javascript)

How it works.

First, import the readCSV function file from the read-csv.js module and insertAuthors function from the insert-many.js module:

import { readCSV } from './read-csv.js';
import { insertAuthors } from './insert-many.js';Code language: JavaScript (javascript)

Second, read data from the authors.csv file by calling the readCSV() function:

const authors = await readCSV(filePath);Code language: JavaScript (javascript)

The readCSV() function returns an array of author objects

Third, insert authors from the array into the Authors table by calling the insertAuthors function:

await insertAuthors(authors);Code language: JavaScript (javascript)

Step 7. Open the terminal and run the following command to import data from data/authors.csv file into the Authors table:

npm startCode language: JavaScript (javascript)

Verify the inserts

Step 1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Execute the following SELECT statement to query data from the Authors table:

SELECT * FROM Authors;Code language: JavaScript (javascript)

The output indicate the data from the CSV file has been imported into the Authors table successfully.

Download the project source code

Download the project source code

Summary

• Use the csv-parse package to read data from a CSV file and execute an INSERT statement to insert rows into a table in the SQL Server database.