[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I use the SQLBulkCopy Class to transfer data from Excel to SQL server

Posted on 2008-11-11
8
Medium Priority
?
1,040 Views
Last Modified: 2013-06-04
I have seen a ton of information on using the SQL BulkCopy Class to transfer data from Excel to SQL server but for the life of me I am just not getting it. I have tried to change the code that I have found but to no avail. I am brand new to SQL and I am using the Express edition that comes with the latest version of Visual Studio. Okay here is what I am trying to do. I have an old Access db that I need to transfer to a SQL backend. All of the data that I need to move into the SQL tables are in Excel spreadsheets. I have a SQL table called Collinfo that has 8 columns (QID - primary key - int, CompID - foreign key - int, CollQ - varchar (500), UnAnc - varchar (500), AAnc - varchar (500), EAnc - varchar(500), ExperienceReq - bit, Selected - bit). I need to take the data from an Excel spreadsheet with the exact same column names and blast it into SQL. I have over 500 rows in the Excel files so I don't want to recreate this thing by hand. What I need to know is step by step how do I make this happen. I am new so please leave nothing out - I need to know things as trival as where to paste the code in SQL (e.g. new class or new query) - think trying to teach a monkey to drive a car. Thanks - any help is much appreciated.
0
Comment
Question by:jazzj
  • 5
  • 2
8 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 22934060
Here is code I use to dump a table of data into a SQL Server table. It assumes there is no header row and the fields are in the same order. It also assumes the table starts in cell A1 on the active sheet.

Private Const TargetServerName As String = "Server1"
Private Const TargetDatabaseName As String = "Database1"
Private Const TableName As String = "Table1"

Public Sub WriteTable()
   
    Dim TargetDatabase As ADODB.Connection
    Dim TargetRecordset As New ADODB.Recordset
    Dim Record As Long
    Dim Field As Long
   
    ' Open target database connection
    Set TargetDatabase = New ADODB.Connection
    TargetDatabase.CursorLocation = adUseClient
    TargetDatabase.Open "Provider=SQLOLEDB;Data Source=" & TargetServerName & ";Initial Catalog=" & TargetDatabaseName & ";Integrated Security=SSPI"
   
    ' Delete existing records
    TargetDatabase.Execute "DELETE FROM dbo." & TableName
   
    ' Open target recordset
    TargetRecordset.Open TableName, TargetDatabase, adOpenDynamic, adLockPessimistic
   
    ' Copy records
    For Record = 1 To ActiveSheet.UsedRange.Rows.Count
        TargetRecordset.AddNew
        For Field = 1 To TargetRecordset.Fields.Count
            TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)
        Next Field
        TargetRecordset.Update
    Next Record
   
    ' Close recordset
    TargetRecordset.Close
   
    ' Close database
    TargetDatabase.Close

End Sub

Kevin
0
 

Author Comment

by:jazzj
ID: 22936198
Kevin,

Thanks for the quick response. Where do I put this code in SQL. Do I paste it into a new query?  if not where and how? Also I am assuming I just need to have the Excel spreadsheet open but don't need to specifically reference the Excel file name - is that correct? Does the following look correct for my specific situation"

"Server 1" = RYN\sqlexpress
"Database1" = Collinfo.dbo
"Table1" = Collinfo_T

Thanks for the help.


0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 22936520
The code is VBA code and is placed in the Excel workbook, not SQL Server.

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Kevin
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:jazzj
ID: 22936669
That explains alot. Okay I ran it and it gives me the following error message.

Compile Error: User defined type not defined
it indicates the Dim TargetDatabase As ADODB.Connection line
0
 

Author Comment

by:jazzj
ID: 22936806
Kevin,

Scratch that. I selected the activeX library from the references and that seem to take care of the problem. Now the error I get is this:

Multiple Step Operation Generated Errors. Check each Status Value.
0
 

Author Comment

by:jazzj
ID: 22936915
This is the line where the error occurs:

TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)

I have checked my data types in SQL and make sure they match so not sure where to go from here.
0
 

Author Comment

by:jazzj
ID: 22944882
Kevin,

you're awesome. Your code worked perfectly. A couple things I needed to do in SQL to get it to work:

- delete my primary designations and reset the identity specification
- change some data types for text data
0
 

Expert Comment

by:app_G
ID: 39217649
Was just curious if  anyone has sample code that does this same thing - but in DAO instead of ADO?  And from an Access 2007 database instead of Excel?   I'm just needing  to load a table or query that is in Access to a table in the SQL Server but with speed of the SQLBulkCopy class.   The conventional Access append query is just too slow.  And 'Pass-Through' Append queries will not work with Access queries or tables when your attempting to append data from them.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question