SQLite Go: Importing CSV Data into a Table

Summary: in this tutorial, you will learn how to import data from a CSV file into a table in Go.

How to import CSV file into a table

To import data from a CSV file into a table, you follow these steps:

First, read data from a CSV file into a list (slice) of records.

Second, connect to the SQLite database file:

db, err := sql.Open("sqlite", "./my.db")Code language: Go (go)

Third, insert each record into a table.

Reading data from a CSV file

Step 1. Create a new countries.csv file in the project directory with the following contents:

name,population,area
India,1450935791,2973190
China,1419321278,9388211
United States,345426571,9147420
Indonesia,283487931,1811570
Pakistan,251269164,770880
Nigeria,232679478,910770
Brazil,211998573,8358140
Bangladesh,173562364,130170
Russia,144820423,16376870
Ethiopia,132059767,1000000

Step 2. Define a new function ReadCSV() that reads data from the countries.csv file and returns a list of Country struct instances:

func ReadCSV(filename string) ([]Country, error) {
    // Open the CSV file
    file, err := os.Open(filename)
    if err != nil {
        return nil, err
    }
    defer file.Close()
    // Read the CSV file
    reader := csv.NewReader(file)
    records, err := reader.ReadAll()
    if err != nil {
        return nil, err
    }
    // Parse the CSV file
    var countries []Country
    for _, record := range records[1:] { // Skip header row
        population, err := strconv.Atoi(record[1])
        if err != nil {
            return nil, err
        }
        area, err := strconv.Atoi(record[2])
        if err != nil {
            return nil, err
        }
        country := Country{
            Name:       record[0],
            Population: population,
            Area:       area,
        }
        countries = append(countries, country)
    }
    return countries, nil
}
Code language: JavaScript (javascript)

Inserting data in the countries table

Step 1. Modify the main.go with the following code:

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/glebarez/go-sqlite"
)
func main() {
    // connect to the SQLite database
    db, err := sql.Open("sqlite", "./my.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()
    // read the CSV file
    countries, err := ReadCSV("countries.csv")
    if err != nil {
        fmt.Println(err)
        return
    }
    // insert the data into the SQLite database
    for _, country := range countries {
        _, err := Insert(db, &country)
        if err != nil {
            fmt.Println(err)
            break
        }
    }
    
}Code language: Go (go)

How it works.

First, call the ReadCSV function and return a list of Country structs:

countries, err := ReadCSV("countries.csv")
if err != nil {
    fmt.Println(err)
    return
}Code language: Go (go)

Second, call the Insert() method to insert each country into the countries table.

Note that we developed the Insert function in the inserting data into the table tutorial.

for _, country := range countries {
    _, err := Insert(db, &country)
    if err != nil {
        fmt.Println(err)
        break
    }
}Code language: Go (go)

Step 4. Run the Go program

go run main.go country.goCode language: Go (go)

Verifying data

Step 1. Open your terminal and navigate to the project directory.

Step 2. Connect to the my.db using the sqlite3 shell:

sqlite3 my.dbCode language: Shell Session (shell)

Step 2. Format the query output:

.header on
.mode column
.nullvalue nullCode language: Shell Session (shell)

Step 3. Retrieve data from the countries table:

select * from countries;Code language: Go (go)

Output:

id  name           population  area
--  -------------  ----------  --------
1   India          1450935791  2973190
2   China          1419321278  9388211
3   United States  345426571   9147420
4   Indonesia      283487931   1811570
5   Pakistan       251269164   770880
6   Nigeria        232679478   910770
7   Brazil         211998573   8358140
8   Bangladesh     173562364   130170
9   Russia         144820423   16376870
10  Ethiopia       132059767   1000000Code language: Shell Session (shell)

The output shows that the data from the countries.csv has been imported successfully.

Step 4. Quit the sqlite3 tool:

.quitCode language: Go (go)

Summary

• Read data from a CSV file and insert each row into a table.