importing excel text data into access memo fields

[smithrd6]

I have an excel spreadsheet that contains text data, large text fields that I want to import into access; however, as you know, access limits text fields to 255 characters. Is there vb coding to do this? I have tried changing the table fields in access to memo but this changes back to text as soon as the data is imported.

thanks for your assistance.....

 

[SydneyGeek]

If you import the data into an existing table instead of a new one, the data type will not change. Try using File > Get External Data > Import, and specify an existing table (with the Memo field already defined) as the destination.

Denis

 

[smithrd6]

that works.....interesting thing is it duplicates entrys "I suppose" to capture the lengthy text content. To overcome this I am exporting the data back out to excell and filtering unique records and importing it again......

 

[SydneyGeek]

If you already had the same data in Access before doing the import, you will have duplicates in that table. Teh solution under those circumstances is to
1. Import to a new table with the data types already defined, then transfer new or changed data over to the original table.
2. If you haven't done anything much with the original imported data, delete it and then run the import.

Denis

 

[smithrd6]

Great info......the problem I have with importing to new table is the fields which have the extensive text info default to text fields in access rather than memo which truncates the data and limits those fields to 255.

 

[SydneyGeek]

Great info......the problem I have with importing to new table is the fields which have the extensive text info default to text fields in access rather than memo which truncates the data and limits those fields to 255.

That's the reason for this... maybe I wasn't clear enough with my description.

1. Import to a new table with the data types already defined, then transfer new or changed data over to the original table.

1. Do the import once only into a new table.
2. Convert the required fields to Memo

Then, each time you import a batch:

3. Delete ALL records from this table
4. Re-run the import; this time you will not get truncation.
5. At this stage you can compare new imports to existing data in the main table, and append new records, either ignoring or updating existing records.

Denis

 

[Serious Sam]

Can I ask how you got a cell in Excel to hold more than 255 characters in the first place?
I'm wanting a large amount of text in excel imported into an access table, and was thinking I'd have to embed a word document in the spreadsheet for the user to input in to,..... or some such thing.
Any ideas welcome.

 

[doco]

That's the reason for this... maybe I wasn't clear enough with my description.



1. Do the import once only into a new table.
2. Convert the required fields to Memo

Then, each time you import a batch:

3. Delete ALL records from this table
4. Re-run the import; this time you will not get truncation.
5. At this stage you can compare new imports to existing data in the main table, and append new records, either ignoring or updating existing records.

Denis

This does not work - no amount of cajoling will keep Access (2007) from truncating Excel cell text. What is interesting is it does not truncate at the same point in the text after each attempt to import. Very, very frustrating

 

[SydneyGeek]

If you save as CSV or a tab-delimited text file does that solve the problem?

Denis

 

[doco]

Haven't tried that but I will. Thanks