importing data from excel that exceeds text field size limit

[Oak N.]

Hi there,
I'm trying to import a spreadsheet that contains a
column of text where individual cells sometimes exceed
255 characters. Every time I import the spreadsheet,
access makes that column field a text field and truncates
the text at 255. I've tried exporting to a tab delimited
file and importing it that way where I can specify the
field type but it still truncated the cells.
I've tried doing a linked table, but it still truncates
the text and won't let me change the field type to memo.
Can anyone tell me how to get this to import correctly?

Thanks,

Oak

 

[Ken Snell]

Try using TransferText (in VBA code or in macro). Doing the "Get External
Data" route manually will usually truncate fields' length to 255 characters.

If that fails, then you can use Automation to open the EXCEL file and read
each row into a recordset, one row at a time.

 

[John Nurick]

Hi Oak,

Try one of the following:

1) Ensure that at least one of the first few records contains more than
255 characters in this column. Access decides the field types to use by
examining the first dozen or so records.

2) Create a table manually in Access, using the exact same field names
as in Excel and the field types you want. Then import the data into that
table.

 

[Oak N.]

After doing I don't know how many attempts, I finally
figured out what you said in your first suggestion
below. I actually tried the second one earlier but it
didn't work. Thanks.

-----Original Message-----
Hi Oak,

Try one of the following:

1) Ensure that at least one of the first few records contains more than
255 characters in this column. Access decides the field types to use by
examining the first dozen or so records.

2) Create a table manually in Access, using the exact same field names
as in Excel and the field types you want. Then import the data into that
table.

Hi there,
I'm trying to import a spreadsheet that contains a
column of text where individual cells sometimes exceed
255 characters. Every time I import the spreadsheet,
access makes that column field a text field and truncates
the text at 255. I've tried exporting to a tab delimited
file and importing it that way where I can specify the
field type but it still truncated the cells.
I've tried doing a linked table, but it still truncates
the text and won't let me change the field type to memo.
Can anyone tell me how to get this to import correctly?

Thanks,

Oak

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.