 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2006linking or merging to Excel
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  SignatureKen Snell <MS ACCESS MVP> >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 [quoted text clipped - 4 lines] > manually > changing it? 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? > 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 [quoted text clipped - 23 lines] > > manually > > changing it? 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. SignatureKen Snell <MS ACCESS MVP> >I wanted to automate this process. Adding nonnumeric character also adds > false records and putting ' is a process. Is there anything else? Is [quoted text clipped - 33 lines] >> > manually >> > changing it? 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. SignatureKen Snell <MS ACCESS MVP> > 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 [quoted text clipped - 49 lines] >>> > manually >>> > changing it? 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. SignatureKen Snell <MS ACCESS MVP> > Another thought... you could save the EXCEL file as a .csv file, then use > TransferText (with an import specification that defines that field as [quoted text clipped - 54 lines] >>>> > manually >>>> > changing it? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.