Solved

importing bulk dbf files TO oracle or access

Posted on 2013-05-25
48
750 Views
Last Modified: 2013-07-23
hi
i have one folder , contain of 100 folders
each folder named like this : store_01,store_02,,etc   (store number)
under each store folder , there are many dbf monthly transactions file , named preffex of transaction type & month name
for example :
for sales transactions : SALJAN13,  SALFEB13 , SALMAR13
for purchase transactions :   PURJAN13,  PURFEB13 , PURMAR13
etc
i know how to import it from dbf to access , then from access to oracle
but the problem is : it is so difficult to do it manual
i wish to have a code to import it all through code , eighter to access , or directly to oracle
0
Comment
Question by:NiceMan331
  • 22
  • 12
  • 6
  • +3
48 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
For Access you can find function for file search in subfolders here:
http://www.ammara.com/access_image_faq/recursive_folder_search.html
Then you can link each file and import it. If you don't need to add extra fields (date from file name, for example), you can import files directly, but I prefer link and import with query.
0
 

Author Comment

by:NiceMan331
Comment Utility
thanx , but the request is full code , not to only find the directory , but to also import & append into a specific table
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
It is possible, but in this case we need sample files and you should describe details: do you like to add imported files to one table or each file should be imported saparately?
0
 

Author Comment

by:NiceMan331
Comment Utility
it should be appended to same table
regarding sample file , i'm sorry it is official , but you can use any sample file , the important one is the code
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Look at sample. You should have table (DBFImport) with same structure as your dbf files (you can manually import one file at first). QryImport is used for appending (you can modify it if you need to make any data conversion).
DBdbfimport.accdb
0
 

Author Comment

by:NiceMan331
Comment Utility
yes it works
but i preffere if you change it from executing the query to directly insert into the table
to avoid any missing while appending & deleting
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 75 total points
Comment Utility
There are no direct methods for appending. It will be always an append query. You can write it in VBA, but it is query.
DBdbfimport.accdb
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What is the format of the DBF files?

You might be able to go straight to Oracle using SQL Loader.
0
 

Author Comment

by:NiceMan331
Comment Utility
slightwv
i prefer to do that
the bdf file format , for sales transactions ,  is  :
sale_date,sale_qty,sale_amount
date / number / number
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Can you post some actual data?
Also confirm the input files is an actual text document and not some proprietary binary file that is mostly text.

What you posted shows commas (,) in the header and slashes (/) in the data.

I would also like to see the date format since I assume you want to load it into a date data type in the database.
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , could you please give me a chance till Saturday , cause the file is in my office , and i will go there on Saturday mooring
thanx in advance
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Just make sure anything you post or upload here isn't sensitive information.
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , i'll do
but i worry if tomorrow will be your holiday ?
if that , it is better i'll design now sample dbf to win the time
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I would prefer actual data.  It would save issues with typographical errors.

I'm normally not on this site on weekends so if you prefer to have something to test with before Monday, I'll take a look at whatever you provide.
0
 

Author Comment

by:NiceMan331
Comment Utility
I TRIED TO UPLOAD DBF FILE , but it not accept it
so , i convert it to xls
same filed format are the dbf exactly
but could you do another favor also
this file , as i mentioned above , is found under many folders
so , i would like in oracle to add folder_name in additional field , so i will know the source of the data
SALJAN01.xls
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I TRIED TO UPLOAD DBF FILE , but it not accept it

Could have renamed it to a .txt file.

>>so , i convert it to xls

This doesn't help me.  I need to know what the field delimiters of the original file are.

Please rename the raw data file to .txt and attach it.

>> i would like in oracle to add folder_name in additional field , so i will know the source of the data

This is a pretty common wish with sql loader.  Unfortunately there isn't a magic way to do this.

You can create a constant literal in the sql loader control file but this will require a different control file for each file.

There are many links out there on adding the file name to part of a sql loader run if you Google around for them.
0
 

Author Comment

by:NiceMan331
Comment Utility
check
SALJAN01.txt
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
OK, that looks like it is some form of binary file.  I don't see how sql loader will be able to parse it.  I cannot find any hard delimiters.

If you can come up with some fixed-width's there still might be a chance.

It looks like there might be specific offsets in the data but you would need to confirm the actual file layout.

It might be best to write your own code.  Using pl/sql and utl_file to read the file and parse it.  This would better allow you to capture the file name and location.

The down side to this approach is the files would need to be on the database server.

You may need to write code on the client (I would probably use PowerShell or a .Net console app).

It would help a lot if you knew what program was generating the files.  There may already be some code or program out there that can parse these already.
0
 

Author Comment

by:NiceMan331
Comment Utility
The file generated by dbase program
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 75 total points
Comment Utility
If you Google around you can find tools that can load a DBase file into Oracle.

Here's a stored procedure:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:711825134415

Here's a .Net app:
http://www.webxpert.ro/andrei/2008/05/30/dbase-dbf-import-to-oracle-freeware-tool/

Keep in mind, I just found these links and cannot verify any of the code or products.
0
 

Author Comment

by:NiceMan331
Comment Utility
hi
here is real file
i just uploaded from my office
SALAUG12.txt
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
OK, same suggestions apply.  This is a binary file.  If it is a DBase file, check the links I posted above.
0
 

Author Comment

by:NiceMan331
Comment Utility
but sorry , in the link it is too long procedures , how can simplify it ?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I don't know that you can.  It takes as much code as it takes to parse a DBase file.

You can try to write your own but you will need to find the specs on the layout of how DBase stores it's data.  This should be available on the Internet somewhere.
0
 

Author Comment

by:NiceMan331
Comment Utility
till now i didn't know how to upload to oracle
that site which slightwv suggest it has a very difficult code
i didn't understand it
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
The code from Tom Kyte's site has the stored procedures and examples on using it.  Sorry but I don't know how to simplify it more.
0
 

Author Comment

by:NiceMan331
Comment Utility
ok , i appriciate
i wish to accept the answer
but
i found it is difficult for me to do it my self
sorry
and thanx to your help
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>i found it is difficult for me to do it my self

It should be as simple as executing the code to create the package/procedure then run it.

For example:  Do you really need to see the code for dbms_random.value or do you just use it?   select dbms_random.value from dual;

When you created the database Oracle ran the code that created the package dbms_random.

In this case, you need to run the code that creates the package.  Then you just use it.

You might think about hiring a consultant to come in and help you set up the procedures.  They can also explain to you what the different pieces do.
0
 

Author Comment

by:NiceMan331
Comment Utility
Ok good solution, but how to call that consultant ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Check for local Oracle consultants in your area.  Most major cities have consultants that specialize in Oracle.


You can always call Oracle directly:
http://www.oracle.com/us/products/consulting/index.html
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
A couple of questions
1. Do you need to add data during the insert that indicates which DBF file or folder was used as the source?
2. Are the DBF and Oracle tables identically structured (same column names, column order, column data types)?  If not, what are the mappings?

============
I would use Access as the glue that ties these two data sources together.
* Attach the Oracle tables to the Access database
* write some code that will iterate the folders and files and execute a query to pump the data from each DBF to the associated Oracle table.  The data is not stored in the Access database
* These queries would use the IN clause to reference the DBF directly from the query.  There is no need to attach any DBF to the Access database.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
Here is an inexpensive tool: dbf2csv.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
All you need to code is a batch script that will convert all the dbf files to csv.
0
 

Author Comment

by:NiceMan331
Comment Utility
aik

1. Do you need to add data during the insert that indicates which DBF file or folder was used as the source?

yes , i would like to add folder name

2. Are the DBF and Oracle tables identically structured (same column names, column order, column data types)?  If not, what are the mappings?
 yes they are same structure , or in other word : i will start to create oracle table as per the DBF structure
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
In the following code example, your Oracle columns are named OrC#.  You will want to change these to your actual Oracle column names.  The same applies to the extra data columns (storename and MMMYY).

Likewise, the dBase column names are DBFC#.  You will want to change these to your actual dBase column names in the same order as the corresponding Oracle column names.

This code assumes you have attached the Oracle tables to your Access database with the names ("SAL", "PUR").

Finally, the root path constant will need to be changed to the parent directory of your store folders.  This code assumes that only the DBF files will be in the

Option Explicit


Public Sub Q_28138929()
    Const cRootPath As String = "C:\Users\Mark\Downloads\Q_28138929\"
    Const cSQL As String = "Insert Into {OracleTable} (OrC1, OrC2, OrC3, OrStorename, OrMMMYY) " & _
                        "Select (DBFC3, DBFC2, DBFC1, '{Storename}', {MMMYY}) " & _
                        "FROM Table IN ""{DBFpath}"" ""dBASE IV;"""

    Dim strType As String
    Dim strMMMYY As String
    Dim oFS As Object
    Dim oStore As Object
    Dim oFile As Object
    Dim oRoot As Object
    Dim strTemp As String
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Set oRoot = oFS.GetFolder(cRootPath)
    For Each oStore In oRoot.SubFolders
        'Debug.Print oStore.Name
        For Each oFile In oStore.Files
            'Debug.Print , oFile.Name
            strType = Left$(oFile.Name, 3)
            strMMMYY = Mid$(oFS.GetBaseName(oFile), 4)
            strTemp = cSQL
            strTemp = Replace(strTemp, "{OracleTable}", strType)
            strTemp = Replace(strTemp, "{Storename}", oStore.Name)
            strTemp = Replace(strTemp, "{MMMYY}", strMMMYY)
            strTemp = Replace(strTemp, "{DBFpath}", oFile.Path)
            DBEngine(0)(0).Execute strTemp
            'Debug.Print , strTemp
        Next
    Next
End Sub

Open in new window

0
 

Author Comment

by:NiceMan331
Comment Utility
ok
i will check the file on sunday
cause we are off now for 2 days
thanx
0
 

Author Comment

by:NiceMan331
Comment Utility
hi
i searched , then i found this code

Private Sub cmdImport_Click()
On Error GoTo ErrHandler
   
   Dim oFSystem As Object
   Dim oFolder As Object
   Dim oFile As Object
   Dim sFolderPath As String
   Dim SQL As String
   Dim i, h As Integer
    DoCmd.SetWarnings False
    i = 0
   'h = 1
   For h = 1 To 35
   sFolderPath = "Q:\DAT\SALES" & h & "\"
    
 
  If FolderExists(sFolderPath) = True Then
   Set oFSystem = CreateObject("Scripting.FileSystemObject")
   Set oFolder = oFSystem.GetFolder(sFolderPath)
   For Each oFile In oFolder.files
     If Right(oFile.Name, 4) = ".dbf" And Left(oFile.Name, 3) = "sal" And Len(oFile.Name) > 11 Then
       SQL = "Insert into [sales]" _
           & " Select """ & Left(oFile.Name, 8) & """ as [Key], """ & sFolderPath & """ as [ST_K],""" & h & """ as [ST_id],*" _
           & " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
           & " IN """ & sFolderPath & """ ""dBASE 5.0;"""
       
       
       DoCmd.RunSQL SQL
       
       i = i + 1
    
     End If
   Next
   End If
   Next h
   MsgBox i & " dbf files were imported."
   DoCmd.SetWarnings True
   Exit Sub
   
ErrHandler:
   MsgBox Err.Description
End Sub

Open in new window



it works well , but i have some comments needed
1- i wish to know how manhy records where inserted after insert statemnet
2-i wish to code to test the file if it is corrupted then go next file
because the code often stopped when the file corrupted
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
1. change
DoCmd.RunSQL SQL
to
dbengine(0)(0).Execute SQL

After the statement executes, you can look at the dbEngine(0)(0).RecordsAffected property.

2. What constitutes corruption?
0
 

Author Comment

by:NiceMan331
Comment Utility
I don't know , but it sent me message that file may be corrupted
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
can you post the corrupted dbf file or a zipped version of it?
0
 

Author Comment

by:NiceMan331
Comment Utility
HI
i found it
the files was not corrupted
but cause the folder is under server , and due to security i think it not allowed
i copied all folders to my local drive , it works
but is very difficult now to always copy all folder every time
another think , how to use your suggesion regarding dbengine
how to call its proberty to know how many records inserted
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
after you make the call to run the SQL, you can assign or add the RecordsAffected property to a numeric variable.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 75 total points
Comment Utility
but is very difficult now to always copy all folder every time

Change your code to something like this:
   For Each oFile In oFolder.files
     If Right(oFile.Name, 4) = ".dbf" And Left(oFile.Name, 3) = "sal" And Len(oFile.Name) > 11 Then
       Filecopy sFolderPath & "\" & oFile.Name, environ("temp") & "\" & oFile.Name
       SQL = "Insert into [sales]" _
           & " Select """ & Left(oFile.Name, 8) & """ as [Key], """ & sFolderPath & """ as [ST_K],""" & h & """ as [ST_id],*" _
           & " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
           & " IN """ & environ("temp") & """ ""dBASE 5.0;"""
       Kill environ("temp") & "\" & oFile.Name

Open in new window

Basically copy the file to your temp directory, extract the data, then delete it.
0
 

Author Comment

by:NiceMan331
Comment Utility
can you post an example of using this RecordsAffected
0
 
LVL 45

Accepted Solution

by:
aikimark earned 275 total points
Comment Utility
replace line 28 in your script
      DoCmd.RunSQL SQL
with
      dbEngine(0)(0).Execute SQL

Add a line after line 28
       MsgBox "Number of rows inserted from " & oFile.Name & ": " & dbEngine(0)(0).RecordsAffected

Open in new window

0
 

Author Comment

by:NiceMan331
Comment Utility
ok , thanx aikmark
thanx for others also
0
 

Author Comment

by:NiceMan331
Comment Utility
i'm sorry for splitting the points , aimark help me alot , but cause the others also participates
so , i split alittle for them
thanx for all again
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now