Last year, I accessed the closed beta for ChatGPT. I tried it once, only to come back a few weeks later to find that all my free tokens had expired. With their latest release, I decided to give it another shot.
I came up with a basic use case after talking to a friend of mine in the legal industry. He had to send out similar documents to each of his clients, which was a tedious process if you’re not familiar with the “Merge Mail” function in Microsoft Word.
Here’s the idea: make it as easy as dropping an Excel sheet with customer data and a Word template into a service that would identify a generic template and duplicate it for each line of the Excel sheet (one line per customer).
Goals
- Extract data from an Excel and a Word document
- Experiment with ChatGPT’s semantic capabilities
- Experiment with ChatGPT’s front-end development capabilities
- Successfully match key data from an Excel sheet to a Word document and generate a PDF template.
Process
- Get the data out of the Excel
We made a mock Excel file with random data. The header fileld with data such as the status of the company, its name, its address, its shares number.
I then used the library excelize to extract the header and the first line.
func ExtractExcelData(path string) ([]string, []string) {
f, err := excelize.OpenFile(path)
if err != nil {
fmt.Println(err)
return nil, nil
}
defer func() {
// Close the spreadsheet.
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
// Get all the rows in the first sheet.
rows, err := f.GetRows("Feuil1")
if err != nil {
fmt.Println(err)
return nil, nil
}
if len(rows) > 1 {
return rows[0], rows[1]
}
return nil, nil
}
- Get the data out of the Word
Just as we did with the Excel file, we need to be able to extract the content of the Word document as well, as it is the template that the user wants to see duplicated for every row of their Excel sheet.
Initially, I considered a raw solution, which would involve simply extracting the raw text from the document. However, since the final production should be identical to the original Word document, we may need to retain the formatting when processing the document at some point in the project life. Therefore, I looked for a library that would allow me to preserve the formatting, if needed. After some research, I chose the ‘unioffice’ library. While it requires an API key, they do offer a free tier for testing purposes.
func ExtractTextFromWordDocument(path string) string {
doc, err := document.Open(path)
if err != nil {
panic(err)
}
// To extract the text and work with the formatted info in a simple fashion, you can use:
extracted := doc.ExtractText()
return extracted.Text()
}
- Build a ChatGPT prompt with the data
The trickiest part is getting the right prompt for ChatGPT. We settled on the following one (in french 🇫🇷):
Voici les intitulés de colonnes de mon excel : __header__. A partir de ces colonnes, j'aimerais identifier les valeurs auxquelles elles correspondent dans le document que je vais mettre juste après. Le but étant de les variabiliser pour générer un document similaire avec d'autres valeurs. Voici un exemple de la première ligne avec les exemples de valeur séparées par un point virgule pour chacune des colonnes : __firstExcelRow__. Elles devraient être similaires dans le template et j'aimerais donc que tu me proposes un template variabilisé avec uniquement mes intitulés de colonnes comme noms de variables. Je veux uniquement le template dans ta réponse. Voici ci-desous le texte du template :
To this text we merged the Excel heade through the __ header __ and the __ firstExcelRow __ tags as well as the Word document text at the end.
- Get the resulting template in a PDF
Once ChatGPT proceeded, I saved the result to a PDF which will be displayed to the user.
func ProcessFiles(excelFilePath string, wordFilePath string, ChatGPTPrompt string, uniqueID string) (string, error) {
log.Println("Launched processing..")
// docx to text
initialTemplateContent := models.ExtractTextFromWordDocument(wordFilePath)
// xlsx header to text + first line
headerList, firstLine := models.ExtractExcelHeader(excelFilePath)
//fmt.Println(headerList, firstLine)
//Add header to ChatGPTPrompt
ChatGPTPrompt = strings.Replace(ChatGPTPrompt, "__header__", strings.Join(headerList, ";"), -1)
//Add first excel line to ChatGPTPrompt
ChatGPTPrompt = strings.Replace(ChatGPTPrompt, "__excelFirstRow__", strings.Join(firstLine, ";"), -1)
//ChatGPT prompt creation
template := ChatGPT(ChatGPTPrompt, initialTemplateContent)
// Convert the text to a []rune
runeSlice := []rune(template)
// Create a new bytes.Buffer
buffer := bytes.Buffer{}
// Iterate over the []rune and convert to Windows-1252
for _, r := range runeSlice {
if utf8.ValidRune(r) {
buffer.WriteRune(r)
} else {
buffer.WriteRune(utf8.RuneError)
}
}
// Convert the bytes.Buffer to []byte
byteSlice := buffer.Bytes()
// Convert the []byte to a string in Windows-1252 encoding
str := string(windows1252EncodedBytes(byteSlice))
// Create a new PDF
pdf := gofpdf.New("P", "mm", "A4", "")
// Add a new page to the PDF
pdf.AddPage()
pdf.SetFont("Times", "", 12)
// Write the text to the PDF
pdf.MultiCell(0, 10, str, "", "", false)
// Output to a file
err := pdf.OutputFileAndClose("/tmp/"+uniqueID+"-template.pdf")
if err != nil {
log.Println(err)
return ChatGPTPrompt, err
}
The folder needs to be tmp as its the default folder allowing temporary writing operations in google cloud app engine.
- Build a basic UI allowing for testing the prompt for a non-technical user
With the entire processing chain set up, all that was left was to create a front-end that would allow my friend to test it out, upload documents, and modify the prompt on the fly.
Using Chat GPT, I developed a user interface by building it block by block. In the end, I had two upload buttons and a dynamic list of the files I had uploaded, all thanks to some JavaScript generated by Chat GPT. I made some minor tweaks to the design, such as adjusting the colors and adding a bit of code, but nothing too fancy.
- Generate the documents for each customer
The final step would be to apply our generated template to every row in our Excel sheet. I did not carry out this last step, as it is quite straightforward to implement. All I would have had to do was loop through each row of the Excel sheet. The trickiest part would have been on the front-end side, but I’m confident that ChatGPT would have been able to handle it well.
Results
Quite satisfying and impressive from ChatGPT I must say.
The front-end looks nice for a basic product. I even add the possibility to delete the files I added before submitting them. Something that would have taken me some time to do by myself as I don’t know much about Javascript and how HTML form data can be tricly to play with.
On the processing side, ChatGPT was able to generate a template from the data I extracted from the Excel and Word documents. It correctly identified which data from my Excel sheet was to be used in my Word documents, and how it should be used. It did struggle with some content, such as number strings with attached units such as “100 (cent).” It recognized the number “100” but omitted the “cent” from processing.
Review
These files were pre-formatted. In the real world, some users would input Excel with pretty wild structures, no headers and mixed data fields. This alone would require some heavy pre processing.
On the Word template side, certain structures such as tables or lists can be problematic to identify, as they are not parsed as such. It’s also difficult for ChatGPT to differentiate between constant data and variable data. For some inflexible template data, ChatGPT creates variables which may not fully represent the intended data. This is especially noticeable in the introduction of the template, which is often a summary.
Overall, it was enjoyable to work with ChatGPT, and I could see this tool being deployed in many companies. Data matching is a critical issue in some projects, and ChatGPT was able to handle it quite well in my opinion. The user experience was way better than what Microsoft is currently offering with “Mail Merge.”
I will conclude this blog with a legal issue, as it is also a significant concern. Many companies would place a heavy focus on the data collection issues. In this proof-of-concept, we have none, as the files are automatically deleted after processing. However, it would be a significant undertaking to sell this service without addressing data collection and privacy concerns.
P.S : As of june 2023, I feel like the prompt is not as good as it was. I was using GPT-3.5 but it has probably been updated. Feel free to clone the project and give it a try.