Using Python (3.9.12 64bit) to read in a Word file of transcribed documents with the objective of getting this information into a prebuilt Access database which I cannot edit.
I'm basically doing this as a favour to a friend who is supporting a team transcribing records for entering into a database. They have Word files that they transcribe into Excel, which is then imported into Access. I offered to create a program to automate the Word to Excel process, using the docx and openpyxl functions. One of the entries is a multiline text field that when imported into Access appears as a continuous string.
I learned that Access uses a combination of Line Return and Carriage Return to put new lines into a single field, so I write the ASCII characters for both at the end of each subline before it is written into Excel.
if writeFullText == '':
writeFullText = subline + chr(10) + chr(13)
else:
writeFullText = writeFullText + subline + chr(10) + chr(13)
...
writeSheet.cell(i+2,26,writeFullText)
This all seems to work when the output is viewed in Excel, but on importing into Access the problem still remains: the Line Return, Carriage Return combo isn't recognised and still produces a continuous string in the appropriate field in the database.
Does anyone have any recommendations on how to create an Excel file using Python that could be loaded into Access without this problem? Thanks for your help.