How to import csv and save in MSSQL database using golang?

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\n", 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?

maerics · Accepted Answer · 2018-10-02 23:31:30Z

4

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)
    }
  }
}

edited Oct 2, 2018 at 23:31

answered Oct 2, 2018 at 23:19

maerics

157k47 gold badges277 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Prashanth Over a year ago

Thank you so much for your help. So here we have a for loop for 21 columns . So can you please elaborate on how to add the "stmt,Exec()" . I am quite new to golang so can you please walk me through the for loop .

maerics Over a year ago

You can find plenty of online documentation about for-loops and sql.Stmt.Exec. Good luck!

Collectives™ on Stack Overflow

How to import csv and save in MSSQL database using golang?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related