New to Go: SQL to CSV


(John Denny) #24

appreciate it.


(Markus Bolder) #25

I added the log.Println line so I can see the output, and it goes though the 3 db’s but only dumps lines of data from the last db it sees to the csv. so progress thanks.

Try to replace

file, err := os.Create("result.csv")

with

file, err := os.Create(database_name)

(Markus Bolder) #26

“each line had lots of string= comments”

You can get rid of the types before the values (string=… ) by changing the Sprinf
from

a:=fmt.Sprintf(server_name,database_name,recovery_model_desc,last_full_backup,last_diff_backup,last_tran_backup,days_since_full_backup,days_since_diff_backup,hours_since_tranlog_backup,full_backup_location,diff_backup_location,tlog_backup_location)

to

a:= fmt.Sprintf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s" , server_name,database_name,recovery_model_desc,last_full_backup,last_diff_backup,last_tran_backup,days_since_full_backup,days_since_diff_backup,hours_since_tranlog_backup,full_backup_location,diff_backup_location,tlog_backup_location)

(John Denny) #27

Interesting - Create some random text files named after each of the 6 databases seen by the query with differing information in them.

I guess I need to work out how to open the csv leave open , file with the results from all the db scans then close. If that’s possible.


(Markus Bolder) #28

You could make the slice “a” a global variable and then write the file after the loop where you read the db data is finished, at the end of the main function.


(John Denny) #29

If I was a coder yes, but alas I’m a just messing. I think I follow the logic. And again thanks Ill play.


(Markus Bolder) #30

That´s straight forward, you move

var mapofrows []string

to the top, same place where you have the var db

and then move

file, err := os.Create("result.csv")
checkError("Cannot create file", err)
defer file.Close()
writer := csv.NewWriter(file)
defer writer.Flush()
err = writer.Write(mapofrows)
checkError("Cannot write to file", err)

to the end of your code in the main function.
Now the data from all 3 db´s is accumulated in the slice and then saved.


(John Denny) #31

I see, so its known to whole project. I had some success with the sqltocsv module, which I might also try and use use you split with to give me another idea. cheers.

I’ve done that, and it now has got all the data from all the rows.

Only small issue is the excel formatting, each line of data is actual inserted into a cell, so I have 14 cells rather that 14 lines. lol

Assuming I put the code in right area.

// Get values from row.
        err := rows.Scan(&server_name,&database_name,&recovery_model_desc,&last_full_backup,&last_diff_backup,&last_tran_backup,&days_since_full_backup,&days_since_diff_backup,&hours_since_tranlog_backup,&full_backup_location,&diff_backup_location,&tlog_backup_location)
        if err != nil {
            return -1, err
        }
        
		
		a:= fmt.Sprintf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s" , server_name,database_name,recovery_model_desc,last_full_backup,last_diff_backup,last_tran_backup,days_since_full_backup,days_since_diff_backup,hours_since_tranlog_backup,full_backup_location,diff_backup_location,tlog_backup_location)
        mapofrows=append(mapofrows,a)
		
    count++   
	
    
}
	file, err := os.Create("result.csv")
	checkError("Cannot create file", err)
	defer file.Close()
	writer := csv.NewWriter(file)
	defer writer.Flush()
	err = writer.Write(mapofrows)
	checkError("Cannot write to file", err)		

    return count, nil
	

}

func checkError(message string, err error) {
    if err != nil {
        log.Fatal(message, err)
    }
	
}

(John Denny) #32

Is there anyway to control how the csv is built up, as in to populate each row of sql data to a separate row in the csv? rather than it dumping it all into cells on one row.
cheers in advance.


(Yamil Bracho) #33

yes, just do this :

file, err := os.Create(“result.csv”)
checkError(“Cannot create file”, err)
defer file.Close()
writer := csv.NewWriter(file)
defer writer.Flush()

// Get values from row.
err := rows.Scan(&server_name,&database_name,&recovery_model_desc,&last_full_backup,&last_diff_backup,&last_tran_backup,&days_since_full_backup,&days_since_diff_backup,&hours_since_tranlog_backup,&full_backup_location,&diff_backup_location,&tlog_backup_location)
if err != nil {
return -1, err
}
row := make([]string, 0)
row = append(row, server_name)
row = append(row, database_name)
row = append(row, recovery_model_desc)
row = append(row, last_full_backup)
row = append(row, last_diff_backup)
row = append(row, last_tran_backup)
row = append(row, days_since_full_backup)
row = append(row, days_since_diff_backup)
row = append(row, hours_since_tranlog_backup)
row = append(row, full_backup_location)
row = append(row, diff_backup_location)
row = append(row, tlog_backup_location)

err = writer.Write(row)
checkError(“Cannot write to file”, err)

count++

}

return count, nil


(John Denny) #34

Thanks for the advice. If I understand changes after the select as below.

	tsql := fmt.Sprintf(sql)

	file, err := os.Create("result.csv")
    checkError("Cannot create file", err)
    defer file.Close()
    writer := csv.NewWriter(file)
    defer writer.Flush()
   
	 var count int
	 var server_name string
	 var database_name string
	 var recovery_model_desc string
	 var last_full_backup string
	 var last_diff_backup string
	 var last_tran_backup string
	 var days_since_full_backup string
	 var days_since_diff_backup string
	 var hours_since_tranlog_backup string
	 var full_backup_location string
	 var diff_backup_location string
     var tlog_backup_location string


    rows, err := db.QueryContext(ctx, tsql)
    if err != nil {
        return -1, err
    }
    defer rows.Close()
	


    for rows.Next() {
// Get values from row.
err := rows.Scan(&server_name,&database_name,&recovery_model_desc,&last_full_backup,&last_diff_backup,&last_tran_backup,&days_since_full_backup,&days_since_diff_backup,&hours_since_tranlog_backup,&full_backup_location,&diff_backup_location,&tlog_backup_location)
if err != nil {
return -1, err
}
row := make([]string, 0)
row = append(row, server_name)
row = append(row, database_name)
row = append(row, recovery_model_desc)
row = append(row, last_full_backup)
row = append(row, last_diff_backup)
row = append(row, last_tran_backup)
row = append(row, days_since_full_backup)
row = append(row, days_since_diff_backup)
row = append(row, hours_since_tranlog_backup)
row = append(row, full_backup_location)
row = append(row, diff_backup_location)
row = append(row, tlog_backup_location)

fmt.Println(row)

err = writer.Write(row)
checkError("Cannot write to file", err)  
count ++
}
  
err = rows.Err()
if err != nil {
    panic(err)
}

    return count, nil

}

func checkError(message string, err error) {
    if err != nil {
        log.Fatal(message, err)
    }
	
}

(John Denny) #35

The code now creates a CSV that has one line per row from my DB SQL selection. But only for the last of the 3 databases I am iterating through. Is this restriction of the sqltocsv logic or user error? cheers in advance.


(John Denny) #37

Dear all, any more thoughts?