Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    21

    Unanswered: VBA & DB2 - import .csv file

    Hi,

    I created a VBA code (found on this forum) to connect with DB2 from MS Excel. Now I want to perform an import command directly to DB2 table.

    Code :

    Code:
    Option Explicit
    
    Dim DBCONSRT, QRYSTR As String
    Dim DBCON, DBRS  As Object
    
    Dim excel_app As Object
    
    Private Sub DB2_Connect()
        Set excel_app = CreateObject("Excel.Application")
        DBCONSRT = "Driver={IBM DB2 ODBC DRIVER};Database=OFFROAD;hostname=localhost;port=50000;protocol=TCPIP; uid=db2admin; pwd=sample123"
        'CHANGE THE BELOW QUERY STRING ACCORDING TO YOUR NEEDS
        QRYSTR = "db2 import from C:\Users\db2admin.Luka-PC\Documents\auth.csv of del insert into db2admin.razpored"
    
        Set DBCON = CreateObject("ADODB.Connection")
        DBCON.ConnectionString = DBCONSRT
        DBCON.Open
    
        'BELOW CODE USED TO GET THE DATABASE CONECTION AND EXECUTE THE QUERY CHANGE ACCORDING TO YOUR NEEDS
        Set DBRS = CreateObject("ADODB.Recordset")
        With DBRS
            .Source = QRYSTR
        Set .ActiveConnection = DBCON
            .Open
        End With
        
    End Sub
    When I run the code I get an error (which I posted in screenshot) !

    Problem is that import command is not an SQL statement (It's a db2cmd command). How can I write SQL statement, so that import will work ?

    P.S.: .csv is separate Excel file from where macro is executed, you can see path of .csv in red coloured line of upper code.

    Please, anybody, HELP
    Attached Thumbnails Attached Thumbnails Error.jpg  

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use the ADMIN_CMD() stored procedure.

    http://www-01.ibm.com/support/knowle...3-6-1-3-0-0-10
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2014
    Posts
    21
    Hi n_i,

    I'm sorry but I'm a newbie in DB2, so I think I'll have problem with understanding everything on that link. Besides, aren't that db2cmd commands ?

    I tried that in above VBA code, but I got an error as provided in attached screenshot - VBA doesn't allow me to use "\" character which I need for .csv file path that needs to be imported. That red coloured syntax of db2cmd command in my previous post works fine in db2cmd, but not in VBA code.

    I think I have to include SQL statement in code for importing...Or can you show me an example ?

    Thanks for reply !!
    Last edited by Lukael; 10-06-14 at 10:20.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure what your question is. There is an example of the procedure call for IMPORT in the link I posted earlier. Are you asking how to escape backslashes in VB? How to call a procedure in VB? Something else?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2014
    Posts
    21
    I'm asking both actually. I want to perform import command to DB2 from .csv file, but VBA is returning me errors, in this case because of backslashes. Maybe I even call procedure incorrectly, but I checked here and It looks same as mine : http://www.connectionstrings.com/ibm-db2/

    So, I think problem is within that red coloured line in 1# post - a line for SQL statement....I tried different varieties of SQL syntax from link you provided and others, such as :

    - " IMPORT FROM MyCSVfile OF .csv"

    - "db2 load from C:\Users\db2admin.Luka-PC\Documents\auth.csv of del insert into db2admin.razpored"

    - "LOAD FROM MyCSVFile OF .csv"

    None of these work, but maybe I did It wrong, I am a newbie in SQL and DB2...I know something about VBA though.

    P.S. : VBA is executed from a file, which creates .csv in another file path.I can also send you a sample of my .csv and Excel file with VBA code If you like, but I cannot attach .xls file in this forum !
    Last edited by Lukael; 10-07-14 at 14:04.

  6. #6
    Join Date
    Oct 2014
    Posts
    21
    Me again, this time with great news, I managed get It working

    Here's code for anyone else in future :

    Code:
    Option Explicit
    
    Dim DBCONSRT, QRYSTR As String
    Dim DBCON, DBRS  As Object
    Dim excel_app As Object
    Private Sub CSVtoDB2()
        Set excel_app = CreateObject("Excel.Application")
        DBCONSRT = "Driver={IBM DB2 ODBC DRIVER};Database=OFFROAD;hostname=localhost;port=50000;protocol=TCPIP; uid=db2admin; pwd=sample123"
        
        'CHANGE THE BELOW QUERY STRING ACCORDING TO YOUR NEEDS, THIS ONE IMPORTS CSV FROM FILE PATH INTO TABLE CALLED RAZPORED
       
        QRYSTR = "Call SYSPROC.ADMIN_CMD ('IMPORT FROM C:\Users\db2admin.Luka-PC\Documents\auth.csv OF DEL INSERT INTO db2admin.razpored')"
              
        Set DBCON = CreateObject("ADODB.Connection")
        DBCON.ConnectionString = DBCONSRT
        DBCON.Open
        'BELOW CODE USED TO GET THE DATABASE CONECTION AND EXECUTE THE QUERY CHANGE ACCORDING TO YOUR NEED
        Set DBRS = CreateObject("ADODB.Recordset")
        With DBRS
            .Source = QRYSTR
        Set .ActiveConnection = DBCON
            .Open
        End With
        
    End Sub
    Red coloured line is where problems were. You have to call ADMIN_CMD for QUERY, so n_i a BIG thanks for that clue, I just had to dig It up a little and play with characters in VBA
    Last edited by Lukael; 10-08-14 at 11:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •