Import Flat File Data into Oracle with VB ADO

Bhavik
Bhavik used Ask the Experts™
on
Hello,

I have a bulk data in sequential file, which need to be imported in oracle database. At present,I have a sample application, which imports .csv/.txt file in to access database, like below, and i need similar code for import in oracle instead of access.

Public Sub ImportCSVData()

Dim m_cnCSV As ADODB.Connection

Dim strFileCSV As String
Dim strPathCSV As String
Dim strFileMDB As String

strFileCSV = "test.CSV"
strPathCSV = "C:\abcd"
strFileMDB = App.Path & "\Database\Data.mdb"

'  Create CSV Connection
Set m_cnCSV = New ADODB.Connection
With m_cnCSV
  .CursorLocation = adUseClient
  .ConnectionString = _
      "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
      "Initial Catalog=" & strPathCSV & ";"
  .Open
End With

'  Create Table

strSql = "SELECT * " _
        & "  INTO TEST_CSV_TABLE IN '" & strFileMDB & "' " _
        & "  FROM " & strFileCSV

m_cnCSV.Execute (strSql)
m_cnCSV.Close

End Sub

As complete import functionaly done in single execute statement, this method works very fast, compare to line by line read and export.

Can anyone provide equilant or better code, for similar task, to import data in oracle ?

Thanks and Regards.

Bhavik.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
you could check on this

title: Bulk importing into Oracle
source : http://www.experts-exchange.com/Q_20636686.html

-----------
SQLLDR is an oracle utility that has a command line interface. So you need to use the Win32 API function CREATEPROCESS or WINEXEC to run it.

The syntax for SQL LDR can be found on the interent.

Here is one resource explaining it's usage :

http://www-db.stanford.edu/~ullman/fcdb/oracle/or-load.html 
-----------

hope this helps a bit

Author

Commented:
Thanks bruintje,  I had seen that post earlier, but dont get much idea.  Can anyone else help me further ?
Top Expert 2006
Commented:
do you have the loader util? assuming you got oracle the loader should be there too

why not try to issue a

shell "sqlldr <yourName> control=<ctlFile> log=<logFile> bad=<badFile>"

from vb?
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Basically, my client is away and i may not have database access for more than once. His requirement may change time by time in future, so when format changes, i need to change data file and control file. If i am being able to handle all files, with vb only, i would be able to handle all files in future without any changes at server.

I can read flat file line by line and insert into oracle, but as volume of data is much, it might take a lot of time.

Any other ideas please .....

Author of the Year 2009

Commented:
Moderator, my recommended disposition is:

    Accept bruintje's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer

Commented:
Hi,

Please can you help me?

I am trying to import a .CSV file into an oracle table
using commands in a .BAT file which is scheduled to run overnight.

How would I go about doing this.

Please include an example of code for the .BAT file.

Best regards
Bhupinder

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial