linking or merging to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet in Excel that has customer information including phone
number etc... I've imported it in to Access 2000. It imports the phone
number as a number field. I need it to be a text field to perform other
functions such as find unmatched. Iv'e tried changing the format in Excel
prior to importing but it still imports as a number. Is there a way to
change the field type without opening the table in design view and manually
changing it?
 
When you link to an EXCEL spreadsheet from ACCESS, Jet (the database engine)
reviews the first 8 to 25 rows of data in the spreadsheet and decides what
the data type is. If there are no nonnumeric characters in those initial
rows, Jet will assign a numeric data type. Then, for rows farther down with
nonumeric characters, ACCESS will display the #Num! error because those
strings are not numeric.

With linking, your have two choices involving changes to the EXCEL
spreadsheet:
1) Put nonumeric characters in the first row of the spreadsheet.
2) Put an ' character in front of every value in the appropriate
column for the spreadsheet. That tells Jet that the value is a text value
and not a numeric value.

You can make changes to the Registry itself that will force Jet to scan all
the rows before deciding on a data type. See this article for information
about how to change the MaxScanRows property to the value of 0:
http://www.dicks-blog.com/excel/2004/06/external_data_m.html
 
I wanted to automate this process. Adding nonnumeric character also adds
false records and putting ' is a process. Is there anything else? Is
importing a better option?
 
You could import the data as done now, but import to a temporary table. Then
use an append query to copy the data to your permanent table where the data
type is correct. Note that this method has the potential of an error if a
phone number might be a "nonnumeric" entry because of a typo, etc.

Otherwise, you could use Automation to open the EXCEL file, read each row
and write each row to your table via recordset. Laborious but would work.
 
Another thought... you could save the EXCEL file as a .csv file, then use
TransferText (with an import specification that defines that field as text)
to import the data from the .csv file to your table. If you want to automate
this, you can do the EXCEL save as via Automation.
--

Ken Snell
<MS ACCESS MVP>
 
And yet one other possibility --

You might be able to use a query to extract the data from the EXCEL
worksheet. I've not used such queries, but the newsgroups contain lots of
examples of queries where a source table is an EXCEL spreadsheet. If that
were to work, you could use a calculated field for the "phone number" data,
and have that calculated field explicitly cast the data as text
(CStr([Fieldname])). If this is of interest, post back and I'll see if I can
scrounge up some examples for the query. This would avoid the need to use
Automation.
 
Back
Top