Truncating memo fields importing csv

[BillPiper]

Using Office 2010 in Windows 7, I am getting import errors when I try to import a csv file into an existing table with some memo fields. The data destined for these fields is being truncated at 255 chars as if the fields were text.Any help appreciated. Thanks

[TheSmileyCoder]

If you do the import manually, you should be able to specify the field type during the import wizard. That import specification can be stored, and reused in for example code.

[BillPiper]

If you do the import manually, you should be able to specify the field type during the import wizard. That import specification can be stored, and reused in for example code.

Thanks but it doesn't give me this option if I am importing to an existing table, only if I create a new table every time.

[NeoPa]

BillPiper:
Thanks but it doesn't give me this option if I am importing to an existing table, only if I create a new table every time.

That's not accurate Bill. I suspect you haven't read Smiley's comment carefully enough.

When you do an import manually, whether into a new table or an existing one, you get the opportunity of specifying many details about the import. You also get the opportunity to save this Import Specification for reuse at other times. If you do that, then any future imports can be made to use that named Import Specification, and thereby follow the way you have set it up.

Does that make the situation clearer?

[BillPiper]

Thanks. I think I am being thick but I cannot find the option to specify the field type. Using the wizard, I accept the 'delimited' nature as offered, then check the 'first row contains field names' and choose " as text identifier. The next step takes me to the table name and finish. I have tried going into the 'Advanced...' option and understand what you mean about saving the specifications but although I can see the field names listed on the left there doesn't seem to be a way of controlling their properties. Perhaps I have misinterpreted what you mean by 'manually' - do you mean without using the wizard at all? If you caan put me right I will be most grateful! Cheers.

[NeoPa]

I'll do what I can, but your comment wasn't very full of information, so I can only explain it relative to that.

You indicated that you were able to do something when importing to new tables that you were unable to when importing to existing tables. I was simply pointing out that you could save the Import Specification when you import it into a new table then re-use that same Import Specification in future imports when loading into an existing table.

I ran a test myself, and it worked fine for me. The column in the Import Spec you need to set is called [Data Type]. Which bit are you having difficulty with? Re-using the Import Spec? Going into Advanced? It's hard to help unless you give a clear indication of where you get stuck.

[BillPiper]

Thanks for your patience! I understand what you mean and will give it a try. I never used to have this problem prior to moving to 2010 version - memo fields just impoted fine.

[TheSmileyCoder]

Actually if you have not specified a field type, access will look at the first 25 rows(I believe 25 is the "magic" number) to determine the field type. If none of the first 25 rows are long enough to be a memo then it will treat it as text.

That is actually what can make it hard to spot, since the behavior can appear in-consistent.

[NeoPa]

Smiley makes a very good point there. Importing suffers a great deal from Access determining things for you unless you're very specific. When importing from Excel it's even worse, as there is no way to specify how data should be treated. You have to rely on Access' best guess.

[BillPiper]

Thanks for all the help. I have saved an import spec and it is now working fine. I had fallen foul of the 'first 25 lines' problem before so had tried deliberately putting more than 255 chars in first line of data but that didn't do the trick - it got truncated as well. Never mind, problem solved. Thanks again!