Import xlsx into New Access table, without strings being truncated (1 Viewer)

[OTWarrior]

I am importing xlsx files via fileopendialog. The Excel file chosen may be different each time in structure.

The code I am using to import is:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
             Replace(strTable, ".xlsx", ""), strPathFile

The issue is when the cell in Excel has a string of more than 255, it gets truncated.

How can I create a new table based on the Excel file selected and import the full text of each cell?

 

[Minty]

If the field type is set to text it can only be 255 character long. Have a read here for some insight http://allenbrowne.com/ser-63.html
And not so helpful https://support.microsoft.com/en-gb...cated-to-255-characters-in-an-access-database Microsoft explanation ...

 

[OTWarrior]

The Microsoft one was actually more useful and explained the following (Posting so this information is on here in case Microsoft remove the article)

When you link an Excel spreadsheet to an Access database, and the columns of the Excel spreadsheet contain more than 255 characters, the columns are mapped to Memo data types, and the Format property of the Memo field is set to @. Therefore, Access treats the Memo field as a text field, and you can view only 255 characters of data.

However, when you import an Excel spreadsheet that has columns that contain more than 255 characters, the columns are mapped to a Memo field with no specific format. Therefore, you can view the complete data in the field.

I am not setting a field type, only creating the table.

How do I set the fields to be the memo type (or to accept a longer string) when importing an xlsx file?

 

[sxschech]

Some posts I read indicated that you need to have data longer than 255 on the first data row in the cells you intend to import as long text. Unfortunately, haven't figured out how to automate this step because the data I get doesn't always have the same positions (it is an extract that has been formatted from a json file) and since I have to manually convert the json file with an online tool, have been copying an pasting a dummy text string containing over 255 chars. Then when the file is imported, it doesn't truncate.

 

[OTWarrior]

Some posts I read indicated that you need to have data longer than 255 on the first data row in the cells you intend to import as long text. Unfortunately, haven't figured out how to automate this step because the data I get doesn't always have the same positions (it is an extract that has been formatted from a json file) and since I have to manually convert the json file with an online tool, have been copying an pasting a dummy text string containing over 255 chars. Then when the file is imported, it doesn't truncate.

I have been seen this mentioned a few times now, this does appear to work. it is a pain to have to put an extra row in the excel file though.

I have discovered the issue isn't when importing, but the query I am outputting. Basically, if you group fields in a query, memo fields are truncated. (stupid blooming thing, Access should have a warning or something about this)

 

[sxschech]

Glad you figured it out. Unfortunately, there are quite a few things access doesn't tell you about, or if it tells you, it is so vague that you can't (easily) track down the issue. In case you hadn't seen this, Allen Browne has an article about truncation of memo fields and exporting. http://allenbrowne.com/ser-63.html