Using Datatable in Go language – insert data into postgresSQL tables

Question

I have below file, number of rows might change to any numbers

Sample .text File

1019893476200 02

5200ABCD company

6218034762001234

799R022408200000

6211934762001134

799R122408200000

8200000002008934

5200ABCD company

6218934762009994

799R042408200000

8200000002008934

9000004000002000

Here

row starts with 1 represents file header,

row starts with 5 represents batch header,

row starts with 6 represents details record,

row starts with 7 represents additional record,

row starts with 8 represents batch trailer record,

row starts with 9 represents file trailer record (this represens end of file and it contains complete file info)

I have below tables with the column as follows

Table 1 - it stores complete file as different rows

FileId(Primary) FileHeader BatchHeader BatchTrailer FileTrailer

1 1019893476200 5200ABCD company 8200000002008934 9000004000002000

2 1019893476200 5200ABCD company 8200000002008934 9000004000002000

Table 2 - it stors row starts with 6 and 7

Detailed(PK) FileId(FK) Number1 Account Code

1 1 03476 0012 R02

2 1 93476 0011 R12

3 2 93476 0099 R04

Note : Number1 - can be find in row starts with 6 - data position starts with 5 and length 5 (example - 03476)

Account -can be find in row starts with 6 - data position starts with 11 and length 4(example - 0012)

code - can be find in row start 7 - data position starts with 4 and length 3 (example - R02)

Question : Using datatable in go llanguage , how do i parse the entire file and how do i populate data in datatable in Go and then pass the same datatable to postgresSQL and insert into tables

Kumar AU · Answer

Hello Jayraj, Thank you so much for the response and your time which you have spent on this.

Actually as you seen in my original question, Table 1 and Table 2 will have foreign key relation (FileId column) which needs to be addressed And File contents can be more than 100K records , in this situtation , I am thinking to use DataTable and stored proc in PostresSQL for better formance while reading the entore file.

As posted in my original question, could you please let me know how do i achive inserting data to postgres tables using DataTable in Go and also Datatable in postgresSQL. either direct inline query in GO lang or using stored procedure

Jayraj Chhaya · Answer

Hi Kumar AU,

To parse the provided text file and populate the data into PostgreSQL using Go, you can follow these steps:

Read the File: Use the os and bufio packages to read the file line by line.
Parse the Data: Identify the row types based on the starting character and extract the necessary fields.
Store in Data Structures: Use slices or structs to hold the parsed data temporarily.
Insert into PostgreSQL: Utilize the database/sql package along with a PostgreSQL driver (like lib/pq) to insert the data into the respective tables.

package main

import (
    "bufio"
    "database/sql"
    "fmt"
    "os"
    _ "github.com/lib/pq"
)

type FileRecord struct {
    FileHeader     string
    BatchHeader    string
    BatchTrailer   string
    FileTrailer    string
}

type DetailRecord struct {
    FileId   int
    Number1  string
    Account  string
    Code     string
}

func main() {
    // Open the file
    file, err := os.Open("data.txt")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer file.Close()

    var fileRecords []FileRecord
    var detailRecords []DetailRecord
    var fileId int

    scanner := bufio.NewScanner(file)
    for scanner.Scan() {
        line := scanner.Text()
        switch line[0] {
        case '1':
            // Parse file header
            fileRecords = append(fileRecords, FileRecord{FileHeader: line})
        case '5':
            // Parse batch header
            fileRecords[fileId].BatchHeader = line
        case '6':
            // Parse detail record
            detailRecords = append(detailRecords, DetailRecord{
                FileId:  fileId,
                Number1: line[5:10],
                Account: line[11:15],
            })
        case '7':
            // Parse additional record
            detailRecords[len(detailRecords)-1].Code = line[4:7]
        case '8':
            // Parse batch trailer
            fileRecords[fileId].BatchTrailer = line
            fileId++
        case '9':
            // Parse file trailer
            fileRecords[fileId-1].FileTrailer = line
        }
    }

    // Insert into PostgreSQL
    db, err := sql.Open("postgres", "user=username dbname=mydb sslmode=disable")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    for _, record := range fileRecords {
        _, err := db.Exec("INSERT INTO table1 (FileHeader, BatchHeader, BatchTrailer, FileTrailer) VALUES ($1, $2, $3, $4)", 
            record.FileHeader, record.BatchHeader, record.BatchTrailer, record.FileTrailer)
        if err != nil {
            fmt.Println(err)
        }
    }

    for _, detail := range detailRecords {
        _, err := db.Exec("INSERT INTO table2 (FileId, Number1, Account, Code) VALUES ($1, $2, $3, $4)", 
            detail.FileId, detail.Number1, detail.Account, detail.Code)
        if err != nil {
            fmt.Println(err)
        }
    }
}

This code provides a basic structure for reading the file, parsing the data, and inserting it into PostgreSQL. Adjust the database connection string and table names as necessary for your specific use case.

Using Datatable in Go language – insert data into postgresSQL tables

Kumar AU

Jayraj Chhaya