CSV to SQL

main.go

package main
import (
	_ "code.google.com/p/odbc"
	"database/sql"
	"log"
	"flag"
	"runtime"
	"os"
	"bufio"
	"strings"
	"fmt"
	"bytes"
	_ "code.google.com/p/go.text/encoding"
	"code.google.com/p/go.text/encoding/charmap"
	"code.google.com/p/go.text/transform"
)

var (
	mssrv    = flag.String("mssrv", "", "ms sql server name")
	msdb     = flag.String("msdb", "dbname", "ms sql server database name")
	msuser   = flag.String("msuser", "", "ms sql server user name")
	mspass   = flag.String("mspass", "", "ms sql server password")
	msdriver = flag.String("msdriver", defaultDriver(), "ms sql odbc driver name")
	msport   = flag.String("msport", "1433", "ms sql server port number")
	skipFirstLine = flag.Bool("skipFirstLine", false, "skip the first line of the csv")
	file   = flag.String("file", "", "file to transform")
	outFilename   = flag.String("out", "", "file to write")
	clientId   = flag.String("clientId", "", "client_id for extended properties")
	contextClient   = flag.String("contextClient", "", "context for extended properties")
)

func defaultDriver() string {
	if runtime.GOOS == "windows" {
		return "sql server"
	} else {
		return "freetds"
	}
}

func mssqlConnect() (db *sql.DB, err error) {
	var params map[string]string
//	if runtime.GOOS == "windows" {
//		params = map[string]string{
//			"driver":   *msdriver,
//			"server":   *mssrv,
//			"database": *msdb,
//		}
//		if len(*msuser) == 0 {
//			params["trusted_connection"] = "yes"
//		} else {
//			params["uid"] = *msuser
//			params["pwd"] = *mspass
//		}
//	} else {
		params = map[string]string{
			"driver":   *msdriver,
			"server":   *mssrv,
			"port":     *msport,
			"database": *msdb,
			"uid":      *msuser,
			"pwd":      *mspass,
			//"clientcharset": "UTF-8",
			//"debugflags": "0xffff",
		}
//	}
	var c string
	for n, v := range params {
		c += n + "=" + v + ";"
	}
	log.Printf("TEST DE CONNECTION %s", c);
	db, err = sql.Open("odbc", c)
	if err != nil {
		return nil, err
	}
	return db, nil
}

func readFile(db *sql.DB) {
	log.Printf("client %s", *clientId);
	log.Printf("context %s", *contextClient);

	file, err := os.Open(*file)
	if err != nil {
		log.Fatal(err)
	}
	defer file.Close()

	var insertStmt *sql.Stmt
	var insertTx *sql.Tx
	if(db != nil) {
		insertTx, _ = db.Begin()
		insertStmt, _ = db.Prepare("insert into PROPERTIES (client_id, context, name, valeur) values (?, ?, ?, ?)")
	}

	scanner := bufio.NewScanner(file)
	outFile, _ := os.Create(*outFilename)

	writer := bufio.NewWriter(outFile)
	writerWithDecoder := transform.NewWriter(writer, charmap.Windows1252.NewDecoder())
	var lineContent string
	var contents []string
	var insertQuery string

	if(*skipFirstLine) {
		scanner.Scan()
	}

	for scanner.Scan() {
		lineContent = scanner.Text()
		lineContent = strings.Replace(lineContent, "'", "''", -1);
		contents = strings.Split(lineContent, ";")
		if(len(contents[0]) > 0 && len(contents[1])>0) {
			insertQuery = fmt.Sprintf("insert into PROPERTIES (client_id, context, name, valeur) values ('%s', '%s', '%s', '%s')\n", *clientId, *contextClient,
				contents[0], contents[1]);
				//decodeWindows1252(contents[0]), decodeWindows1252(contents[1]));

			writerWithDecoder.Write([]byte(insertQuery));
			if(db != nil) {
				insertStmt.Exec(*clientId, *contextClient, fromWindows1252(contents[0]), fromWindows1252(contents[1]));
			}
		}

	}
	writer.Flush()
	writerWithDecoder.Close()

	if(db != nil) {
		insertTx.Commit();
	}

	if err := scanner.Err(); err != nil {
		log.Fatal(err)
	}
	defer outFile.Close();
}

//func decodeWindows1252(str string) string {
//	sr := strings.NewReader(str)
//	tr := transform.NewReader(sr, charmap.Windows1252.NewDecoder())
//	return string(tr.ReadByte())
//}

func fromWindows1252(str string) string {
	var arr = []byte(str)
	var buf *bytes.Buffer
	if(len(arr) >= 512) {
		buf = bytes.NewBuffer(make([]byte, 512))
	} else {
		buf = bytes.NewBuffer(make([]byte, len(arr)))
	}

	var r rune

	for _, b := range(arr) {
		switch b {
		case 0x80:
			r = 0x20AC
		case 0x82:
			r = 0x201A
		case 0x83:
			r = 0x0192
		case 0x84:
			r = 0x201E
		case 0x85:
			r = 0x2026
		case 0x86:
			r = 0x2020
		case 0x87:
			r = 0x2021
		case 0x88:
			r = 0x02C6
		case 0x89:
			r = 0x2030
		case 0x8A:
			r = 0x0160
		case 0x8B:
			r = 0x2039
		case 0x8C:
			r = 0x0152
		case 0x8E:
			r = 0x017D
		case 0x91:
			r = 0x2018
		case 0x92:
			r = 0x2019
		case 0x93:
			r = 0x201C
		case 0x94:
			r = 0x201D
		case 0x95:
			r = 0x2022
		case 0x96:
			r = 0x2013
		case 0x97:
			r = 0x2014
		case 0x98:
			r = 0x02DC
		case 0x99:
			r = 0x2122
		case 0x9A:
			r = 0x0161
		case 0x9B:
			r = 0x203A
		case 0x9C:
			r = 0x0153
		case 0x9E:
			r = 0x017E
		case 0x9F:
			r = 0x0178
		default:
			r = rune(b)
		}
		buf.WriteRune(r)
	}
	return string(bytes.Trim(buf.Bytes(), "\x00"))
}

func main() {
	flag.Parse()
	var db *sql.DB
	if(len(*mssrv) > 0) {
		db, _ = mssqlConnect();
		if(db == nil) {
			log.Printf("bdd nil");
		} else {
			log.Printf("bdd not nil");
		}
	}
	readFile(db)

	if(len(*mssrv) > 0) {
		defer db.Close()

		rows, _ := db.Query("SELECT nom FROM UTILISATEUR")
		defer  rows.Close()

		for rows.Next() {
			var nom string
			_ = rows.Scan(&nom)
			log.Printf("Nom : "+nom);
		}
	}
}

go get code.google.com/p/odbc
go test -mssrv=DEVSQL,1575 -msdb=DEMO -msuser=user -mspass=pass -v -run=MS

-mssrv=DEVSQL,1575 -msdb=DEMO -msuser=user -mspass=pass -file "mon_fichier.csv" -out "extract.txt" -clientId "OUEST" -contextClient "E"

-skipFirstLine=true