Imported Excel file and datatypes - HELP!!!

[Corey-g via AccessMonster.com]

Hi All,

I have researched this, tried the work-arounds, and still am unable to get
this to work. Arrrrggg...

I am importing an excel file that has 2 fields that are numbers mostly (some
text) into a stage table with all fields as text. When the import finishes,
the numbers in these 2 columns are in scientific notation.

Now, this app must run as an MDE, and I am unable to touch / see / alter the
registry, so i can't change the values in the ImportMixedTypes or
TypeGuessRows keys (nor can I even tell what they are set too). I had
originally tried to use the docmd.transferspreadsheet acimport ... but this
also produced the issues with these 2 columns. I then switched to using ADO
& ADOX (to get the worksheet name) and tried connecting to the excel
spreadsheet, and doing
"Insert into MyLocalTable select * from [Excel 8.
0;HDR=Yes;IMEX=1;database=PathtoFile].[worksheetname]"

but to the same result. I actually just got a chance to ask a sys admin if
he could at least show me what the registry setting were (and barely cost me
anything - 2 weeks of getting his coffee) and the settings are

ImportMixedTypes --> Text
TypeGuessRows --> 0x00000008 (8)

Also, I forgot to add that I have sorted the file by these 2 columns in
descending order so that the text entries come first (and there is 44 of them)
...

Anybody able to offer any other suggestion, I'd be eternally grateful...

TIA,

Corey

 

[John Nurick]

Hi Corey,

What you're doing looks right to me. The only improvement (???) I can
suggest - based on a response at
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
is to explicitly cast the fields to text, e.g.

INSERT INTO MyLocalTable (Field1, Field2)
SELECT CStr(A.Field1), CStr(A.Field2)
FROM [Excel 8.0;HDR=Yes;IMEX=1; database=PathtoFile].[worksheetname]
AS A;

If that doesn't do the trick, you can always use automation to read
values from the worksheet cell by cell and know that you're not
depending on registry keys.

Hi All,

I have researched this, tried the work-arounds, and still am unable to get
this to work. Arrrrggg...

I am importing an excel file that has 2 fields that are numbers mostly (some
text) into a stage table with all fields as text. When the import finishes,
the numbers in these 2 columns are in scientific notation.

Now, this app must run as an MDE, and I am unable to touch / see / alter the
registry, so i can't change the values in the ImportMixedTypes or
TypeGuessRows keys (nor can I even tell what they are set too). I had
originally tried to use the docmd.transferspreadsheet acimport ... but this
also produced the issues with these 2 columns. I then switched to using ADO
& ADOX (to get the worksheet name) and tried connecting to the excel
spreadsheet, and doing
"Insert into MyLocalTable select * from [Excel 8.
0;HDR=Yes;IMEX=1;database=PathtoFile].[worksheetname]"

but to the same result. I actually just got a chance to ask a sys admin if
he could at least show me what the registry setting were (and barely cost me
anything - 2 weeks of getting his coffee) and the settings are

ImportMixedTypes --> Text
TypeGuessRows --> 0x00000008 (8)

Also, I forgot to add that I have sorted the file by these 2 columns in
descending order so that the text entries come first (and there is 44 of them)
..

Anybody able to offer any other suggestion, I'd be eternally grateful...

TIA,

Corey

 

[Corey-g via AccessMonster.com]

Thanks for the response John.

I have been trying to see if I can type cast the values all morning, but I'm
not having any success. The data still ends up in scientific notation.
Maybe I'm doing something wrong...

Here is the string I'm creating to import the data:

strSQL1 = "INSERT INTO IMPORT_MPL SELECT a.PART_NUMBER, a.MPL_DESCRIPTION, a.
MPL_STATUS, a.EFORM_FLAG, " & _
"a.EFORM_DESCR, a.MANUFACTURER, a.[PURCHASED FROM], a.
[CHARGE TO], a.DEFAULT_CHARGE_CODE, CStr(a.PO_GI) as PO_GI, CStr(a.PO_RETAIL)
AS PO_RETAIL, " & _
"a.CATEGORY, a.SUB_CATEGORY, a.AD_SECURITY_GROUP, a.
Specifications FROM " & strFullFile & " AS a"

Then i do a "docmd.runsql strSQL1"

Is something missing to actually do the 'type-casting' ?

Another point that I haven't mentioned is that ultimately this data is to end
up in oracle (which I have linked to my Access db). Maybe I could load the
data directly into Oracle without Jet interaction. I'm just not sure how to
do so (I am going to try using the Oracle table instead of my local access
table, but then I need to change alot of my queries as they filled the lookup
tables from this data - aka Category & Sub_Category, Manufacturer, etc...).
Actually this wouldn't work 'as is' without creating a stage table in Oracle
for the data.

Thanks again, and any other ideas are still appreciated...

Corey

 

[John Nurick]

Hi Corey,

I've been experimenting with a worksheet containing a table of three
columns

ID - sequential numbers 1 to 340-odd
Mixed - A mix of text and numbers
Number - Numbers.

In the registry,
ImportMixedTypes --> Text
TypeGuessRows --> 0x00000008 (8)

I'm using this SQL in a query and looking at the datasheet view:

SELECT * FROM [Excel 8.0;HDR=Yes;
IMEX=1;database=C:\Temp\CDF.xls].[CDF2$];

So far the only ways I can get the numbers in the Mixed column to show
up in scientific notation are

a) by applying Excel's "Scientific" number format to the cells in the
column

b) by using very large numbers (maybe ones that need more digits to
display in decimal form than a Double has significant figures).

Do either of these apply?



One work-roundm by the way, would be the time-honoured one of prepending
an apostrophe to the value in the worksheet. Even if this
123456789.012345
gets screwed up, this
'123456789.012345
will be linked or imported as literal text.

Thanks for the response John.

I have been trying to see if I can type cast the values all morning, but I'm
not having any success. The data still ends up in scientific notation.
Maybe I'm doing something wrong...

Here is the string I'm creating to import the data:

strSQL1 = "INSERT INTO IMPORT_MPL SELECT a.PART_NUMBER, a.MPL_DESCRIPTION, a.
MPL_STATUS, a.EFORM_FLAG, " & _
"a.EFORM_DESCR, a.MANUFACTURER, a.[PURCHASED FROM], a.
[CHARGE TO], a.DEFAULT_CHARGE_CODE, CStr(a.PO_GI) as PO_GI, CStr(a.PO_RETAIL)
AS PO_RETAIL, " & _
"a.CATEGORY, a.SUB_CATEGORY, a.AD_SECURITY_GROUP, a.
Specifications FROM " & strFullFile & " AS a"

Then i do a "docmd.runsql strSQL1"

Is something missing to actually do the 'type-casting' ?

Another point that I haven't mentioned is that ultimately this data is to end
up in oracle (which I have linked to my Access db). Maybe I could load the
data directly into Oracle without Jet interaction. I'm just not sure how to
do so (I am going to try using the Oracle table instead of my local access
table, but then I need to change alot of my queries as they filled the lookup
tables from this data - aka Category & Sub_Category, Manufacturer, etc...).
Actually this wouldn't work 'as is' without creating a stage table in Oracle
for the data.

Thanks again, and any other ideas are still appreciated...

Corey

 

[Corey-g via AccessMonster.com]

Thanks for the help John, I do appreciate it...

The numbers that I'm having the issue with are all 8 digit 'PO numbers'
(Which makes it a really big deal as they don't seem to want to change the PO
systems to work with scientific notation...)

I have made sure that these 2 columns are 'Text' in Excel, but I still get
numbers like this:
12345678 becomes 1.23456e+007
The sheet I'm trying to import contains 1400 entries, of which only 44 are
actual text. I did sort the data so that the text is first, but that didn't
help.

One thing I have noticed is now that I have changed it to use the "Insert
into MYTable select ...", it is also importing these numbers as scientific
notation in my test system now as well... Strange... Only difference is
that the "TypeGuessRows" key is 0x00000000 (0) on my test system.

So I am still unsure of what to do, but I'm working on a test that will use
transferText method. Then I can see if the users can export their files to
csv before loading them, which isn't ideal but what choice do I have...

Corey