linking or merging to Excel John C. 20.07.2006 01:59:02 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?

Re: linking or merging to Excel "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 20.07.2006 02:17:43 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 -- Ken Snell <MS ACCESS MVP> "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message news:CDF8F023-7DE1-4E6B-B87D-56F48598315A[ at ]microsoft.com... [Quoted Text] >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?

Re: linking or merging to Excel John C. 20.07.2006 02:36:01 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? "Ken Snell (MVP)" wrote: [Quoted Text] > 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 > > > -- > > Ken Snell > <MS ACCESS MVP> > > > "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message > news:CDF8F023-7DE1-4E6B-B87D-56F48598315A[ at ]microsoft.com... > >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? > > >

Re: linking or merging to Excel "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 20.07.2006 14:10:52 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. -- Ken Snell <MS ACCESS MVP> "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message news:697A069D-AAFE-4810-B095-32C32102B5ED[ at ]microsoft.com... [Quoted Text] >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? > > "Ken Snell (MVP)" wrote: > >> 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 >> >> >> -- >> >> Ken Snell >> <MS ACCESS MVP> >> >> >> "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message >> news:CDF8F023-7DE1-4E6B-B87D-56F48598315A[ at ]microsoft.com... >> >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? >> >> >>

Re: linking or merging to Excel "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 21.07.2006 11:55:17 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> "Ken Snell (MVP)" <kthsneisllis9[ at ]ncoomcastt.renaetl> wrote in message news:%23jAJtZArGHA.4408[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > 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. > > -- > > Ken Snell > <MS ACCESS MVP> > > "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message > news:697A069D-AAFE-4810-B095-32C32102B5ED[ at ]microsoft.com... >>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? >> >> "Ken Snell (MVP)" wrote: >> >>> 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 >>> >>> >>> -- >>> >>> Ken Snell >>> <MS ACCESS MVP> >>> >>> >>> "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message >>> news:CDF8F023-7DE1-4E6B-B87D-56F48598315A[ at ]microsoft.com... >>> >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? >>> >>> >>> > >

Re: linking or merging to Excel "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 21.07.2006 20:54:27 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. -- Ken Snell <MS ACCESS MVP> "Ken Snell (MVP)" <kthsneisllis9[ at ]ncoomcastt.renaetl> wrote in message news:eCS0qxLrGHA.492[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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> > > > > > "Ken Snell (MVP)" <kthsneisllis9[ at ]ncoomcastt.renaetl> wrote in message > news:%23jAJtZArGHA.4408[ at ]TK2MSFTNGP04.phx.gbl... >> 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. >> >> -- >> >> Ken Snell >> <MS ACCESS MVP> >> >> "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message >> news:697A069D-AAFE-4810-B095-32C32102B5ED[ at ]microsoft.com... >>>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? >>> >>> "Ken Snell (MVP)" wrote: >>> >>>> 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 >>>> >>>> >>>> -- >>>> >>>> Ken Snell >>>> <MS ACCESS MVP> >>>> >>>> >>>> "John C." <JohnC[ at ]discussions.microsoft.com> wrote in message >>>> news:CDF8F023-7DE1-4E6B-B87D-56F48598315A[ at ]microsoft.com... >>>> >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? >>>> >>>> >>>> >> >> > >