{"id":4078,"date":"2024-10-29T16:52:41","date_gmt":"2024-10-29T09:52:41","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4078"},"modified":"2024-10-29T16:56:17","modified_gmt":"2024-10-29T09:56:17","slug":"import-csv","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-bun\/import-csv\/","title":{"rendered":"SQLite Bun: Importing CSV"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to import data from a CSV file into a table in SQLite using Bun.<\/p>\n\n\n\n

Importing CSV Into a Table<\/h2>\n\n\n\n

Step 1. Open your terminal and install csv-parse<\/code> package for parsing CSV data by running the following command:<\/p>\n\n\n

bun install csv-parse<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Modify the author.ts<\/code> file to import the readFileSync<\/code> from fs<\/code> module and parse<\/code> function from the csv-parse\/sync<\/code> module:<\/p>\n\n\n
import<\/span> { readFileSync } from<\/span> \"fs\"<\/span>;\nimport<\/span> { parse } from<\/span> \"csv-parse\/sync\"<\/span>;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Define a new function called importCSV<\/code> that reads from a CSV file that contains author data (First Name and Last Name column) and inserts each line into the authors<\/code> table:<\/p>\n\n\n
export<\/span> function<\/span> importCSV<\/span>(db: Database, filename: string<\/span><\/span>) <\/span>{\n  \/\/ read the CSV file<\/span>\n  const<\/span> content = readFileSync(filename, { encoding: \"utf-8\"<\/span> });\n\n  \/\/ parse CSV content<\/span>\n  const<\/span> records = parse(content, {\n    columns: (header<\/span><\/span>) =><\/span>\n      header.map((column<\/span>: string<\/span><\/span>) =><\/span> {\n        if<\/span> (column === \"First Name\"<\/span>) return<\/span> \"firstName\"<\/span>;\n        if<\/span> (column === \"Last Name\"<\/span>) return<\/span> \"lastName\"<\/span>;\n        return<\/span> column;\n      }),\n    delimiter: \",\"<\/span>,\n    trim: true<\/span>,\n    skip_empty_lines: true<\/span>,\n  });\n\n  \/\/  insert each record into the authors table<\/span>\n  for<\/span> (const<\/span> { firstName, lastName } of records) {\n    insert(db, firstName, lastName);\n  }\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, read text from a CSV file specified by the filename<\/code> into the content<\/code> variable:<\/p>\n\n\n
const<\/span> content = readFileSync(filename, { encoding: \"utf-8\"<\/span> });<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, parse the CSV contents into an array of records, each is an object that contains the firstName<\/code> and lastName<\/code> fields:<\/p>\n\n\n
const<\/span> records = parse(content, {\n    columns: (header<\/span><\/span>) =><\/span>\n      header.map((column<\/span>: string<\/span><\/span>) =><\/span> {\n        if<\/span> (column === \"First Name\"<\/span>) return<\/span> \"firstName\"<\/span>;\n        if<\/span> (column === \"Last Name\"<\/span>) return<\/span> \"lastName\"<\/span>;\n        return<\/span> column;\n      }),\n    delimiter: \",\"<\/span>,\n    trim: true<\/span>,\n    skip_empty_lines: true<\/span>,\n});<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the following maps the data in the First Name<\/code> and Last Name<\/code> to firstName<\/code> and lastName<\/code> properties:<\/p>\n\n\n
columns: (header<\/span><\/span>) =><\/span>\n      header.map((column<\/span>: string<\/span><\/span>) =><\/span> {\n        if<\/span> (column === \"First Name\"<\/span>) return<\/span> \"firstName\"<\/span>;\n        if<\/span> (column === \"Last Name\"<\/span>) return<\/span> \"lastName\"<\/span>;\n        return<\/span> column;\n}),<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, iterate over the records and insert each into the authors<\/code> table by calling the insert()<\/code> method:<\/p>\n\n\n
for<\/span> (const<\/span> { firstName, lastName } of records) {\n  insert(db, firstName, lastName);\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Create a new import.ts<\/code> file with the following code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { importCSV } from<\/span> \".\/author\"<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict: true<\/span> });\n  importCSV(db, \"authors.csv\"<\/span>);\n} catch<\/span> (error) {\n  console<\/span>.log(error);\n} finally<\/span> {\n  if<\/span> (db) db.close();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the importCSV<\/code> function from the author.ts<\/code> module:<\/p>\n\n\n
import<\/span> { importCSV } from<\/span> \".\/author\"<\/span>;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, connect to the pub.db<\/code> database:<\/p>\n\n\n
db = new<\/span> Database(\"pub.db\"<\/span>, { strict: true<\/span> });<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, call the importCSV<\/code> function to import data from the authors.csv<\/code> file into the authors<\/code> table:<\/p>\n\n\n
importCSV(db, \"authors.csv\"<\/span>);<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Download the authors.csv<\/code> file and copy it to the project directory.<\/p>\n\n\n\n
Step 6. Run the import.ts<\/code> file to import data from the authors.csv<\/code> into the authors<\/code> table:<\/p>\n\n\n
bun import.ts<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying data<\/h2>\n\n\n\n
Step 1. Open a new terminal and navigate to the project directory.<\/p>\n\n\n\n
Step 2. Connect to the pub.db<\/code> database using the sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3<\/span> pub<\/span>.db<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Format the query output:<\/p>\n\n\n
.mode<\/span> column<\/span>\n.header<\/span> on<\/span> \n.nullvalue<\/span> null<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Retrieve data from the authors<\/code> table:<\/p>\n\n\n
select * from<\/span> authors;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id  firstName  lastName\n--  ---------  --------\n1   Alex       Starling\n2   Jamie      Falcon\n3   Casey      Rivers\n4   Morgan     Sky\n5   Taylor     Phoenix\n6   Jordan     Blaze\n7   Avery      Storm\n8   Riley      Moon\n9   Quinn      Frost\n10  Dakota     Ember<\/code><\/span><\/pre>\n\n\n
The output indicates that the Bun app has successfully imported data from the authors.csv<\/code> file into the authors<\/code> table.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the csv-parse<\/code> module to parse a CSV file into an array of objects.<\/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