如何使用golang导入csv并保存在MSSQL数据库中？

Question

This is my code. Currently, I just read the contents of the CSV file. I need to migrate this CSV file to MSSQL using Go Language Application

package main

import (
    "encoding/csv"
    "log"
    "os"
)

func main() {
    rows := readOrders("Ec2Instances.csv")
}

func readOrders(name string) [][]string {
    f, err := os.Open(name)
    if err != nil {
        log.Fatalf("Cannot open '%s': %s
", name, err.Error())
    }
    defer f.Close()
    r := csv.NewReader(f)
    r.Comma = ';'
    rows, err := r.ReadAll()
    if err != nil {
        log.Fatalln("Cannot read CSV data:", err.Error())
    }
    return rows
}

I want the CSV file to be written specifically into the "MSSQL Database". I have installed MSSQL and SSMS Softwares. How do I access these and write data into it?

Answer 1

It looks like you're half way there!

Your sample code looks like it will read CSV data from a named file. The next step would be to connect to the MSSQL database instance and insert the rows from the CSV data.

To do so, you'll need to find a golang driver for that database (denisenkom/go-mssqldb looks worth trying, there's even a simple example) and follow the patterns established by the go database/sql package (especially the package examples).

A working solution might look something like this (not working but demonstrative):

import (
  "database/sql"
  _ "github.com/denisenkom/go-mssqldb"
  // ...
)

func main() {
  rows := readOrders("Ec2Instances.csv")
  insertRowsToDatabase(rows)
}

func insertRowsToDatabase(rows [][]string) {
  // Connect to the database.
  connString := "server=myserver;user id=123;password=secret;port=234"
  db, err := sql.Open("mssql", connString)
  if err != nil {
    log.Fatal("Open connection failed:", err.Error())
  }
  defer db.Close()

  // Insert the rows, omitting the first header row from the CSV.
  stmt, err := db.Prepare("INSERT INTO Ec2Instances(id, name) VALUES(?, ?)")
  if err != nil {
    log.Fatal(err)
  }
  for _, row := range rows[1:] {
    _, err := stmt.Exec(row[0], row[1])
    if err != nil {
      log.Fatal(err)
    }
  }
}