Import from Excel to Access - truncates

[DHudson]

I'm doing a manual import into Access (2010) from Excel, changing the field to a "memo" field, which is supposed to be practically unlimited. However, when I import - field truncates at 255 characters. I need for it to import all the characters but have run out of ideas.

 

[CazB]

Have you tried... importing the spreadsheet as a table, then deleting all the data, changing the field types to what you want them to be, and then importing the spreadsheet again into that table (rather than importing as a new one) ?

I had the same problem and that approach sorted it for me

 

[DHudson]

Just tried - what do you mean by "delete the data" ? go to each cell and erase the data? I tried what you stated but didn't delete the data - didn't work - field getting truncated didn't come over at all
:banghead:

 

[Frothingslosh]

What he meant was this:

1. Import the spreadsheet into a table.
2. Run a delete query and delete every record in the table.
3. Open the table in design view.
4. For every field, change the properties so that the field type is what you need it to be (be it date/time, text, memo, whatever)
5. Save your changes.
6. Re-import your spreadsheet to the same table.

This should fix your issue, and you can re-use the table in the future just by deleting all the records in it, then re-importing to that table again. Just make sure to compact and repair (and backup!) regularly.

The problem you're running into is that unless you import your spreadsheet into a pre-existing table, Access determines what type of field EVERY field is based on the data in the first 10 records. If your would-be memo field has less than 256 characters in all ten of those records, then the field is imported as regular text.

 

[DHudson]

Thanks - Did each step - still truncating.

I had already tried setting up the first 5 rows with >255 character fields before inputting. States it is a memo field, but after import, still have truncated data. I even did a manual import where you can set the field, before import, to a memo field -- that didn't work either.

Thanks for your help - let me know if you find a solution.

 

[DHudson]

from researching blogs looks like I'm going to have the same issue when exporting...

I'm just splitting into 6 separate fields - then I'll concatenate after exporting. So much for Access making my life easier.

 

[Frothingslosh]

So which version of excel and access are you running? We have 2003 at work, and the suggested steps worked perfectly for me. You shouldn't have to do all this splitting and concatenation.

 

[DHudson]

2010 for both Excel and Access -- that could be the problem - Microsoft "upgrade"