I'm trying to insert around 20.000 users at the same time in my MSSQL Server from a csv file. I created an API for my Angular2 application and I parsed the csv file to JSON format. But now I'm kind of stuck, I found an answer for this from more that 2 years old so that's not working anymore. Any help?

This is the code I'm already using:

//Insert gebruikers from CSV file
router.post('/gebruikers/csv', type, (req, res) => {

    fs.readFile(req.file.path, 'utf8', function (err, csvData) {
        if (err) {
            res.send("error in file reader: " + err);
            return console.log(err);
        }
        csvParser(csvData, {
            delimiter: ',',
            columns: true
        }, function (err, json) {
            if (err) {
                res.send("error in csvParser: " + err);
                console.log(err);
            } else {
                console.log(json);
                //I think the query should be done here... 
            }
        })
    })
});

Thanks in advance!!

EDIT

Using this code right now but it returns this error:

TypeError: parameter.value.getTime is not a function

Code:

router.post('/gebruikers/csv', type, (req, res) => {

    fs.readFile(req.file.path, 'utf8', function (err, csvData) {
        if (err) {
            res.send("error in file reader: " + err);
            return console.log(err);
        }
        csvParser(csvData, {
            columns: true,
            ltrim: true,
            rtrim: true
        }, function (err, json) {
            if (err) {
                res.send("error in csvParser: " + err);
                console.log(err);
            } else {
                console.log(json);
                const table = new sql.Table('[Alg].[User]');
                table.create = true;
                table.columns.add('Firstname', sql.NVarChar, { nullable: false });
                table.columns.add('LastName', sql.NVarChar, { nullable: false });
                table.columns.add('PrivateMail', sql.NVarChar, { nullable: false });
                table.columns.add('UserName', sql.NVarChar, { nullable: false });
                table.columns.add('Password', sql.NVarChar, { nullable: false });
                table.columns.add('Auth', sql.NVarChar, { nullable: false });
                table.columns.add('Enabled', sql.Bit, { nullable: false });
                table.columns.add('Created', sql.SmallDateTime, { nullable: false });
                table.columns.add('Manual', sql.Bit, { nullable: false });
                table.columns.add('LastChanged', sql.SmallDateTime, { nullable: false });
                table.columns.add('Staff', sql.Bit, { nullable: false });

                var count = Object.keys(json).length;
                console.log(count);

                for (i = 0; i < count; i++) {
                    table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, "GetDate()", 1, "GetDate()", 1);
                }

                console.log("Async function started!");
                const request = new sql.Request();
                request.bulk(table, (err, result) => {
                    // error checks? 
                    if (err) {
                        console.log("ERROR post bulk gebruikers: " + err);
                    }
                    else {
                        res.send("SUCCES! " + result);
                    }
                })
            }
        })
    })
});

FIXED

As user Grigoriy Chudnov pointed out, I need an instance of new Date(). I was thinking this wouldn't work because it would be in the wrong format, but node-mssql handles this for me.

currentDateTime = new Date();
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, currentDateTime, 1, currentDateTime, 1);

I'm trying to insert around 20.000 users at the same time in my MSSQL Server from a csv file. I created an API for my Angular2 application and I parsed the csv file to JSON format. But now I'm kind of stuck, I found an answer for this from more that 2 years old so that's not working anymore. Any help?

This is the code I'm already using:

//Insert gebruikers from CSV file
router.post('/gebruikers/csv', type, (req, res) => {

    fs.readFile(req.file.path, 'utf8', function (err, csvData) {
        if (err) {
            res.send("error in file reader: " + err);
            return console.log(err);
        }
        csvParser(csvData, {
            delimiter: ',',
            columns: true
        }, function (err, json) {
            if (err) {
                res.send("error in csvParser: " + err);
                console.log(err);
            } else {
                console.log(json);
                //I think the query should be done here... 
            }
        })
    })
});

Thanks in advance!!

EDIT

Using this code right now but it returns this error:

TypeError: parameter.value.getTime is not a function

Code:

router.post('/gebruikers/csv', type, (req, res) => {

    fs.readFile(req.file.path, 'utf8', function (err, csvData) {
        if (err) {
            res.send("error in file reader: " + err);
            return console.log(err);
        }
        csvParser(csvData, {
            columns: true,
            ltrim: true,
            rtrim: true
        }, function (err, json) {
            if (err) {
                res.send("error in csvParser: " + err);
                console.log(err);
            } else {
                console.log(json);
                const table = new sql.Table('[Alg].[User]');
                table.create = true;
                table.columns.add('Firstname', sql.NVarChar, { nullable: false });
                table.columns.add('LastName', sql.NVarChar, { nullable: false });
                table.columns.add('PrivateMail', sql.NVarChar, { nullable: false });
                table.columns.add('UserName', sql.NVarChar, { nullable: false });
                table.columns.add('Password', sql.NVarChar, { nullable: false });
                table.columns.add('Auth', sql.NVarChar, { nullable: false });
                table.columns.add('Enabled', sql.Bit, { nullable: false });
                table.columns.add('Created', sql.SmallDateTime, { nullable: false });
                table.columns.add('Manual', sql.Bit, { nullable: false });
                table.columns.add('LastChanged', sql.SmallDateTime, { nullable: false });
                table.columns.add('Staff', sql.Bit, { nullable: false });

                var count = Object.keys(json).length;
                console.log(count);

                for (i = 0; i < count; i++) {
                    table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, "GetDate()", 1, "GetDate()", 1);
                }

                console.log("Async function started!");
                const request = new sql.Request();
                request.bulk(table, (err, result) => {
                    // error checks? 
                    if (err) {
                        console.log("ERROR post bulk gebruikers: " + err);
                    }
                    else {
                        res.send("SUCCES! " + result);
                    }
                })
            }
        })
    })
});

FIXED

As user Grigoriy Chudnov pointed out, I need an instance of new Date(). I was thinking this wouldn't work because it would be in the wrong format, but node-mssql handles this for me.

currentDateTime = new Date();
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, currentDateTime, 1, currentDateTime, 1);

• javascript
• node.js
• api
• node-mssql

• You provide a string: "GetDate()" for sql.SmallDateTime data type, but there must be an instance of Date, e.g.: new Date() – Grigorii Chudnov Commented Apr 28, 2017 at 15:41
• But GetDate() is a function that has to run on the SQL server itself? Like in a query, you can give GetDate() as a variable and it will place the current date in the table? That's what I have to achieve. Any ideas? – Milan_w Commented May 4, 2017 at 11:33
• If you don't want to provide new Date() you can set the default value in schema, e.g: CREATE TABLE ...... DEFAULT GETDATE() or write a raw SQL-query. – Grigorii Chudnov Commented May 4, 2017 at 21:18
• And no, the string you're supplying won't run on the server. It is processed by the node-mssql library. – Grigorii Chudnov Commented May 4, 2017 at 21:21
• Oh god, I was all the time thinking the Date() instance would be in the wrong format... I'm very sorry!! Thanks a lot for the help!!! – Milan_w Commented May 5, 2017 at 9:47

1 Answer 1

If you're using node-mssql library, use bulk insert to add multiple records at once.

It should look something like this:

'use strict';

const sql = require('mssql');

const user = 'sa';
const password = 'yourStrong(!)Password';

const connStr = `mssql://${user}:${password}@172.17.0.2:1433/tempdb?encrypt=true`;

sql.connect(connStr)
  .then(() => {
    console.log('connected');

    const table = new sql.Table('my_users');
    table.create = true;
    table.columns.add('id', sql.Int, { nullable: false, primary: true });
    table.columns.add('name', sql.VarChar(128), { nullable: false });

    // add here rows to insert into the table
    table.rows.add(1, 'Alice');
    table.rows.add(2, 'Bob');
    table.rows.add(3, 'Carol');

    const request = new sql.Request();
    return request.bulk(table)
  })
  .then(data => {
    console.log(data);
  })
  .catch(err => {
    console.log(err);
  });

And the output is:

connected
{ rowsAffected: 3 }