change data type when importing

[jammyp]

Hi,
I am trying to import an excel spreadsheet into Access2000 using the import wizard.
I choose my XLS file, I say whether or not the first row contains headings, (no is the anwser).
Then I want to change some of the fields to data type of memo from text, but all I can see if Field 1 and data type option is blanked out.
If I click or double click on another field it doesn't change.
so I cannot change the datatype of a field..
do you know why, and how I can fix this ?

The reason I want to do this is one of the rows in excel has 300 characters, and the text field in access only allows 255.


regards,
Jamie

 

[neileg]

You could define an empty table and import to the table. This will allow you to set the datatypes instead of allowing Access to guess (and get it wrong).

 

[jammyp]

Hi , I created a table, and import the spreadsheet but the memo field is being converted back to text !!

 

[jammyp]

Hi, I still am having problems importing large text fields from excel into Access.
how else could I import a 300 character field into access as a memo field.
I try to import into a new table but cannot change the data types..

thank you
Jammy

 

[neileg]

Works fine for me! When you import data, Access makes a guess as to the data type from the first few rows of data. If your data does not contain more than 255 characters in the first few rows, it will assume this is a text field. That's a problem. What I sometimes do is to include a dummy row of data as the first record which has clear unambiguous data types. So if I wanted a memo field I'd have a dummy record with more than 255 characters in that field.

However, if you create an empty table in Access with the relevant field defined as memo and import your data to the existing empty table, it won't change the data type from memo to text, even if none of the records has a field length of more than 255.

 

[jammyp]

thanks Neil, I will add the dummy row, that might fix it !