Data is truncated when it is imported from Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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 said:
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?
Click to expand...

Yea, it normally would truncate it at 255 characters. I believe you can
get around this by predefining the filed (one or more of your six columns)
as a memo field. Memo field types will have no problem with 568 characters.
I don't recall if you need do any other special things to get it to work, I
have never needed to do that. I have always been in a position to tell my
users that I don't want them writing that much in any of the fields they
had. :-)
 
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!!!!
 
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.
 
Back
Top