• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

importing bulk dbf files TO oracle or access

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
NiceMan331
Asked:
NiceMan331
  • 22
  • 12
  • 6
  • +3
4 Solutions
 
als315Commented:
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
 
NiceMan331Author Commented:
thanx , but the request is full code , not to only find the directory , but to also import & append into a specific table
0
 
als315Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NiceMan331Author Commented:
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
 
als315Commented:
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
 
NiceMan331Author Commented:
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
 
als315Commented:
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
 
slightwv (䄆 Netminder) Commented:
What is the format of the DBF files?

You might be able to go straight to Oracle using SQL Loader.
0
 
NiceMan331Author Commented:
slightwv
i prefer to do that
the bdf file format , for sales transactions ,  is  :
sale_date,sale_qty,sale_amount
date / number / number
0
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
Just make sure anything you post or upload here isn't sensitive information.
0
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
NiceMan331Author Commented:
check
SALJAN01.txt
0
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
The file generated by dbase program
0
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
hi
here is real file
i just uploaded from my office
SALAUG12.txt
0
 
slightwv (䄆 Netminder) Commented:
OK, same suggestions apply.  This is a binary file.  If it is a DBase file, check the links I posted above.
0
 
NiceMan331Author Commented:
but sorry , in the link it is too long procedures , how can simplify it ?
0
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
NiceMan331Author Commented:
Ok good solution, but how to call that consultant ?
0
 
slightwv (䄆 Netminder) Commented:
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
 
aikimarkCommented:
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
 
MikeOM_DBACommented:
Here is an inexpensive tool: dbf2csv.
0
 
MikeOM_DBACommented:
All you need to code is a batch script that will convert all the dbf files to csv.
0
 
NiceMan331Author Commented:
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
 
aikimarkCommented:
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
 
NiceMan331Author Commented:
ok
i will check the file on sunday
cause we are off now for 2 days
thanx
0
 
NiceMan331Author Commented:
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
 
aikimarkCommented:
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
 
NiceMan331Author Commented:
I don't know , but it sent me message that file may be corrupted
0
 
aikimarkCommented:
can you post the corrupted dbf file or a zipped version of it?
0
 
NiceMan331Author Commented:
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
 
aikimarkCommented:
after you make the call to run the SQL, you can assign or add the RecordsAffected property to a numeric variable.
0
 
Jim P.Commented:
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
 
NiceMan331Author Commented:
can you post an example of using this RecordsAffected
0
 
aikimarkCommented:
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
 
NiceMan331Author Commented:
ok , thanx aikmark
thanx for others also
0
 
NiceMan331Author Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 22
  • 12
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now