Map[] to tsv (tab separated file)

[Sibert]

I have got data as a map:

[
map[user_id:3 user_name:John Doe] 
map[user_id:23 user_name:Donald Duck] 
map[user_id:24 user_name:Jane Doe]
]

I want it to be a simple .tsv file (tab separated) like this:

user_id   -> user_name
3         -> John Doe 
23        -> Donald Duck 
24        -> Jane Doe

func main() {
	Connect()
	c := cron.New()
	c.AddFunc("@every 30s", SendMail)
	c.Start()
	time.Sleep(time.Duration(1<<63 - 1))
}

func SendMail() {
	data := getall("SELECT * FROM usr")
	t := time.Now()
	t.String()

	file := "test_" + t.Format("2006-01-02T15-04-05.000Z") + ".tsv"
	f, err := os.Create(file)

	fmt.Println(file)

	if err != nil {
		fmt.Println(err)
	}
	defer f.Close()
	_, err2 := f.WriteString(data)

}

Is this possible?

[mje (Jeff Emanuel)]

  for k,v := range userMap {
    fmt.Fprintf(f,"%d\t%s\n",k,v)
  }

[Sibert]

Thank you, but I do not quite understand:

for key, val := range data {
	fmt.Fprintf(f, "%d\t%s\n", key, val)
}

Error:

cannot range over data (type interface {})

What am I doing wrong? How do I get the userMap from data?

[ljh]

Hi Sibert,

What is the return type of the function getall("SELECT * FROM usr") ? You can check it like this:

fmt.Printf("%T\n", getall("SELECT * FROM usr"))

[mje (Jeff Emanuel)]

I assumed you “got data as a map” and wrote the example assuming userMap to be a map[int]string. Adjust the example accordingly for your actual types.

[Sibert]

@ljh

What is the return type of the function getall("SELECT * FROM usr") ?

[]map[string]interface{}

[Sibert]

@mje

assumed you “got data as a map”

data := getall("SELECT * FROM usr") returns

[
map[user_id:3 user_name:John Doe]
map[user_id:23 user_name:Donald Duck]
map[user_id:24 user_name:Jane Doe]
]

[mje (Jeff Emanuel)]

You can work with data directly. Loop over the elements of data via for ... := range data {. Each element is a map[string]interface{}. From your printout you know the two keys in that map are “user_id” and “user_name”. Print the values for those keys, separated by a tab ‘\t’.

for _, entryMap := range data {
  fmt.Fprintf(f, "%d\t%v\n", entryMap["user_id"], entryMap["user_name"])
}

[ljh]

That is a slice of map. Every element in slice is type of map.

[Dean_Davidson]

@Sibert

[]map[string]interface{}

OK so stubbing out your data I can get it working just fine with @mje’s supplied example code:

package main

import (
	"fmt"
	"os"
	"time"
)

func main() {
	SendMail()
}

// Simulate db call
func getall(sql string) []map[string]interface{} {
	return []map[string]interface{}{
		{"user_id": 3, "user_name": "John Doe"},
		{"user_id": 23, "user_name": "Donald Duck"},
		{"user_id": 24, "user_name": "Jane Doe"},
	}
}

// SendMail with some of the superfluous lines removed
func SendMail() {
	data := getall("SELECT * FROM usr")
	t := time.Now()

	fileName := "test_" + t.Format("2006-01-02T15-04-05.000Z") + ".tsv"
	f, err := os.Create(fileName)
	if err != nil {
		fmt.Println(err)
	}
	defer f.Close()
	// Print headers
	fmt.Fprint(f, "user_id\tuser_name\n")
	// Print each record
	for _, entryMap := range data {
		fmt.Fprintf(f, "%d\t%v\n", entryMap["user_id"], entryMap["user_name"])
	}
}

I got a file with the following:

user_id	user_name
3	John Doe
23	Donald Duck
24	Jane Doe

What problem are you running into? Also worth noting that you can use encoding/csv to write TSV as well. Both reader and writer support changing the comma rune. It’s just set to ‘,’ by default:

// Comma is the field delimiter.
// It is set to comma (',') by NewReader.
// Comma must be a valid rune and must not be \r, \n,
// or the Unicode replacement character (0xFFFD).
Comma rune

Here’s an example if you want to pursue that.

[ljh]

Yes, also the separator in CSV can be space.

[mje (Jeff Emanuel)]

@Dean_Davidson

you can use encoding/csv to write TSV

Of course this will also be more robust for quoting when the values contain the separator character and for proper handling of the quote character contained within quoted values.

[Sibert]

@Dean_Davidson

stubbing out your data I can get it working just fine

Yes, that is one way to do this. Thank you!

But my intention is to be generic. A newbie lame attempt to do this is here
https://play.golang.com/p/wNDDSiMcKJk

Far from perfect, but at least show what I am looking for.

package main

import (
	"fmt"
)

func main() {
	data := getall("SELECT * FROM usr")
	tsv := map2csv(data)
	fmt.Println(tsv)

}

// Simulate db call
func getall(sql string) []map[string]interface{} {
	return []map[string]interface{}{
		{"user_id": 3, "user_name": "John Doe"},
		{"user_id": 23, "user_name": "Donald Duck"},
		{"user_id": 24, "user_name": "Jane Doe"},
	}
}

func map2csv(data []map[string]interface{}) (tsv []interface{}) {

	cols := make(map[string]struct{})
	for _, record := range data {
		for key, _ := range record {
			cols[key] = struct{}{}
		}
	}

	keys := []string{}
	for key := range cols {
		keys = append(keys, key)
	}

	fmt.Println(keys)

	var vals []interface{}

	for _, row := range data {
		for _, val := range row {
			vals = append(vals, val)
		}
	}

	return vals

}

Is this possible? Is this possible using encoding/csv?

[mje (Jeff Emanuel)]

@Sibert

keys := []string{}

Incorrect {}

@Sibert

	var vals []interface{}

	for _, row := range data {
		for _, val := range row {
			vals = append(vals, val)
		}
	}

This looks like a flat slice of all values in data. I don’t think that’s what you want.

Furthermore, you can’t count on the iteration over row to be the same order from row-to-row or to match your cols. It’s better to iterate over your keys slice to get consistent ordering. It may or may not be relevant, but you might need to account for missing values for a column in a row.

[Dean_Davidson]

I would first caution you that []map[string]interface{} is possibly not ideal for the data you’re trying to represent. But, if you wanted a generic TSV function, here’s a naive implementation:

// writeTSV writes tab-separated data to w. It doesn't support jagged maps, so
// all maps must contain the same keys. It also doesn't support escaped tabs so
// it is up to caller to sanitize data.
func writeTSV(w io.Writer, items []map[string]interface{}) {
	// Empty data
	if len(items) == 0 {
		fmt.Fprint(w, "Empty dataset")
		return
	}

	var headers = make([]string, len(items[0]))
	i := 0
	// Iterate over first item in our array to get headers (AKA keys)
	// These will not be in any specific order. See also:
	// https://stackoverflow.com/questions/9619479/go-what-determines-the-iteration-order-for-map-keys
	for key := range items[0] {
		headers[i] = key
		i++
	}
	// Since not in specific order, sort columns to make this predictable.
	sort.Strings(headers)
	// Print our header
	fmt.Fprintln(w, strings.Join(headers, "\t"))

	// Iterate over our maps and populate each row with data
	for _, row := range items {
		for i, column := range headers {
			// Print our row value. This is the part that would break
			// with jagged maps. TODO: support jagged maps?
			fmt.Fprint(w, row[column])
			// For the last column, we print newline
			if i+1 == len(headers) {
				fmt.Fprint(w, "\n")
			} else {
				// Otherwise, print our delimiter (tab in this case).
				fmt.Fprint(w, "\t")
			}
		}
	}
}

And to use it:

func SendMail() {
	data := getall("SELECT * FROM usr")

	t := time.Now()

	fileName := "test_" + t.Format("2006-01-02T15-04-05.000Z") + ".tsv"
	f, err := os.Create(fileName)
	if err != nil {
		fmt.Println(err)
	}
	defer f.Close()
	writeTSV(f, data)
}

Putting it together here’s a go playground link that writes to os.stdout:

https://play.golang.com/p/-Qsx6igk8-E

Anyway, something like that should get you closer to what you’re trying to accomplish. Hopefully.

[Sibert]

@Dean_Davidson

But, if you wanted a generic TSV function, here’s a naive implementation:

Thank you! That is almost exact what I asked for. The only thing I found is that the tab is not added correct in the header:

fmt.Fprintln(w, strings.Join(headers, "\t"))

There is a tab missing after tsk_desc (space instead of tab) tsk_id:

Is there a way to replace the space with the missing tab?

@Dean_Davidson

I would first caution you that []map[string]interface{} is possibly not ideal for the data you’re trying to represent.

Is there any better way to do this that is generic?

[mje (Jeff Emanuel)]

@Sibert

There is a tab missing after tsk_desc (space instead of tab) tsk_id

You almost certainly have "tsk_desc tsk_id" as keys in your maps and "Desc 176 176" etc as values. Check your map creation.

You can see clearly that strings.Join indeed puts a tab between every field here:

Go Playground - The Go Programming Language

[Sibert]

@mje

You can see clearly that strings.Join indeed puts a tab between every field here:

Yes, I found that this worked. But how do I do with “multiline” fields?

They appear as a separate rows.

[Dean_Davidson]

I noted that the implementation was naïve and didn’t support escaped characters. The solution in this case is probably to use encoding/csv as I also mentioned above. First, refactor our test data to include problematic test cases:

func getall(sql string) []map[string]interface{} {
	return []map[string]interface{}{
		{"user_id": 3, "user_name": "John Doe"},
		{"user_id": 23, "user_name": "Donald Duck"},
		{"user_id": 24, "user_name": "Jane tabbed\t Doe"}, // Test tabs
		{"user_id": 33, "user_name": `problematic
		name
		with
		crlf`}, // Test crlf
	}
}

Then refactor writeTSV to use encoding/csv:

// writeTSV writes tab-separated data to w. It doesn't support jagged maps, so
// all maps must contain the same keys.
func writeTSV(w io.Writer, items []map[string]interface{}) {
	// Empty data
	if len(items) == 0 {
		fmt.Fprint(w, "Empty dataset")
		return
	}

	// Create a new CSV writer
	encoder := csv.NewWriter(w)
	// Set our delimiter to tab
	encoder.Comma = '\t'

	var headers = make([]string, len(items[0]))
	i := 0
	// Iterate over first item in our array to get headers (AKA keys)
	// These will not be in any specific order. See also:
	// https://stackoverflow.com/questions/9619479/go-what-determines-the-iteration-order-for-map-keys
	for key := range items[0] {
		headers[i] = key
		i++
	}
	// Since not in specific order, sort columns to make this predictable.
	sort.Strings(headers)
	// Print our header
	encoder.Write(headers)

	// Create array to store row data. We COULD re-use headers here but for
	// readability I'm going to create a new variable.
	var rowValues = make([]string, len(headers))

	// Iterate over our maps and populate each row with data
	for _, row := range items {
		// For each row, build up our rowValues and then write to our encoder
		for i, column := range headers {
			// This is the part that would break with jagged maps.
			// TODO: support jagged maps?
			rowValues[i] = fmt.Sprint(row[column])
		}
		encoder.Write(rowValues)
	}

	// Write any buffered data to the underlying writer
	encoder.Flush()
}

And putting it all together yields the following properly escaped output:

user_id user_name
3       John Doe
23      Donald Duck
24      "Jane tabbed     Doe"
33      "problematic
                name
                with
                crlf"

Try it yourself here:

https://play.golang.com/p/b70UUDrKsWK

It will also handle escaping other problematic characters (what happens if your string contains "?).