Linking Excel into Access - format of columns is wrong 2

[hayo]

When linking Excel (XP) into Access (XP), I have a problem with the format of specific columns: They are set to "Number", but I want them to be "Text" in Access.

Is there a way to force Access to define them as text ? Most of the column values are numbers, but not all of them.

I know that I could transfer XLS to CSV format, but then I loose the possibility to update Excel cells from Access.

Thanks for any hints.

Hayo

 

[dodge20]

If you open the table in design view. You can change the column type to anything you want by changing the datatype for your particular field name.

 

[hayo]

This is NOT possible in my case. Let me explain the situation in more detail:

I opened Access, then I goto External data, here I select "link table" (where I also select XLS type). I do NOT select "import" !

When I link an Excel-Sheet this way, I can update it in Access, but I cannot change any field properties.

Hayo

 

[jebry]

Hi!

The only way I have ever found is to go into the Excel sheet and add a ' in front of all the data. Access will recognize this as indicating the field is to be treated as text.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Isadore]

'hayo: I think Access just looks at the first record in Excel and sets the format for each column. Just make sure each column in the first record is text.

 

[hayo]

Hi Jeff and Isadore,

thanks for your replies.
Changing only the first value to text is NOT sufficient. I changed the first 4-5 to text, and it still converts to number !

I think the tip to insert a ' in front of >each< value looks good.

But how can I easily do that in Excel, if I have a big number of rows ?? (I am not an Excel expert).

Hayo

 

[Isadore]

hayo: I'm surprised about the first row not responding; very interesting, that has been my experience. I have a friend who is an Excel expert, although he doesn't drop by the Tek-Tips forum. Send him an email with your question, and tell him &quot;Isadore from Auburn&quot; sent ya - he'll give you his 2 cents; which may give you and edge. His name is Sergio Cordova, and his email add is:

ruizcor@auburn.edu

One thing I know he can help you with with is manipulation of Excel tables - he's been doing it for many years. Good Luck.

 

[slreynolds]

You could use a query to convert the field to text using
str(). Set output all fileds to yes and just use the query instead of the table.

 

[jebry]

Hi!

Add a column next to the one you need to change and in the first cell put =&quot;'&quot; & A1 (or whatever column you need to change). Then select the cell and grab the autofill handle and drag down.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[hayo]

Thanks for all your answers !

An important point is the ability to update the Excel sheet from MS Access, but also from MS Excel. Therefore I think, the idea to place a &quot;'&quot; in front of the column is appropriate (doing this with the technique shown by Jeff).

If I apply that to the original column, I get the result I wanted. Gave you some stars for your help.

Thanks
Hayo