February 20, 2018 at 5:56 am
How to import a Excel file into SQL table directly?
Before, I used vb.net to import Excel file into a datatable and then bulkcopy into SQL table.
February 20, 2018 at 5:59 am
I want to create a stored procedure to complete it.
February 20, 2018 at 6:24 am
adonetok - Tuesday, February 20, 2018 5:59 AMI want to create a stored procedure to complete it.
Why going with SQL queries where you have a SSIS instead ? Any explanations ?
February 20, 2018 at 6:33 am
Because my windows project needs more steps and import Excel file is only one step of many steps.
February 20, 2018 at 6:36 am
adonetok - Tuesday, February 20, 2018 5:59 AMI want to create a stored procedure to complete it.
Check out this forum post
😎
I've also written a procedure/framework that reads the spreadsheet files directly, will probably publish that as an article on SSC soon.
February 20, 2018 at 8:15 am
adonetok - Tuesday, February 20, 2018 5:56 AMHow to import a Excel file into SQL table directly?
Before, I used vb.net to import Excel file into a datatable and then bulkcopy into SQL table.
I assume you need adhoc distributed query.
1. You probably need to install AccessDatabaseEngine for X64
2. you need to enable it in configuration.
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
3. You probably need to change OLE properties AllowInProcess and DynamicParameters to 1
4. Then you can operate Excel in the following way.
INSERT INTO [TARAGET TABLE IN MSSQL]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=%EXCEL FILE WITH PATH%;', 'SELECT * FROM [SheetName$]')
GASQL.com - Focus on Database and Cloud
February 20, 2018 at 1:25 pm
Just as an FYI - you can pass a datatable to a stored proc. Not sure if this is what you are looking for or not.
--create table type
CREATE TYPE [dbo].[MyDataTypeTable] AS TABLE(
[Id] [INT],
[Col1] [VARCHAR](50) NULL
)
--use it in a stored proc
CREATE PROCEDURE [dbo].[MyProc]
@Data dbo.MyDataTypeTable READONLY
AS
BEGIN
--do something
END
Then add a sql parameter and set the db type as structured.
DataTable data = FillDataTable();
SqlCommand objSqlCommand = new SqlCommand(StoredProcName, objSqlConnection)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter tvpParam = objSqlCommand.Parameters.AddWithValue("@Data", data);
tvpParam.SqlDbType = SqlDbType.Structured;
objSqlCommand.ExecuteNonQuery();
February 20, 2018 at 2:35 pm
adonetok - Tuesday, February 20, 2018 5:56 AMHow to import a Excel file into SQL table directly?
Before, I used vb.net to import Excel file into a datatable and then bulkcopy into SQL table.
It's actually really easy. You just need a couple of things...
1. Download and install the "ACE" drivers including a minor bit of configuration and a special "trick" to load it without having to unload all 32 bit applications.
2. Enable "Ad hoc distributed queries".
3. Write a simple query with OPENROWSET. This may be the catch for some... it's the thing that requires the "Ad hoc distributed queries" thing and some DBAs are as paranoid about using that as they are in using xp_CmdShell. As a bit of a side bar, neither should be feared when used properly and it's simple to use either or both properly when using stored procedures.
We could also use OPENDATASOURCE instead of OPENROWSET but it takes twice as long to execute.
If that's acceptable, post back and I'll provide some details.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply