Data is truncated when it is imported from Excel

[contour]

I did import a excel worksheet (one tab) to access. The worksheet contains
200 rows and 6 columns. Some cells contain a string of around 568 characters
(including text, number and spaces). The data in those cells is truncated
when it is imported into access.
How do we solve this problem? Is there any workaround?

 

[BruceM]

Check Help for "Import, export, and link data between Access and Excel". In
particular, look at "things you should know before importing...". As I
understand it, Access scans the first 25 rows of the spreadsheet and decides
the data type. If none of the cells in the first 25 rows of a particular
column contain more than 255 characters, Access will decide that it is a
Text field. If you move a row with a long string to a row above the 25th
row, Access should interpret the field as being of the Memo data type, and
the data will not be truncated. You will need to account for every column
with a long string. For instance, if Column A contains a long string in the
first row, and Column G contains a long string in the 40th row, ColumnG will
be truncated. The value in Column A will not affect Column G. You need to
move the 40th row above the 25th row in order for Column G to work out
properly.

 

[contour]

BruceM - I did place a set of "dummy" data which has the same format and size
of the real data in the first 8 rows of the speadsheet. However, after
importing some sets of data were classified as Text and some were classified
as Memo by Access; and data in those cells was still truncated.
The funny thing here is the set of data, which was classified as Memo, was
also truncated!!!!

 

[Ken Snell \(MVP\)]

If you used the File | Get External Data... | Import method, that will
truncate data. It uses "old" EXCEL format for the import.

Use a macro or VBA to run the TransferSpreadsheet action to import the data.