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.