{"id":4159,"date":"2024-10-31T14:18:18","date_gmt":"2024-10-31T07:18:18","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4159"},"modified":"2024-10-31T16:53:14","modified_gmt":"2024-10-31T09:53:14","slug":"import-csv","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-go\/import-csv\/","title":{"rendered":"SQLite Go: Importing CSV Data into a Table"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to import data from a CSV file into a table in Go.<\/p>\n\n\n\n

How to import CSV file into a table<\/h2>\n\n\n\n

To import data from a CSV file into a table, you follow these steps:<\/p>\n\n\n\n

First, read data from a CSV file into a list (slice) of records.<\/p>\n\n\n\n

Second, connect to the SQLite database file<\/a>:<\/p>\n\n\n

db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert each record into a table.<\/p>\n\n\n\n
Reading data from a CSV file<\/h2>\n\n\n\n
Step 1. Create a new countries.csv<\/code> file in the project directory with the following contents:<\/p>\n\n\n
name,population,area\nIndia,1450935791,2973190\nChina,1419321278,9388211\nUnited States,345426571,9147420\nIndonesia,283487931,1811570\nPakistan,251269164,770880\nNigeria,232679478,910770\nBrazil,211998573,8358140\nBangladesh,173562364,130170\nRussia,144820423,16376870\nEthiopia,132059767,1000000<\/code><\/span><\/pre>\n\n\n
Step 2. Define a new function ReadCSV()<\/code> that reads data from the countries.csv<\/code> file and returns a list of Country<\/code> struct instances:<\/p>\n\n\n
func ReadCSV(filename string) ([]Country, error) {\n    \/\/ Open the CSV file<\/span>\n    file, err<\/span> := os.Open(filename)\n    if<\/span> err != nil {\n        return<\/span> nil, err\n    }\n    defer file.Close()\n\n\n    \/\/ Read the CSV file<\/span>\n    reader<\/span> := csv.NewReader(file)\n    records, err<\/span> := reader.ReadAll()\n    if<\/span> err != nil {\n        return<\/span> nil, err\n    }\n\n    \/\/ Parse the CSV file<\/span>\n    var<\/span> countries []Country\n    for<\/span> _, record<\/span> := range records[1<\/span>:] { \/\/ Skip header row<\/span>\n        population, err<\/span> := strconv.Atoi(record[1<\/span>])\n        if<\/span> err != nil {\n            return<\/span> nil, err\n        }\n        area, err<\/span> := strconv.Atoi(record[2<\/span>])\n        if<\/span> err != nil {\n            return<\/span> nil, err\n        }\n        country<\/span> := Country{\n            Name<\/span>:       record[0<\/span>],\n            Population<\/span>: population,\n            Area<\/span>:       area,\n        }\n        countries = append(countries, country)\n    }\n\n    return<\/span> countries, nil\n}\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Inserting data in the countries table<\/h2>\n\n\n\n
Step 1. Modify the main.go<\/code> with the following code:<\/p>\n\n\n
package<\/span> main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    \"fmt\"<\/span>\n\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)\n\nfunc<\/span> main<\/span>()<\/span><\/span> {\n    \/\/ connect to the SQLite database<\/span>\n    db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n    defer<\/span> db.Close()\n\n\n    \/\/ read the CSV file<\/span>\n    countries, err := ReadCSV(\"countries.csv\"<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n\n    \/\/ insert the data into the SQLite database<\/span>\n    for<\/span> _, country := range<\/span> countries {\n        _, err := Insert(db, &country)\n        if<\/span> err != nil<\/span> {\n            fmt.Println(err)\n            break<\/span>\n        }\n    }\n    \n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, call the ReadCSV<\/code> function and return a list of Country<\/code> structs:<\/p>\n\n\n
countries, err := ReadCSV(\"countries.csv\"<\/span>)\nif<\/span> err != nil<\/span> {\n    fmt.Println(err)\n    return<\/span>\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the Insert()<\/code> method to insert each country into the countries<\/code> table. <\/p>\n\n\n\n
for<\/span> _, country := range<\/span> countries {\n    _, err := Insert(db, &country)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        break<\/span>\n    }\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Run the Go program<\/p>\n\n\n
go<\/span> run main.go<\/span> country.go<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying data<\/h2>\n\n\n\n
Step 1. Open your terminal and navigate to the project directory.<\/p>\n\n\n\n
Step 2. Connect to the my.db<\/code> using the sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3 my.db<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Format the query output:<\/p>\n\n\n
.header on\n.mode column\n.nullvalue null<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Retrieve data from the countries<\/code> table:<\/p>\n\n\n
select<\/span> * from countries;<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id  name           population  area\n--  -------------  ----------  --------\n1   India          1450935791  2973190\n2   China          1419321278  9388211\n3   United States  345426571   9147420\n4   Indonesia      283487931   1811570\n5   Pakistan       251269164   770880\n6   Nigeria        232679478   910770\n7   Brazil         211998573   8358140\n8   Bangladesh     173562364   130170\n9   Russia         144820423   16376870\n10  Ethiopia       132059767   1000000<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows that the data from the countries.csv<\/code> has been imported successfully.<\/p>\n\n\n\n
Step 4. Quit the sqlite3<\/code> tool:<\/p>\n\n\n
.quit<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Read data from a CSV file and insert each row into a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Note that we developed the Insert<\/code> function in the inserting data into the table tutorial<\/a>.<\/p>\n\n\n