csv文件通过node.js转存postresql数据库

排坑

分享一下代码

const config = require('../config')
const pglink = config.pg
const csv = require('csvtojson')
const fs = require('fs')
const {Client} = require('pg')
const copyFrom = require('pg-copy-streams').from

module.exports = (targetTable, inputFile, columnStr) => {
  console.log('开始导入')
  // Connecting to Database
  const client = new Client({
    connectionString: pglink,
  })
  client.connect()

  var stream = client.query(copyFrom(`COPY ${targetTable} (${columnStr}) FROM STDIN CSV`));

  var fileStream = fs.createReadStream(inputFile)

  fileStream.on('error', (error) =>{
    console.log(`Error in creating read stream ${error}`)
  })
  stream.on('error', (error) => {
    console.log(`Error in creating stream ${error}`)
  })
  stream.on('end', () => {
    console.log(`Completed loading data into ${targetTable}`)
    client.end()
  })
  fileStream.pipe(stream);
}

调用代码示例

const copyToPg =  require('/* 上面的代码 */')
const path = require('path')

const inputFile = path.join(__dirname, '/csv/country.csv')

copyToPg('Country', path.join(__dirname, '/csv/country.csv'),'country, code, continent')

这里有两个坑需要特别说明targetTable这里是区分大小写的，而且只支持小写。。。

CSV需要使用utf8格式，不然会有中文乱码问题。但是你使用Excel是改不了utf8的，我的操作流程是使用记事本打开csv文件再改变编码转存。
之后再使用Excel编辑。

参考文档

官方说明档：sql-copy

How to do postgresql bulk insert with node-pg-copy-streams