Import From Excel - Cutting Off At 255 Characters 1

[DataChick]

I am importing from an Excel document into a memo field in Access and it's cutting off the data at 255 characters even though the Excel cells contain more than 255 characters.

Is there a way to get all of the characters (even more than 255) to load in Access?

Thank you in advance for your help.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[Remou]

What version are you using? Is it 97? I ask, because it should be possible to import a spreadsheet with cells containing more than 255 characters since the '97 version. You may need to update Jet.

 

[DataChick]

I'm using Access 2003 and I thought that it was able to do it but it's not.

The import is happening through an event (using VBA). I don't know if that makes a difference.

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[Remou]

This works for me in Access 2K

[tt]strXLS = "C:\Docs\Memo.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblXLSMemo", strXLS, True[/tt]

Where tblXLSMemo has the following fields:

[tt]ID: Number
LongText: Memo
ShortText: Text[/tt]

The field names are also the titles of columns in Memo.xls.

Have you tried a stripped down test in a scratch database?

 

[lameid]

Excel is a headache when it comes to datatypes. The ISAM is probably thinking the field is text which has a limit of 255 characters.

I have a possible solution for you and a stand by that should definitely work.

First ensure that a field containing more than 255 characters is the first row in the spreadsheet (anywhere int he top 15 would probably work) and try importing again. In my experience putting text in a column that contains what looks like numeric data will force the datatype as text so I am hoping that somehthing similar happens for memo's.

If that does not work, export the data to text and then import it. Text imports are much friendlier, just remember to hit the advanced button.

 

[DataChick]

Thank you Remou!!! That worked perfectly! The main issue is that not every record contains more than 255 characters in one cell and we are importing from a form so I can't sort it before it comes in. Your code pulls it in whether it's the first record or the 100th record!

Thank you again!!!!

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein