Link to XLS or Import DBF

[Bob Barnes]

I posted this in Import/ Export ..no replies yet. Looking to see if someone
reading here might know the solution...

A snippet (see below) of an Excel file that I tried Access linking to
Excel...the Color "3R3" below links in Excel as #Num! (a dealbreaker).

If I save as a DBaseIV, any Color NOT readable as a number arrives as a Blank.

Also, saving as a DBF changes all the "GoPK" below to a Date..IE 9/10/09. I
can deal w/ that in the VBA code I use working w/ the DBF. I had hoped to
either bring in a GoPk (IE...9/10/09 6:49:09 AM) as a Date (w/time), or use
as a PK (as a text)..which would be unique combined w/ Booth as a multi-key
PK. Is there a known "negative" using a longer text (IE...9/10/09 6:49:09
AM) as part of a PK?

I could also deal w/ this data w/o using a PK.

The ISSUE now...getting all Color values in the DBF when they appear as a
number (IE..202), or a text (IE...3R3). TIA - Bob

GoPk Booth Acolor
9/10/09 6:49:09 AM C 202
9/10/09 6:53:33 AM D 202
9/10/09 7:55:44 AM D 3R3
9/10/09 8:12:30 AM C 202

 

[Clif McIrvin]

Another victim of loose typing (Excel) vs. strict typing (Access).

An Excel Save As .csv to which you link from Access might work.


Here is some information I have gleaned over time regarding the #NUM!
error when importing or linking to Excel spreadsheets.

Excel stores cell values as a variant, and regardless of how you format
the entire column you can end up with different variant data types on a
cell by cell basis.

This becomes a nasty problem because the 'transfer spreadsheet' function
that Access uses to either import or link from an Excel worksheet does
not do any data type conversion. If you 'trick' Access into expecting
text data and one of the Excel cells has a number(or date) data type you
will get the #NUM error; conversely if you have everything formatted as
numeric(or date) and Access comes across a text datatype cell you will
get the #NUM error. (Empty cells properly come across as Null in either
case.)

I have also found that a cell containing a single space (in an otherwise
numeric or date column) will link as #Num!.


From MS Access MVP Roger Carlson:
Because I've had so much trouble with linking Excel files into Access
(previous to Access 2007, you had no control over the datatype), I've
taken to ALWAYS linking them as CSVs. That way I can define the
datatype in the Import Specification. (www.rogersaccesslibrary.com)


Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File (additional discussion of Jet, possible
registry hack)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr


Here's a thread where James A. Fortune demonstrates using SQL to
succcessfully link to an Excel spreadsheet containing mixed data:
http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/41994/Num



Threads containing additional discussion:

http://groups.google.com/group/micr...read/809542461417afdf/7440746c9fa31c61?q=#num

http://groups.google.com/group/micr...read/b4b7748816387977/f8eaa8f9c95bc766?q=#num

Barry Gilbert discussing multiple data types in an Excel comumn:
http://groups.google.com/group/micr...roup:microsoft.public.access#a8b5bcd4f77450d9

This post by Mark shows a macro to force Excel cells to text data type
and another to force cells to a numeric type:
http://tinyurl.com/3vt5rm

Van T. Dinh finds the easy way is to modify the Excel file as follows:
* Insert a "calculated" Column next to the MixedColumn
* Link the Excel file to Access, ignore the original MixedColumn and use
the "calculated" Column. All values in this Column will be Text so the
values won't have #NUM entries.
His discussion is at http://tinyurl.com/4vf6uv .

One possible work-around is to use the procedure in this KB article to
force every cell to text type in the Excel sheet:
(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us

-- Clif

 

[Fred]

Bob, you may be way ahead of me on this, but.....

Your post kind of hopped all over the place with a lot of terminology but
short on clear statements on exactly what you are trying to do. That may
be why you're having trouble getting an answer.

Your color field in the Access table should be formatted as a text field and
it sounds like it may be formatted as a numeric field.

As written, your "I had hoped to....." statement looks like an "either or"
statement between two totally different types of objectives, and so you did
not communicate your objective in that area.

I don't know of any categorical disadvantages of a long PK, but that
question could probably be more usefully answered in the context of the field
type and the method that you are using to populate the PK and related FK's.
For example personally I would not like to use a field where the actual
value is hidden (e.g. date/time) as a (part of a) PK. Another example is that
if you'll ever have situations where people have to manually enter a long
complex PK value (including spaces etc.) into a FK (not that such is common
or unavoidable) , that is certain to go badly. .

 

[Bob Barnes]

Thank you Fred.

After I Posted this Topic, I remembered a way to get this to work. When
Excel makes the DBF, it examines the first row(s) to "guess" what data type
it is. The way to get the DBF to bring in bith numbers & text is to ensure
the 1st row is, in this scenario, a text..IE...the ist row I enter below is a
fake record which I remove from the DBF after it is created. I had tried
making the entire column a "text", but taht didn't work when creating the DBF.

Also, making the Date/ Time "PK" as a text would control when new records
are imported...where either an "AddNew" of "Edit" would be used during a Do
Loop
examining additional DBF Imports.

GoPk Booth Acolor
12/31/09 A 3R3 <--- Makes Excel treat Column
as a text
9/10/09 6:49:09 AM C 202
9/10/09 6:53:33 AM D 202
9/10/09 7:55:44 AM D 3R3
9/10/09 8:12:30 AM C 202