Re: Imported Excel file and datatypes - HELP!!!

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 work*** cell by cell and know that you're not
depending on registry keys.


On Fri, 03 Nov 2006 23:42:15 GMT, "Corey-g via AccessMonster.com"
<u19162@uwe> wrote:

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.transferspread*** acimport ... but this
also produced the issues with these 2 columns. I then switched to using ADO
& ADOX (to get the work*** name) and tried connecting to the excel
spread***, 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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Quantcast