Importing Excel Problem

[Martyh]

Hi,

I have a problem when importing excel when the cell is longer than 255 char. The cells are 300-1000 chars long. (Verified by doing a len().)

The problem is that when it is imported into Access --> appended to the table it is truncated at 255 char even though it is a memo field that it is going to. (Verified by doing len() at the other end)

Is there a work around, or am I missing something? Does anyone else have this problem?

Regards,

Marty

PS When I create a new table it seems to work fine. It is only when I append to a table!!

 

[The_Doc_Man]

I believe you are running into the fact that the Access "import wizard" is dumber than a box of rocks and won't exceed 255 characters because a "MEMO" field isn't really one of its preferred data types.

I might try to do that particular import by writing a VBA routine that opens the Excel Object, opens your workbook, activates your worksheet, and loops through the collection of Rows. For each row, you would then try to directly copy the cell contents into a specifically named field with the right properties, which means you would need a pre-defined table with as many MEMO fields as you need for the operation.

I'm not going to swear that this would work either - but if the import wizard won't touch it and a direct application operation won't work either, then you can't get there from here.

 

[GohDiamond]

What version of Access are you using? If 2007 or better is your dB mdb or accdb? I just tried it in MS Access 2013 accdb and imported a string of data that is 500 characters/bytes, in two ways, once with the wizard making sure the Column type was set to LONG TEXT and then again in VBA using docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12Xml,"A_TESTMEMO",....

Both worked as expected, all 500 characters were imported to a New table.

I also appended data to an existing table with the field set to long text and had no problem with either the Wizard or the VBA import.

Cheers!
Goh

 

[JHB]

..When I create a new table it seems to work fine. It is only when I append to a table!!

Then import it into a new table, and then run an append query.

 

[Martyh]

What version of Access are you using?

It version 14.0.7165.5 (32 bit) with the latest version Windows 7

Did you have the memo length items in the first 25-30 rows? While this is good if you have control over what comes in, in Excel, I do not have the same control. I bet if you placed the Memo length items over 30 rows from the beginning of Excel, then you will get the same result as I did.

If I place something of memo length within 1-30 rows from the beginning then I too will get it to "realize" that it is a memo.

See suggestion I discovered by Ken Snell, Microsoft MVP. He had 2 options really but I proved the first one to be incorrect (at least for my versions). He say : 1) Insert a dummy row of data as the first row, where the dummy row contains a text string longer than 255 characters in the cell in that column -- that should let Jet (ACCESS) treat that column's values as memo and not text.

more info at: http://www.accessmvp.com/kdsnell/EXCEL_Import.htm#DataTypeErr

Thanks all of you guys for your attention to this problem. I really am beginning to understand "The_Doc_Man"'s view that it is dumber than ...

Marty

 

[The_Doc_Man]

Marty, I have to admit that I had not tried the import to a Memo table in a recent version of Access, but in general if you want Access to import correctly, NEVER import it directly to a new table. Always import it by APPENDING to an exist table already in the right format, and be sure that the table's columns match the spreadsheet's columns. There is nothing wrong with keeping an empty table around as the intermediate for this import process, and using a second query to populate any other tables based on the contents of the intermediate table after importation. You can erase that table when done.

The idea here is that if you pre-identify the desired format of the destination field by having designed the table ahead of time, the wizard doesn't have to decide and therefore CANNOT make the wrong decision (about that factor, but there are always other ways to fail...). In essence, the wizard CAN'T override the table format, so if your long field aligns with the long-text column in your spreadsheet, it will do the right thing.