SOME info from Excel won't import ... ???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a shared Excel file where various people go in a plug in numbers. My
Access 2003 database is linked to that file, and as necessary I use that
linked table in a query that moves the data over into a different table.

PROBLEMS:

(1) In the linked table, some of the data items (most are numbers, but
sometimes there's an "N/A" or other text) display as errors, even though in
the Excel file they don't look any different that the rest of the data.

(2) In the query part of the process, those errors don't transfer at all,
so in the final destination table I end up with nulls.

If that's not weird enough, if I do just a straight
copy-from-Excel-and-paste-into-Access, it all transfers just fine.

I'm stumped -- any ideas?
 
Dear Larry:

When you import the data, put this into a column that is text. Then you
won't have any problems with the import step.

Probably, you should have a step that puts the data from an "imported table"
to a "fully functional" table. Put only the values that are numeric into a
numeric column. Put the other values in another column if you want to keep
them at all.

Tom Ellison
 
Thanks for the input, Tom. In this case the "daisy chain" starts out with
the linked Excel file, and I am already getting the errors at that point, so
anything I do with that table downstream produces nulls wherever there's an
error. As far as I know there's no way I can set individual columns in a
linked Excel file as text. If you know of one, let me know.
 
Dear Larry:

I was suggesting you set the column in the Access database to text.

Can you open and just view the Excell spreadsheet as a linked table?

Tom Ellison
 
The column in the "final destination" table IS set to text, but the Excel
file is a linked table from which I get the data (via my query) to append to
my "fd" table. In the LINKED table I'm already getting the errors, for no
reason I can figure out, so my query brings across nulls for any error items.
To illustrate:

Actual Excel File Linked Excel File
Destination Table
12 12
12
192 #Error?
Null
44 44
44
N/A #Error?
Null
33 33
33
417 417
417
etc., etc.
 
Hi Larry,

I am not an Excel expert, but I
believe this link will help you.

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

i.e., if I understand correctly:

1) in registry set

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/
ImportMixedTypes=Text

2) in connection set IMEX=1

go into Design View of your xls link,
click on Properties icon,
in Description...something like:

Excel 8.0;HDR=YES;IMEX=1;DATABASE=C:\X.xls;TABLE=WS$

Or, don't link at all...use queries

***quote****
Newsgroups: microsoft.public.access.externaldata
From: (e-mail address removed) (Jamie Collins)
Date: 23 Jun 2004 04:34:25 -0700
Subject: Re: importing SOME columns of excel file

<snip>

To import into an existing table:

INSERT INTO
MyExistingTable
SELECT
F1, F3, F5
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100];

To import into a new table:

SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
INTO
MyNewTable
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A1:E100];

Jamie.
***unquote***

notice how fields can be "ordinal"
if don't use header.

I don't know why you could not
"massage" data in the query.

SELECT
CStr(F1 & "") AS MyCol1,
CStr(F2 & "") AS MyCol2,

{etc}

INTO
MyNewTable
FROM
[Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[Sheet1$A2:E100];

of course, "started sheet range" on second row ($A2)
ignoring header

Like I said, I'm not an Excel expert,
so test on a copy of the xls
(and I guess you do realize that
Excel is not like Access... it was
not designed for multiple users
accessing worksheet at same time).

good luck,

gary
 
Back
Top