Solved Import spreadsheet command truncating data in long text field

[The Rev]

Greetings,

I have an Access 2016 database that I am importing data from a spreadsheet. Below is the command I am using to do so:

DoCmd.TransferSpreadsheet acUpdate, acSpreadsheetTypeExcel12Xml, "Tbl_Objectives_Import", strSelectedItem, True

Everything works as anticipated except my long text field labeled Validation_Text is getting truncated at 255 characters. The data in the Excel sheet cells are longer than 255 characters. I need all of the data in the field. My table field is long text, rich text formatted, and I removed the @ on the table design. I see that it is a known problem, but none of the suggested methods are feasible (linking to the spreadsheet, adding phantom records, and sorting based on text length). How can I get this thing to just import the whole cell's data?

Thanks

The Rev

 

[theDBguy]

Hi. Yes, this is a common issue. Sometimes it works for some people, but not for others. You might end up with using a workaround to get all your data into Access. As a side note, I am not familiar with acUpdate. I'll have to look it up.

 

[The Rev]

I changed it to

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Tbl_Objectives_Import", strSelectedItem, True

and it behaves the same way.

 

[theDBguy]

I changed it to

DoCmd.TransferSpreadsheet acInsert, acSpreadsheetTypeExcel12Xml, "Tbl_Objectives_Import", strSelectedItem, True

and it behaves the same way.

I am more familiar with acImport... Not sure if it matters in your case though.

 

[The Rev]

So, the weird thing is that I allow a multi select, and I import them one at a time. Some data works and some doesn't. I checked the records, and one was truncated at 255 characters, and another had 435. Calgon...

 

[theDBguy]

So, the weird thing is that I allow a multi select, and I import them one at a time. Some data works and some doesn't. I checked the records, and one was truncated at 255 characters, and another had 435. Calgon...

I know... Like I said earlier, I've seen it work and I've seen it not work. It is weird.

 

[The Rev]

So, it looks like Access looks at the first cell of data in that column below the column header, and if that value is longer than 255 characters, it imports that column as long text, but if the first cell is not, it treats that entire column as short text. That's stupid... I'm looking at how to insert some dummy data in the first row of the spreadsheet after the headers that will force Access to import it as long text...

 

[The Rev]

Well, I am inserting over 255 characters of dummy data in the first line of the spreadsheet when it is created by my export query so it thinks the field is a long text field when it imports into the master copy.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Qry_Export_Assessor_Data_1", ObjFileName, True
'Insert dummy data because Access is stupid
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim lRowCount As Long
Dim myRange As Excel.Range

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(ObjFileName)
Set ws = wb.Sheets("Qry_Export_Assessor_Data_1")
'insert 1 rows at top of sheeet
ws.Range("2:2").Insert xlShiftDown
'set value of a cell
longtext1 = "Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text"
longtext2 = "Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text Dummy Text"
ws.Cells(2, 1).Value = "3.0.0" 'There is no matching value on the import query so this line is ignored
ws.Cells(2, 9).Value = longtext1 & longtext2  'This is the number of the column I need as long text.
wb.Save
Set ws = Nothing
wb.Close False

 

[theDBguy]

So, it looks like Access looks at the first cell of data in that column below the column header, and if that value is longer than 255 characters, it imports that column as long text, but if the first cell is not, it treats that entire column as short text. That's stupid... I'm looking at how to insert some dummy data in the first row of the spreadsheet after the headers that will force Access to import it as long text...

Hi. Actually, I've "heard" it looks at the first 8 rows. Not sure how to verify that though.

 

[Jeff Stuart]

Hi. Actually, I've "heard" it looks at the first 8 rows. Not sure how to verify that though.

Have a look at your ODBC Data Sources definitions for the rows to scan setting.

 

[theDBguy]

Have a look at your ODBC Data Sources definitions for the rows to scan setting.

View attachment 87974

Hi Jeff. Welcome to AWF!

Thanks for sharing this. Cheers!