How to 1) copy Excel data with ' ' to SQL and 2) how to copy data without creating duplicates?

I'm trying to create a macro in VBA to copy Excel data into SQL Server 2008 and came across 2 issues. The code is as  follows:

 Sub syncSQL()

    On Error GoTo EH

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim proj, inv, desc, edt, dt, time, details As String
    Dim sconnect As String
    Dim rs As New ADODB.Recordset
    Dim sSQLQry As String
    Dim ReturnArray

        With Worksheets("Entry Form")

       'Open a connection to SQL Server
        sconnect = "*connection settings*"
        conn.Open sconnect

        'Skip the header row
        iRowNo = 4

        'Loop until empty cell
        Do Until .Cells(iRowNo, 1) = ""
           proj = .Cells(iRowNo, 1)
           inv = .Cells(iRowNo, 2)
           desc = .Cells(iRowNo, 3)
           edt = .Cells(iRowNo, 4)
           dt = .Cells(iRowNo, 5)
           time = .Cells(iRowNo, 6)
           details = .Cells(iRowNo, 7)

        'Generate and execute sql statement to import the excel rows to SQL Server table
           ssqlstring = "insert into [table] ([Project], [Inv], [Desc], [Entry], [Date], [Time], [Details]) values ('" & proj & "', '" & inv & "', '" & desc & "', '" & edt & "', '" & dt & "', '" & time & "', '" & details & "')"
           conn.Execute ssqlstring

           iRowNo = iRowNo + 1
       Loop

       MsgBox "Data imported."

        conn.Close
        Set conn = Nothing

    End With
EH:

 MsgBox Err.Description

End Sub

Open in new window


For my SQL table, I'm using varchar(MAX) as data type.

1. When testing the macro, it can copy data to SQL just fine. Unless if there is ' ' used in the data columns, then an error message will appear. And the process will stop until that line. Anything else below it cannot copy.



At this point I'm wondering how to tell the macro to copy ' ' to SQL as well without raising any errors. Because I have thousands of rows of data, hundreds with ' '  and I don't want to manually remove them all.

2. Another issue I face is during the transfer to SQL. When I activate macro again, the transfer copies everything again from the 1st line onwards, even though those lines were already in the database, thus creating duplicates. What can I add to tell macro to only copy lines that are not in database?
Hans J.HauAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
1/ While creating your dynamic sql replace all occurrences of a single quote with yet another single quote. This means that all your single quotes should become two single quotes next to each other. In SQL Server these will be treated as single single quote. This is called escaping (in case you are unaware of the term). For example:

insert into [dbo].[tblMembers] ([MemberName]) values ('Lucy D''Souza');

Open in new window


2/ You must have some way of identifying record's uniqueness. I assume you do. Let us assume it is Project, then while constructing the SQL, you take care of checking the existence of record first. To continue with above piece of code...

if not exists (select 1 from [dbo].[tblMembers] where [MemberName] = 'Lucy D''Souza' ) insert into [dbo].[tblMembers] ([MemberName]) values ('Lucy D''Souza');

Open in new window


If you believe that above is too much, another approach would be, you create a unique index on the table with the column which is expected to be unique. Your SQL will fail with the error, you will need to handle and ignore that specific error in your VBA code.

Hope it helps!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You should not use reserved keywords as column names like Date, Time... etc.

In the following code, please check the Table and it's column names before testing it and tweak them accordingly.
You may try something like....

Sub syncSQL()

    On Error GoTo EH
    
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim proj, inv, desc, edt, dt, time, details As String
    Dim sconnect As String
    Dim rs As New ADODB.Recordset
    Dim sSQLQry As String, sSQLString As String
    Dim ReturnArray
    
    With Worksheets("Entry Form")
    
        'Open a connection to SQL Server
        'sconnect = "*connection settings*"
        sconnect = "Provider=SQLNCLI11;Server=SKTNEER-PC\SKTNEERSQL;Database=Dev;Trusted_Connection=yes;"
        conn.Open sconnect
        
        'Skip the header row
        iRowNo = 4
        
        'Loop until empty cell
        Do Until .Cells(iRowNo, 1) = ""
            proj = "'" & .Cells(iRowNo, 1) & "'"
            inv = "'" & .Cells(iRowNo, 2) & "'"
            desc = "'" & .Cells(iRowNo, 3) & "'"
            edt = "'" & Format(.Cells(iRowNo, 4), "yyyy-mm-dd") & "'"
            dt = "'" & Format(.Cells(iRowNo, 5), "yyyy-mm-dd") & "'"
            time = "'" & TimeSerial(Hour(.Cells(iRowNo, 6)), Minute(.Cells(iRowNo, 6)), Second(.Cells(iRowNo, 6))) & "'"
            'Replacing single quote with two single quotes
            details = "'" & Replace(.Cells(iRowNo, 7), "'", "''") & "'"
            
            '***************************************************************
            'Assuming the Table Name is "Project" and columns are...
            'Project
            'Ive
            'P_Desc
            'Entry_Date Date datatype
            'P_Date Date datatype
            'P_Time Time datatype
            '***************************************************************
            
            sSQLQry = "Select * From Projects where " & _
                            "Project = " & proj & _
                            " AND Inv = " & inv & _
                            " AND P_Desc = " & desc & _
                            " AND Entry_Date = " & edt & _
                            " AND P_Date = " & dt & _
                            " AND P_Time = " & time & _
                            " AND Details = " & details & ""


            rs.Open sSQLQry, conn, adOpenForwardOnly, adLockReadOnly
            
            'Insert new record if doesn't exist
            If rs.EOF Or rs.BOF Then
                'Generate and execute sql statement to import the excel rows to SQL Server table
                sSQLString = "insert into Projects (Project, Inv, P_Desc, Entry_Date, P_Date, P_Time, Details) values (" & proj & "," & inv & ", " & desc & ", " & edt & ", " & dt & ", " & time & "," & details & ")"
                conn.Execute sSQLString
            End If
            iRowNo = iRowNo + 1
            rs.Close
        Loop
        
        MsgBox "Data imported."
        
        conn.Close
        Set conn = Nothing
        
    End With
    Exit Sub
EH:
    
    MsgBox Err.Description
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BitsqueezerCommented:
Hi,

I would recommend not to use such SQL string to insert data into your table. If someone adds an SQL command like ";DELETE FROM [table];" into one cell it could easily injects this command into your code and your table data would be deleted completely.

instead, create a stored procedure on the SQL Server which has a parameter for each value to be inserted and then fill the parameters in VBA with the values and execute it. The SP on the server could also use some additional checks to avoid SQL Injection, but also filling the parameters would be better to avoid problems with special characters as here you do not need to create an SQL string, you would directly fill the parameter value from the cell value.

The stored procedure can then also resolve your second problem by first testing if the record already exists. If your table does not have a unique field for whatever reason you must of course test the existence of a record by comparing each parameter value with each field from the existing table, similar to the one Nitin has described above, only with more comparisons.

In general, I would not use Excel for such tasks, you should better use Access as frontend which would make all these tasks a lot simpler.

Cheers,

Christian
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Hans J.HauAuthor Commented:
@ Subodh Tiwari (Neeraj)

I tried using your solution. But it generated an incorrect syntax error on the first data in Project column.

Capture-1.JPG
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Debug the code manually by hitting the F8 key line by line and see when do you get that error? When line#54 is execute or when line#60 is executed?
Also print the sql string variables in the immediate window and examine them if they look correct. Cross check the column names.
Add two lines in the code just below the msgbox that will print those variables in the immediate window. Press Ctrl+G to open Immediate Window.

EH:
MsgBox Err.Description
Debug.Print sSQLQry
Debug.Print sSQLString

Open in new window

Hans J.HauAuthor Commented:
@Subodh Tiwari (Neeraj)

It goes to line 60 (on your code) and then goes to MsgBox Err.Description.

For the immediate window here is what it captured. (Note I have to change the content of the data due to confidentiality, however the state of the single quotes are left as it is)

select * from [IOT].[dbo].[DIE_ENTRY] where [Project No] = 'P00001' And [Inv No] = '170123' And [Description] = 'SA24S' And [Entry Date] = '2017-06-01' And [Date] = '2017-06-01' and [Time] = '9:00:00 AM' and [Problem + Repair Details] = 'Replace new die & Change pad ring' and [Status] = '3. Complete'

Open in new window


insert into [IOT].[dbo].[DIE_ENTRY]([Project No], [Inv No], [Description], [Entry Date], [Date], [Time], [Problem + Repair Details], [Status]) values (''P00001'', ''170123'', ''SA24S'', ''2017-06-01'', ''2017-06-01'', '9:00:00 AM', ''Replace new die & Change pad ring'', ''3. Completed'')

Open in new window


It would seem that several data ended up with double single-quotes. Is this supposed to happen?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Check the Insert query string where all the values are within two single quotes, that may be the problem.
The insert query which you get in the immediate window should work in the query editor in sql server if you try it there manually. Make sure to get one which also works in sql server so tweak your insert query in the code and see why you are getting two single quotes instead of just one.
aikimarkCommented:
@Hans

It appears that you have applied the Replace() function to all fields, not just the details field.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@aikimark
Even if that's the case, it would not add single quotes if there is no single quote already in the original values. It will just double the single quote only if it is found.

@Hans
I guess, you are still using your old Insert Into SQL string line#33 of your original code.
While I replaced it in the tweaked code. So just use the tweaked version of Insert query line#59 in the code I posted.
aikimarkCommented:
@Neeraj

If the Replace() function were applied to the SQL string instead of the details field, this would account for the duplicate apostrophe character sequences.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Aikimark

Yes, you are right.
Hans J.HauAuthor Commented:
@Subodh Tiwari (Neeraj)

You were right, I missed out on those single quotes. Now the data can move to Excel. But now there's a new problem.

Currently I have around 9700+ rows of data and the import process causes the Excel file to not respond for about a solid 3 minutes before it tells me import has completed.

Is there any way to make it faster?

Also is there any way to have the code allow edits on old data sync to SQL without creating a new row?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In my code, just after the Do Until line#24, add another line like
DoEvents

Open in new window

and see if that works for you.
Hans J.HauAuthor Commented:
@ Subodh Tiwari (Neeraj)

Is this for allowing edits on old data to sync to SQL? I tried it twice. It causes my program to crash.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
DoEvents is an Excel VBA command that temporarily pauses the execution of the macro to refresh the screen and execute any pending events in Excel. It can allow the user to interact with the Excel spreadsheet while the macro is running on the very same workbook! DoEvents can help a VBA program mimic asynchronous execution, which is per se not available in Excel VBA.

Since the code is interacting with other application, in this case sql server, I think you shouldn't use it so get rid of DoEvents.
For processing that much of rows of data, you need to be patient while data is being copied to sql table.
Hans J.HauAuthor Commented:
@Subodh Tiwari (Neeraj)

Ok... But since another division in my company is using this system, they won't  take kindly of the slow speed.

I'll need to modify the SQL, maybe only check for data from current date/week or something like that.

May it'll help reduce the amount to process. Although I'm not sure where to start...
aikimarkCommented:
Start a transaction and do a commit about every 500 rows.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.