SQLTeam.com | Weblogs | Forums

How to export excel data to an existing SQL file without appending


#1

Hi There,

I am a new SQL user and trying to export an excel file to sql after importing SQL file in excel and then, if I modify that in excel, and again export that file in SQL ;it's appending records to the existing SQL file. But I wanted to replace that file ;unfortunately it's not working.

The following code is working perfectly while exporting to SQL but it's not replacing data. Any help would be highly appreciable.

Sub Button1_Click()

Dim con As New ADODB.Connection
Dim iRowNo As Integer
Dim sPeriod, sMERC_ID, sLink_ID As String
Dim sql As String

Dim sql As String

   'Open a connection to SQL Server
    
      ServerName = "10.38.46.74"

'Setting ConnectionString
    con.ConnectionString = "Provider=SQLOLEDB; " & _
            "Data Source=" & ServerName & "; " & _
            "Initial Catalog=Dare_Work;" & _
            "User ID=au\chowdhsy; Password=; " & _
            "Integrated Security=SSPI;"

'Setting provider Name
con.Provider = "Microsoft.JET.OLEDB.12.0"
    
 'Opening connection
 con.Open
 With ThisWorkbook.Sheets("Export")
    'Skip the header row
    iRowNo = 2
        
    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        sPeriod = .Cells(iRowNo, 1)
        sMERC_ID = .Cells(iRowNo, 2)
        sLink_ID = .Cells(iRowNo, 3)
        'Generate and execute sql statement to import the excel rows to SQL Server table
         
         sql = "insert into dare_work.daredbo.ExcelDemo (Period, MERC_ID, Link_ID) values ('" & sPeriod & "', '" & sMERC_ID & "', '" & LINK_ID & "')"
         
             
        iRowNo = iRowNo + 1
          con.Execute sql
    Loop
        
    MsgBox "Customers exported."
  
    con.Close
    Set con = Nothing
         
End With

End Sub

Regards,
Mou


#2

The better way to upload Excel to SQL Server is using SSIS. In your case, you can use the import/export wizard. Basically, you save the xlsx file somewhere, run the wizard to upload it to sql, specifying that it should delete all the existing rows first.

If you stil want to do it in VBA, send the command

DELETE dare_work.daredbo.ExcelDemo

before you begin inserting rows. However, note that inserting one row at a time like that will be slow. Won't matter if only a few hundred rows, but if you get many thousands, you'd be better with SSIS.


#3

Thanks a lot for your reply! But if I use DELETE dare_work.daredbo.ExcelDemo in the following code then, in sql it's only picking the last row.e.g. out of 12 rows it's picking up only 1 row in SQL. Not sure why? Any help would be really great!

Sub Button1_Click()

Dim con As New ADODB.Connection
Dim iRowNo As Integer
Dim sPeriod, sMERC_ID, sLink_ID As String
Dim sDATE_LOG_IN  As Date
Dim sql As String

   'Open a connection to SQL Server
    'conn.Open "Provider=SQLOLEDB;Server=10.38.46.74;Data Source=SQL2008;Initial Catalog=Dare_work;Integrated Security=SSPI;"
    
      ServerName = "10.38.46.74"

'Setting ConnectionString
    con.ConnectionString = "Provider=SQLOLEDB; " & _
            "Data Source=" & ServerName & "; " & _
            "Initial Catalog=Dare_Work;" & _
            "User ID=au\chowdhsy; Password=Spring10; " & _
            "Integrated Security=SSPI;"

'Setting provider Name
con.Provider = "Microsoft.JET.OLEDB.12.0"
    
 'Opening connection
 con.Open
 With ThisWorkbook.Sheets("Export")
    'Skip the header row
    iRowNo = 2
        
    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        sPeriod = .Cells(iRowNo, 1)
        sMERC_ID = .Cells(iRowNo, 2)
        sLink_ID = .Cells(iRowNo, 3)
        sDATE_LOG_IN = .Cells(iRowNo, 4)
        
        'Generate and execute sql statement to import the excel rows to SQL Server table
         
         con.Execute "DELETE dare_work.daredbo.ExcelDemo"

         sql = "insert into dare_work.daredbo.ExcelDemo (Period, MERC_ID, Link_ID,DATE_LOG_IN ) values ('" & sPeriod & "', '" & sMERC_ID & "', '" & sLink_ID & "','" & sDATE_LOG_IN & "' )"
        
        iRowNo = iRowNo + 1
          con.Execute sql
    Loop
        
    MsgBox "Customers exported."
  
    con.Close
    Set con = Nothing
         
End With

End Sub


#4

Hi There,

Thanks to the power infinity!!!!

Regards,
Mou


#5

move the "DELETE dare_work.daredbo.ExcelDemo" to before the LOOP


#6

:grinning: that's what you need


#7

Thanks to the power infinity khtan! It's working perfectly.


#8

Hi There,

Is there any option to export comma,& related text variables from excel to SQL.
Though I have declared that variable String but it's not exporting to SQL.

Any idea.

Thanks,
Mou