{"id":3920,"date":"2024-07-15T10:02:20","date_gmt":"2024-07-15T03:02:20","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3920"},"modified":"2024-07-22T07:48:56","modified_gmt":"2024-07-22T00:48:56","slug":"nodejs-sql-server-import-csv","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/nodejs-sql-server\/nodejs-sql-server-import-csv\/","title":{"rendered":"Node.js SQL Server: Import CSV"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn to import data from a CSV file into the SQL Server database using a Node.js program.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0Inserting data into a table from Node.js tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

We’ll import author data from a CSV file into the Authors table.<\/p>\n\n\n\n

Step 1. Open the terminal and install the csv-parse<\/code> package:<\/p>\n\n\n

npm install csv-parse<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The csv-parse<\/code> package allows you to parse a CSV input into JavaScript arrays or objects.<\/p>\n\n\n\n
Step 2. Create a new directory called data<\/code> within the project directory:<\/p>\n\n\n
mkdir data<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Create a new CSV file called authors.csv<\/code> that contains author data inside the data<\/code> directory:<\/p>\n\n\n
FirstName,LastName,BirthDate\nAgatha,Christie,1890<\/span>-09<\/span>-15<\/span>\nMark,Twain,1835<\/span>-11<\/span>-30<\/span>\nGeorge,Orwell,1903<\/span>-06<\/span>-25<\/span>\nJane,Austen,1775<\/span>-12<\/span>-16<\/span>\nCharles,Dickens,1812<\/span>-02<\/span>-07<\/span>\nF. Scott,Fitzgerald,1896<\/span>-09<\/span>-24<\/span>\nErnest,Hemingway,1899<\/span>-07<\/span>-21<\/span>\nLeo,Tolstoy,1828<\/span>-09<\/span>-09<\/span>\nVirginia,Woolf,1882<\/span>-01<\/span>-25<\/span>\nJ.K.,Rowling,1965<\/span>-07<\/span>-31<\/span>\nJ.R.R.,Tolkien,1892<\/span>-01<\/span>-03<\/span>\nStephen,King,1947<\/span>-09<\/span>-21<\/span>\nHarper,Lee,1926<\/span>-04<\/span>-28<\/span>\nH.G.,Wells,1866<\/span>-09<\/span>-21<\/span>\nHerman,Melville,1819<\/span>-08<\/span>-01<\/span>\nMary,Shelley,1797<\/span>-08<\/span>-30<\/span>\nOscar,Wilde,1854<\/span>-10<\/span>-16<\/span>\nEmily,Bronte,1818<\/span>-07<\/span>-30<\/span>\nJ.D.,Salinger,1919<\/span>-01<\/span>-01<\/span>\nKurt,Vonnegut,1922<\/span>-11<\/span>-11<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The CSV file has three columns first name, last name, and birth date with 20 authors.<\/p>\n\n\n\n
Step 4. Create a new file read-csv.js<\/code> that stores the code to parse the authors.csv<\/code> data:<\/p>\n\n\n
import<\/span> { createReadStream } from<\/span> 'fs'<\/span>;\nimport<\/span> { parse } from<\/span> 'csv-parse'<\/span>;\n\nexport<\/span> async<\/span> function<\/span> readCSV<\/span>(filePath<\/span>) <\/span>{\n  const<\/span> results = [];\n\n  return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n    \/\/ Create a read stream for the CSV file<\/span>\n    createReadStream(filePath)\n      \/\/ Use columns option to automatically map CSV headers to object keys<\/span>\n      .pipe(parse({ columns<\/span>: true<\/span> }))\n      .on('data'<\/span>, (data) => {\n        results.push({\n          firstName<\/span>: data.FirstName,\n          lastName<\/span>: data.LastName,\n          birthDate<\/span>: data.BirthDate,\n        });\n      })\n      .on('end'<\/span>, () => resolve(results))\n      .on('error'<\/span>, (error) => reject(error));\n  });\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import createReadStream<\/code> and parse functions from the fs and csv-parse<\/code> respectively:<\/p>\n\n\n
import<\/span> { createReadStream } from<\/span> 'fs'<\/span>;\nimport<\/span> { parse } from<\/span> 'csv-parse'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a new function readCSV<\/code> that reads data from a CSV file specified by a file path:<\/p>\n\n\n
export<\/span> async<\/span> function<\/span> readCSV<\/span>(filePath<\/span>) <\/span>{\n\/\/ ...<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Also, export the function using the export<\/code> statement.<\/p>\n\n\n\n
Third, parse the CSV file using the parse function. The { columns: true }<\/code> argument instructs the parse function to map CSV headers to object keys automatically:<\/p>\n\n\n
.pipe(parse({ columns<\/span>: true<\/span> }))<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Once having data, we manually create an author object with three keys firstName<\/code>, lastName<\/code>, and birthDate<\/code> that map to the FirstName<\/code>, LastName<\/code>, and BirthDate<\/code> properties respectively:<\/p>\n\n\n
.on('data'<\/span>, (data) => {\n        results.push({\n          firstName<\/span>: data.FirstName,\n          lastName<\/span>: data.LastName,\n          birthDate<\/span>: data.BirthDate,\n        });\n      })<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Modify the insert-many.js<\/code> to export the insertAuthors<\/code> function using the named export:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;\n\nconst<\/span> insertAuthors = async<\/span> (authors) => {\n  try<\/span> {\n    \/\/ Connect to the database<\/span>\n    await<\/span> sql.connect(config);\n\n    \/\/ Construct the insert query<\/span>\n    let<\/span> query = 'INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES '<\/span>;\n\n    \/\/ Build the values part of the query<\/span>\n    const<\/span> values = authors\n      .map(\n        ({ firstName, lastName, birthDate }<\/span>) =><\/span>\n          `('${firstName}<\/span>', '${lastName}<\/span>', '${birthDate}<\/span>')`<\/span>\n      )\n      .join(', '<\/span>);\n\n    \/\/ Complete the query<\/span>\n    query += values;\n\n    \/\/ Execute the query<\/span>\n    await<\/span> sql.query(query);\n\n    console<\/span>.log(`${authors.length}<\/span> authors were inserted successfully!`<\/span>);\n  } catch<\/span> (err) {\n    console<\/span>.error('Error inserting authors:'<\/span>, err);\n  } finally<\/span> {\n    \/\/ Close the database connection<\/span>\n    await<\/span> sql.close();\n  }\n};\n\nexport<\/span> { insertAuthors };<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Modify the index.js<\/code> file that reads data from the .\/data\/authors.csv<\/code> file and inserts it into the Authors<\/code> table:<\/p>\n\n\n
import<\/span> { readCSV } from<\/span> '.\/read-csv.js'<\/span>;\nimport<\/span> { insertAuthors } from<\/span> '.\/insert-many.js'<\/span>;\n\nconst<\/span> filePath = 'data\/authors.csv'<\/span>;\n\ntry<\/span> {\n  const<\/span> authors = await<\/span> readCSV(filePath);\n  await<\/span> insertAuthors(authors);\n} catch<\/span> (err) {\n  console<\/span>.error(err);\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the readCSV<\/code> function file from the read-csv.js<\/code> module and insertAuthors<\/code> function from the insert-many.js<\/code> module:<\/p>\n\n\n
import<\/span> { readCSV } from<\/span> '.\/read-csv.js'<\/span>;\nimport<\/span> { insertAuthors } from<\/span> '.\/insert-many.js'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, read data from the authors.csv<\/code> file by calling the readCSV()<\/code> function:<\/p>\n\n\n
const<\/span> authors = await<\/span> readCSV(filePath);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The readCSV()<\/code> function returns an array of author objects<\/p>\n\n\n\n
Third, insert authors from the array into the Authors<\/code> table by calling the insertAuthors<\/code> function:<\/p>\n\n\n
await<\/span> insertAuthors(authors);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 7. Open the terminal and run the following command to import data from data\/authors.csv<\/code> file into the Authors<\/code> table:<\/p>\n\n\n
npm start<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Verify the inserts #<\/a><\/h2>\n\n\n\n
Step 1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server.<\/p>\n\n\n\n
Step 2. Execute the following SELECT<\/code> statement to query data from the Authors<\/code> table:<\/p>\n\n\n
SELECT * FROM Authors;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
\"sql<\/figure>\n\n\n\n
The output indicate the data from the CSV file has been imported into the Authors<\/code> table successfully.<\/p>\n\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the csv-parse<\/code> package to read data from a CSV file and execute an INSERT<\/code> statement to insert rows into a table in the SQL Server database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t