-
Notifications
You must be signed in to change notification settings - Fork 0
/
csvtosql.go
110 lines (89 loc) · 2.19 KB
/
csvtosql.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
package cmd
import (
"context"
"database/sql"
"encoding/csv"
"flag"
"fmt"
"io"
"log"
"os"
"regexp"
_ "github.com/lib/pq"
"github.com/madhab452/csvtosql/cmd/qb"
"github.com/madhab452/csvtosql/cmd/sqlutil"
)
var filePattern = regexp.MustCompile(".csv$")
// CsvToSql ...
type CsvToSql struct {
Fname string
DB *sql.DB
Log *log.Logger
}
// Option holds configuration option for command.
type Option struct {
Fname string
DBURL string
}
// New Creates an instance of CsvToSql
func New(ctx context.Context, log *log.Logger, opts *Option) (*CsvToSql, error) {
fname := flag.String("fname", "", "csv file")
flag.Parse()
if !filePattern.MatchString(*fname) {
return nil, fmt.Errorf("invalid filename, expected csv file")
}
db, err := sql.Open("postgres", opts.DBURL)
if err != nil {
return nil, fmt.Errorf("sql.Open(): %w", err)
}
return &CsvToSql{
Fname: *fname,
Log: log,
DB: db,
}, nil
}
// Exec validates csv data, prepare sql query and runs query against specified db
func (cs *CsvToSql) Exec() error {
f, err := os.Open(cs.Fname)
if err != nil {
return fmt.Errorf("os.Open(): %w", err)
}
defer Close(f, cs.Log)
csvReader := csv.NewReader(f)
records, err := csvReader.ReadAll()
if err != nil { // errors like wrong number if fields in csv
return fmt.Errorf("csvReader.ReadAll(): %w", err)
}
if len(records) <= 1 {
return fmt.Errorf("atleast one record is required")
}
tblname := sqlutil.ToTableName(cs.Fname)
createTableQuery := qb.CreateTbl(func(qb *qb.CreateTblBuilder) {
qb.Table(tblname)
for _, col := range records[0] {
qb.AddCol(sqlutil.ToColumnName(col))
}
}).ToSql()
insertQuery := qb.Insert(func(qb *qb.InsertBuilder) {
qb.Table(tblname)
for _, col := range records[0] {
qb.AddCol(sqlutil.ToColumnName(col))
}
for _, row := range records[1:] {
qb.AddRow(row)
}
}).ToSql()
if _, err := cs.DB.Query(createTableQuery); err != nil {
return fmt.Errorf("cs.DB.Query(createTableQuery): %w", err)
}
if _, err := cs.DB.Query(insertQuery); err != nil {
return fmt.Errorf("cs.DB.Query(insertQuery): %w", err)
}
return nil
}
func Close(r io.Closer, log *log.Logger) {
err := r.Close()
if err != nil {
log.Println(err)
}
}