New to Go: SQL to CSV

John_Denny · December 28, 2018, 12:30pm

appreciate it.

Locust · December 28, 2018, 12:48pm

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)

Locust · December 28, 2018, 1:05pm

“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 · December 28, 2018, 3:33pm

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.

Locust · December 28, 2018, 3:39pm

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 · December 28, 2018, 3:45pm

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

Locust · December 28, 2018, 3:52pm

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 · December 28, 2018, 3:55pm

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 · January 2, 2019, 3:03pm

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 · January 2, 2019, 5:54pm

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 · January 2, 2019, 6:25pm

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 · January 2, 2019, 9:02pm

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 · January 8, 2019, 2:01pm

Dear all, any more thoughts?

system · April 8, 2019, 2:01pm

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.