jazzj
asked on
How do I use the SQLBulkCopy Class to transfer data from Excel to SQL server
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
Compile Error: User defined type not defined
it indicates the Dim TargetDatabase As ADODB.Connection line
ASKER
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.
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.
ASKER
This is the line where the error occurs:
TargetRecordset.Fields(Fie
I have checked my data types in SQL and make sure they match so not sure where to go from here.
TargetRecordset.Fields(Fie
I have checked my data types in SQL and make sure they match so not sure where to go from here.
ASKER
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
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
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.
ASKER
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.