Import CSV file in node-sqlite3

Question

I'm using the npm sqlite3 package in my web application for a mock-up restaurant. When my local server is started I'm creating a menuItems table:

var db = new sqlite3.Database(':memory:');
db.serialize(function() {
  db.run('CREATE TABLE menuItems ('
          + 'itemName VARCHAR(255),'
          + 'itemDescription VARCHAR(255),'
          + 'unitPrice REAL'
          + ');')
  .run("INSERT INTO menuItems (itemName, itemDescription, unitPrice) VALUES"
          + " ('Fish Filet', 'Yummy fish in a sandwich.', 9.95)")
});

I would like menuItems table to be separate from this js file. Options I can think of:

Use current hard-coded commands to insert individual items into the table.
Use SQLite3 to import the CSV file and insert it into the table.
Read items from a CSV file and insert them in a loop.
Establish database before server starts and pass it into sqlite3.Database() constructor.

I'd like to go with option 2. However, I am open to any suggestion. There are ways to import the CSV file using SQLite. I'm trying to do this but from sqlite3 npm package. When I try to import the file via the same command (which seems to be sqlite3-specific) from the linked page:

db.run('.import "C:/Users/path/to/csv/file.csv"'
      + 'INTO TABLE menuItems'
      + 'FIELDS TERMINATED BY ","'
      + 'ENCLOSED BY "\'"'
      + 'LINES TERMINATED BY \'\\n\''
      + 'IGNORE 1 ROWS'
      + ');');

I receive:

events.js:183
  throw er; // Unhandled 'error' event
  ^

Error: SQLITE_ERROR: near ".": syntax error

Trying normal SQL syntax:

.run('BULK INSERT menuItems'
      + 'FROM "C:/Users/path/to/csv/file.csv" '
      + 'WITH '
      + '{ '
      + 'FIRSTROW = 2,'
      + 'FIELDTERMINATOR = ","'
      + 'ROWTERMINATOR = "\n",'
      + 'ERRORFILE = "C:/Users/path/to/csv/error_file.csv" '
      + 'TABLOCK'
      + '}')

I receive:

events.js:183
      throw er; // Unhandled 'error' event
      ^

Error: SQLITE_ERROR: near "BULK": syntax error

Is my syntax incorrect? Is there a better way? Option 3 I haven't tried.

score 1 · Accepted Answer · answered Aug 10 '18 at 16:24

1

I ended up going with Option 3. Namely, I used the fs package to read in the CSV file, did some parsing, returned the results within a Promise object, and did the database insertions within the then of said Promise object.

answered Aug 10 '18 at 16:24

natn2323

1,983
1
13
30

Can you share the code of the implementation? I have been doing this but still getting the errors.. – Nikhil Agarwal Mar 01 '20 at 12:55
1

Hi @NikhilAgarwal, the implementation can be found [here](https://github.com/natn2323/parzival/blob/master/public/javascript/DBManager.js) in the private `getCSV` function. – natn2323 Mar 03 '20 at 05:33

score 0 · Answer 2 · answered Jun 28 '19 at 20:14

try

db.run('.import "C:/Users/path/to/csv/file.csv"'
      + 'INTO TABLE menuItems'
      + 'FIELDS TERMINATED BY ","'
      + 'ENCLOSED BY "\'"'
      + 'LINES TERMINATED BY \'\\n\''
      + 'IGNORE 1 ROWS'
      + ')');

commands beginning with . don't need semicolon at end

score 0 · Answer 3 · answered Feb 03 '21 at 21:51

There is a little example for read your csv into sqlite3 db.

const sql3 = require('better-sqlite3');
const   db = new sql3( 'memory.db' );
const  csv = require('csv-parser');
const   fs = require('fs');

// create table
db.exec( 'CREATE TABLE IF NOT EXISTS menuItems ( itemName TEXT, itemDescription TEXT, unitPrice REAL );' );
//db.exec( 'DROP TABLE menuItems;' );

const insrow = db.prepare( 'insert into menuItems ( itemName, itemDescription, unitPrice ) VALUES (?, ?, ?)' );


fs.createReadStream('C:/Users/path/to/csv/file.csv')
  .pipe(csv({"separator":";"}))
  .on('data', (row) => {
    
    insrow.run( row.itemName, row.itemDescription, row.unitPrice );
    console.log(row);
  })
  .on('end', () => {
    console.log('CSV file successfully processed');
    db.close();
  });

The database in example is memory.db, and the csv format is not comma-separated, but semicolon separated, change separator, if it necessary.

The csv in this case must be started with itemName;itemDescription;unitPrice header, and a row looks like Fish Filet;Yummy fish in a sandwich.;9.95 and so on.

score 0 · Answer 4 · answered Jul 18 '23 at 14:38

create file.csv file for example, the header is the column names, rows are records, separated by ; and strings in "string" format:

itemName;itemDescription;unitPrice

"Fish Filet";"Yummy fish in a sandwich.";9.95

2.1 If the column types are not so important, you can import the csv file to the db, with a file.sql3:

.mode csv
.separator ";"
drop table if exists menuItems;
.import "file.csv" menuItems

2.2 run import from nodejs:

const { exec } = require('node:child_process');

exec('sqlite3 "memory.db" < "file.sql3"', (error, stdout, stderr) => {
    if (error) {
        console.error(`exec error: ${error}`);
        return;
    }
});

3.1 If the column types are important, you can import the csv file to the db, into a temporary tmp table, with this tmp.sql3:

.mode csv
.separator ";"
drop table if exists tmp;
.import "file.csv" tmp

3.2 run import from nodejs:

const { exec } = require('node:child_process');
const sql3 = require('better-sqlite3');   
exec('sqlite3 "memory.db" < "tmp.sql3"', (error, stdout, stderr) => {
    if (error) {
        console.error(`exec error: ${error}`);
        return;
    }
    const db = new sql3('memory.db');

    db.exec( "CREATE TABLE IF NOT EXISTS menuItems(" +
                        + 'itemName VARCHAR(255),'
                        + 'itemDescription VARCHAR(255),'
                        + 'unitPrice REAL'
                        + ');');
    db.exec("INSERT INTO menuItems " +
          + "select itemName, itemDescription, unitPrice from tmp;");
});

Import CSV file in node-sqlite3

4 Answers4

Linked

Related