July 24, 2013 at 9:37 pm
Can anyone please tell how to import data from an excel file to sql server table in SSIS using a stored procedure. I have an excel connection manager which is used by an excel source in the data flow. I don't know where to go from here. Thanks!!
July 24, 2013 at 11:03 pm
create procedure _sp_importexceldata
(@Source varchar(1000)
, @SourceSheet varchar (100)
, @DestinationTable varchar (100))
as
declare @retval int
EXEC master..xp_fileexist @Source, @retval output
if @retval = 0
begin
print 'file does not exist.'
return
end
if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]'
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'
if @DestinationTable is null or @DestinationTable = ''
set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) +
convert(varchar, getdate(), 126)
exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @Source + ''', ' + @SourceSheet + ')')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 25, 2013 at 1:30 am
Just a small remark: Henrico's SP is great, but you cannot use it in the Excel Source with an Excel connection manager. You need to launch the sp from an Execute SQL Task with a connection manager pointing to your DB.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 25, 2013 at 1:40 am
If you already have an Excel connection manager and a data flow, you don't need to use a stored procedure - not to do the actual importing, anyway. What are you trying to do - move everything from an Excel workbook into a table with the same column structure?
John
July 25, 2013 at 8:52 pm
Thank you guys for responding.
The spreadsheet has 160 columns with one row for each column. The destination table has a primary key called "ID" which is not on the spreadsheet.
The 1st step is to get the "ID" from another table where the columnname matches the columnname on the spreadsheet.
The 2nd step is to insert the "ID" and the value of each column to the destination table.
How can I achieve this?
Thank you!
July 26, 2013 at 12:54 am
You can use the Excel Source in the data flow to read the Excel file (not with a stored procedure). Then you can use a Lookup component to find the corresponding ID and insert it into the destination with an OLE DB Destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 26, 2013 at 1:04 am
This will not work because the excel column header is a row in the table. For instance, the 160 column header is the same as the 160 rows in "Name" Column of the table. I will have to do some pivoting but I am not sure where this will take place
July 26, 2013 at 1:07 am
Can you give some sample data so we can see what we're dealing with?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 26, 2013 at 1:15 am
File sample data:
col1 col2 col3 col4 COL5
1.22 1.2475 1.395 1.6 1.275
The file will always have two rows with 160 columns.
The column names in the file is = to the result of this query: Select Name from table where id = 2
The lookup might only work if I can find a way to transpose the columns to rows and give the column name "Name"
July 26, 2013 at 1:33 am
Koen, is this data sufficient?
July 26, 2013 at 1:38 am
wanox (7/26/2013)
Koen, is this data sufficient?
I'll have a look later. I have to go to work now 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 26, 2013 at 1:42 am
You haven't shown what the data in your table looks like, but I think I can picture it.
It looks as if you have three options:
(1) Pivot in Excel then load directly into the table
(2) Pivot as part of your data flow and load directly into the table
(3) Load into a staging table, then do a T-SQL PIVOT into your table
John
July 26, 2013 at 2:12 am
I cannot pivot in excel (source data).
I have tried to pivot as part of my data flow and have not been successful. Can you help me out here please
The data in the table looks like this:
ID Date Value
10 getdate() 1.357
The ID is generated by running a query to select name from another table where name matches the column name on the spreadsheet.
July 26, 2013 at 3:08 am
wanox (7/26/2013)
I cannot pivot in excel (source data).I have tried to pivot as part of my data flow and have not been successful. Can you help me out here please
The data in the table looks like this:
ID Date Value
10 getdate() 1.357
The ID is generated by running a query to select name from another table where name matches the column name on the spreadsheet.
Can you post a few more lines of sample data and the desired output?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 26, 2013 at 5:40 am
This is a sample of data destination:
SourceRateIDRateDateEnteredDateTimeValue
17/25/201300:00.00.0005
27/25/201300:00.00.0013
37/25/201300:00.00.0027
47/25/201300:00.00.0037
57/25/201300:00.00.0042
107/25/201300:00.00.225
117/25/201300:00.00.225
127/25/201300:00.00.225
137/25/201300:00.00.225
147/25/201300:00.00.225
This is sample of source excel file:
onemoaccepttwomoacceptthreemoacceptsixmoaccepttwelvemoacceptcadprimetwoyrtreas
1.221.24751.2751.3951.6#NAME?1.15
Thanks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply