Importing Clipper DBF's into Microsoft Access 
Author Message
 Importing Clipper DBF's into Microsoft Access

I use the CDXDBF rdd with my clipper files and attatch or import them as
foxprow 2.5 tables.  Works great.  We routinely use that rdd anyway,
tho.  Don't know of any other way as MSAccess cannot 'read' clipper files.

Quote:

> I have a number of Clipper files and wish to import them into
> Microsoft Access.

> However, there is no specific 'Clipper' import facility and when I
> import a Clipper DBF with zeroes, Access converts the zeroes into null
> values which is not correct. Anyone had similar problem and able to
> provide solution?

> Cheers

> Steve


Deborah Painter
Systems Analyst/Programmer II
Unversity of Washington


Fri, 29 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access
I import them all the time.  You have to watch out for numbers though.
I'd like to hear the easiest way to handle them.  I assume it's in Access
through the format field menu.
Cheers
Ralph


Fri, 29 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access

Quote:

>However, there is no specific 'Clipper' import facility and when I
>import a Clipper DBF with zeroes, Access converts the zeroes into null
>values which is not correct. Anyone had similar problem and able to
>provide solution?

There's a workaround to this.  Because you can create a link to a Clipper
table (I've used DBIII type, no index), you can then create a query on
that table.  When you create the query, use expressions in lieu of the
field name in the clipper table.  In other words, normally you'd either
drag-and-drop the field name to the query column, or you can pull down the
list in the Field row of the query builder and locate the field you want
included in the resulting dataset.  Instead, in the Field column, write in
the expression.  For instance, to make sure I was telling you right,

1:  I used DBU to create a simple Clipper table called tester.dbf.  It has
two fields, "Name" and "Number".  Name was a character field and Number
was a numeric field with zero decimal places.

2:  I created an empty Access table made up of the fields "Name" and
"Number", character and number fields, respectively.  I had it make the
default counter/primary key since I was only testing this out.

3:  I then clicked on the "Create a link to external table" tab and set my
(DBIII) link to tester.dbf (when it asks you for an index file just click
"Close"), then "Close" again and it will tell you your link is
established.

4:  I then clicked on the Queries tab and clicked on New.  In the query
design page, I added the tester.dbf table.  I drug the Name field to the
first Field row of the first column, then I hand edited the Field row of
the second column to read the following:
     Iif(IsNull([TESTER]![NUMBER]),0,[TESTER]![NUMBER])
(You do not want to include the original numbers field in the query
columns, you'll see)
Access will automatically prepend a field name like "Exp1:".  You can then
manually edit that to read "NUMBER:" if you like.  While you're in the
query design page, the toolbar and the "Query" menu have "Append" as an
option.  When you click on or select it from the menu, you'll be asked
what table to append the query results to and then you select the table
you created in step number 2: above.

5:  Once you've done that, you can click on the ! "Run" button on the
toolbar and Access will report to you the number of rows that it's going
to append to your new table.  This will match the number of records in the
linked Clipper table unless, of course, you specify some criteria that
eliminates some of the originals.

When the append operation is complete, you'll see you have zeros in the
fields that Access thought were null values.

You may want to keep this kind of information handy.  I have seen this
requirement too many times to use it once and forget it.  I had a
world-wide hotel chain that set up a very large franchise sales system by
"rolling their own" using their front office clerk as their Access
"developer".  She did a brilliant job for a recent college grad with no
database management or programming experience.  But the problems were
manifold once they decided they were ready to "run with it."  They had a
major sales mailing that they needed mail-merge with Word and label
generation done for and she had set the zip code field up as a number
field.  Expressions in queries were as foreign to her as you could
imagine.  They were panicked since their labels indicated no leading
zeros.  You can imagine how many of their northeastern target group would
not get their mail, or get it late, or whatever.

What I'm trying to tell you is that field expressions in queries will come
in very, very, very handy over time.  We were able to very quickly replace
the zip field with an expression field in her mailing query and get them
on their way.  (Their labels used barcoding for maximum postage reduction
and quicke



Fri, 29 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access
I have a number of Clipper files and wish to import them into
Microsoft Access.

However, there is no specific 'Clipper' import facility and when I
import a Clipper DBF with zeroes, Access converts the zeroes into null
values which is not correct. Anyone had similar problem and able to
provide solution?

Cheers

Steve



Fri, 29 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access

Quote:

>I have a number of Clipper files and wish to import them into
>Microsoft Access.

>However, there is no specific 'Clipper' import facility and when I
>import a Clipper DBF with zeroes, Access converts the zeroes into null
>values which is not correct. Anyone had similar problem and able to
>provide solution?

I have had a little play with this problem, and have come to the conclusion
that just because a field in a dbf file shows a zero, it might actually be
empty, or uninitialized, and that would explain why it imports in Access as
nil.

This is what I did:

I opened a little file made with dBASE IV and put a couple of zeros in a
numeric field. Access imported this file with the zeros intact.

Next step was assign a nul numeric to var x (in dBASE)

 . x = val("")  (no space between quotes)
(dBASE echoed 0)

 . replace all VTAC_UNIT with x for VTAC_UNIT = 0
(dBASE echoed 2 records replaced)

 . brow
(dBASE showed the replaced records as zero (0) )

Looked at the file with DBU to see how Clipper sees things, and it showed them
as zeroes also.

Imported this file into Access, and again it came across with zeroes, so I was
not able to reproduce your problem.

However, I found a couple of things on the way:

1. The Clipper Reference Guide says that a value of 0 in a numeric field is an
'empty' value   eg EMPTY(0) returns .T. ie in Clipper a nul and a zero are the
same.

2. Access can have numeric fields that are actually empty, unless the
'required' property is YES.

I removed some values from the table in Access so they displayed as empty
fields, (just 'deleted' the values) then exported this to a dbf. When I looked
at this with DBU, the fields that should have been empty showed as zeroes.

And hence my conclusion stated above.

You could try something like

  replace all num_field with 0 for num_field = 0

to make sure the fields are initialized to zero.

Hmmm ... I cant think how to make an /uninitialized/ record!

Jim Maunder



Sat, 30 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access

Quote:


>>I have a number of Clipper files and wish to import them into
>>Microsoft Access.

>>However, there is no specific 'Clipper' import facility and when I
>>import a Clipper DBF with zeroes, Access converts the zeroes into null
>>values which is not correct. Anyone had similar problem and able to
>>provide solution?

[snipped by Jim]

I said:

Quote:
>You could try something like

>  replace all num_field with 0 for num_field = 0

>to make sure the fields are initialized to zero.

>Hmmm ... I cant think how to make an /uninitialized/ record!

>Jim Maunder

As soon as I sent the prev. reply, I had a look at the dbf with nul values
 with dBASE, and they showed no value. I APPENDed BLANK a couple of times,
had a look, and the appended records were also without values. So, it
looks like unititialized fields are the culprit.

Yup, imported the file back to Access, and found empty records. Looked at
it with DBU again, and found zeroes in the empty records.

Jim



Sat, 30 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access
I mean DBFCDX <G>.

Quote:
> I use the CDXDBF rdd with my clipper files and attatch or import them as
> foxprow 2.5 tables.  Works great.  We routinely use that rdd anyway,
> tho.  Don't know of any other way as MSAccess cannot 'read' clipper files.


> > I have a number of Clipper files and wish to import them into
> > Microsoft Access.

> > However, there is no specific 'Clipper' import facility and when I
> > import a Clipper DBF with zeroes, Access converts the zeroes into null
> > values which is not correct. Anyone had similar problem and able to
> > provide solution?

> > Cheers

> > Steve

> Deborah Painter
> Systems Analyst/Programmer II
> Unversity of Washington

Deborah Painter
Systems Analyst/Programmer II
Unversity of Washington


Sat, 30 May 1998 03:00:00 GMT  
 Importing Clipper DBF's into Microsoft Access
In your Visual Basic code you could use this function to convert your
nulls into empty strings.

Function FixNull(FieldValue as Variant) as Variant
If IsNull(FieldValue) then
FixNull=""
Else
FixNull=CStr(FieldValue)
End If
End Function

Hope this helps
--
Alexander Medwedew
Computer Ventures, Inc.

http://tribeca.ios.com/~compvent/
CADVANCE LITE - Affordable CAD Software
http://tribeca.ios.com/~compvent/cadvlite.html



Sun, 31 May 1998 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Importing Clipper DBF's

2. Anyone know how to link Clipper .DBF's to an Access 8.0 database

3. clipper code to import ASCII to a .dbf file

4. Help needed importing Clipper dbf files

5. Importing from Microsoft Access in Fortran

6. Limits of Clipper DBF's and NTX's

7. Accessing one's main script's global from imported libraries

8. Clipper .dbf to Access? Help

9. dbf files with clipper indexing and access forms

10. How to tell if Read Only DBF access in CA Clipper 5.2 for DOS

11. Importing jpeg's to Microsoft word

12. Microsoft Access with Data in Clipper

 

 
Powered by phpBB® Forum Software