If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Import from excel
I have code like this to import from excel into access:
SELECT INDEX, ...DM FROM [Excel 8.0; Extended Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invo ice.xls].[BASE$] problem is: field DM (as diameter) has both number and text (like N/A when it doesn't exist)..under windows registry, the setup allow Access to look up 1st 20 cells of the col and then it decide the column is 'number'...which I don't want. I want to import it as text. I like some expert opinion on how to maneuver this in vba or sql query...NOT from excel side (like to insert rows or reset excel sheet value..) I have tried: str(DM) as DM, but it wont' work since function str only work with well-formated values. And formatting DM column as text won't work either. Access take it as number (double) anyway. I also tried more complicated way, like IIF(Is numeric(....)), won't work either. Generally speaking, is it possible to control Access to take value as Text (or any other format), independend of what the 'True' format of data resource???? Many thanks@@!! -- ************************************************** The Spoon Does Not Exist. |
#2
|
|||
|
|||
Top of my head, not verified:
Try using CStr, which should handle the region specific formatting of numbers correctly when converting to text. Regards, Andreas sbcglobal wrote: I have code like this to import from excel into access: SELECT INDEX, ...DM FROM [Excel 8.0; Extended Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invo ice.xls].[BASE$] problem is: field DM (as diameter) has both number and text (like N/A when it doesn't exist)..under windows registry, the setup allow Access to look up 1st 20 cells of the col and then it decide the column is 'number'...which I don't want. I want to import it as text. I like some expert opinion on how to maneuver this in vba or sql query...NOT from excel side (like to insert rows or reset excel sheet value..) I have tried: str(DM) as DM, but it wont' work since function str only work with well-formated values. And formatting DM column as text won't work either. Access take it as number (double) anyway. I also tried more complicated way, like IIF(Is numeric(....)), won't work either. Generally speaking, is it possible to control Access to take value as Text (or any other format), independend of what the 'True' format of data resource???? Many thanks@@!! |
#3
|
|||
|
|||
Thank you Andreas, but Cstr won't work, because it convert number to string,
but if I have text in column, like "aaa", then it return error. "Andreas" wrote in message ... Top of my head, not verified: Try using CStr, which should handle the region specific formatting of numbers correctly when converting to text. Regards, Andreas sbcglobal wrote: I have code like this to import from excel into access: SELECT INDEX, ...DM FROM [Excel 8.0; Extended Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invo ice.xls].[BASE$] problem is: field DM (as diameter) has both number and text (like N/A when it doesn't exist)..under windows registry, the setup allow Access to look up 1st 20 cells of the col and then it decide the column is 'number'...which I don't want. I want to import it as text. I like some expert opinion on how to maneuver this in vba or sql query...NOT from excel side (like to insert rows or reset excel sheet value..) I have tried: str(DM) as DM, but it wont' work since function str only work with well-formated values. And formatting DM column as text won't work either. Access take it as number (double) anyway. I also tried more complicated way, like IIF(Is numeric(....)), won't work either. Generally speaking, is it possible to control Access to take value as Text (or any other format), independend of what the 'True' format of data resource???? Many thanks@@!! |
#4
|
|||
|
|||
Use an IIF function to test for datatype:
IIF(IsNumeric([DM]),CStr([DM]),[DM]) Regards, Andreas sbcglobal wrote: Thank you Andreas, but Cstr won't work, because it convert number to string, but if I have text in column, like "aaa", then it return error. "Andreas" wrote in message ... Top of my head, not verified: Try using CStr, which should handle the region specific formatting of numbers correctly when converting to text. Regards, Andreas sbcglobal wrote: I have code like this to import from excel into access: SELECT INDEX, ...DM FROM [Excel 8.0; Extended Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\I nvoice.xls].[BASE$] problem is: field DM (as diameter) has both number and text (like N/A when it doesn't exist)..under windows registry, the setup allow Access to look up 1st 20 cells of the col and then it decide the column is 'number'...which I don't want. I want to import it as text. I like some expert opinion on how to maneuver this in vba or sql query...NOT from excel side (like to insert rows or reset excel sheet value..) I have tried: str(DM) as DM, but it wont' work since function str only work with well-formated values. And formatting DM column as text won't work either. Access take it as number (double) anyway. I also tried more complicated way, like IIF(Is numeric(....)), won't work either. Generally speaking, is it possible to control Access to take value as Text (or any other format), independend of what the 'True' format of data resource???? Many thanks@@!! |
#5
|
|||
|
|||
sbcglobal wrote: I have code like this to import from excel into access: SELECT INDEX, ...DM FROM [Excel 8.0; Extended Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invo ice.xls].[BASE$] problem is: field DM (as diameter) has both number and text (like N/A when it doesn't exist)..under windows registry, the setup allow Access to look up 1st 20 cells of the col and then it decide the column is 'number'...which I don't want. I want to import it as text. I like some expert opinion on how to maneuver this in vba or sql query...NOT from excel side (like to insert rows or reset excel sheet value..) In lieu of an expert... You have a good understanding but you haven't quite grasped that it is *Excel* that does the scanning of rows to determine a data type (actually, it is Jet but Jet on the Excel side, rather than Jet on the MS Access side, so it's easier to think in terms of Excel doing the scanning). Once Excel has decided and coerced and/or nulled values as required, it is a done deal. So you seem to be asking, is there anything you can do in MS Access to influence Excel's choice of data type? The straight answer is, no. You are clued up about using IMEX=1 and the registry keys; you may even have read this: http://www.dicks-blog.com/archives/2...ed-data-types/ In case you haven't already, try changing TypeGuessRows from your current setting of 20 to zero. This should force Excel to scan all rows to determine whether a mixed types situation exists; if it does it will determine the data type as Text (or the value of the ImportMixedTypes key if different). Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel import failure w/o a reason | Scificajun | General Discussion | 0 | February 11th, 2005 02:01 PM |
how do I enable "import text file" excel 2002? | jw_schmid | General Discussion | 2 | February 9th, 2005 10:39 PM |
Import Excel Spreadsheet - Move Data to the Correct Record | [email protected] | General Discussion | 3 | February 1st, 2005 11:32 AM |
Windows in Taskbar | Chevy | General Discussion | 8 | October 15th, 2004 03:57 PM |
Import to OL2003 from Excel | Judy | Contacts | 2 | August 19th, 2004 03:09 PM |