Loading Multiple Excel Sheets In To Different Tables In A DB
Nov 21, 2007
Hi All,
I have problem in loading multiple excel sheets data in to according to that excelsheets tables in a DB.
All the excel sheets are in a folder,from that folder i have to acces all excel sheets.
For this i am unsing script task and one dataflow task.
But the error is coming in script task i am not able to put the path in the script..
Is this the correct way to do like this? Or any other way?
Can u please tell me the solution for this..Thanks in advance who are responding to this mail...
We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?
Actually I've got multiple questions - but will start with 1. :)
My task in three words: Use Grand Prix data and make it so that you can ask questions to it via SQL Server or by using Excel.
What I did so far: I gathered the GrandPrix data from f1db.com - I stored the data in multiple Excel Files - I created a Formule1 database - I created separate packages for all the data. Which means, im cloning the data from Excel to SQL Server.
What I'm doing now: In SQL Server Management Studio I am creating new tables in which I store (using SQL queries) the data from the packages that I loaded into the Database. This is going very well so far - and I'm proud of that. ;)
The problem: Most Excel files have only 1 sheet and where the first row are the identifying column-names. I now however have 1 Excel files that exists of multiple sheets, perhaps 40 or something. I could create a package and run this which would result in 40 new tables in SQL Server Management Studio. I bet that would work just fine. But how - and here comes the main question - do I copy information from those 40 tables (former sheets) into 1 new table.
it's hard to explain what I want to be the result...
Ok, i made a screenshot and added some notes that makes the whole situation more clear: direct link: http://img243.imageshack.us/img243/9462/whatimtryingfx9.png
I suppose this requires a complex SQL Query, hopefully someone can help me out or point me into the right direction. Much appreciated!
I know how to use ssis to import data from the sheet in an excel file. How do you use ssis to get the data out from different sheets into the database? Is there such functionality please? Thanks
I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.
I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?
My report consists of 10 subreport and 1 main report. I want to export each subreport in separate sheets like Shee1 is Subreport1 and Sheet2 is Subreport2 etc... How can i do this.
I have a report , it's very simple . just only showing all records. I used the function RowNumber to show pagination. It 's ok that I export to PDF file. but when I exported to a excel , I find every report pagination export to multiple sheets in excel. I don't need this, I need show all data in one sheet,not multiple sheets.
I am trying to import multiple .csv files to excel sheets using Script task in SSIS. I have trouble importing the reference that allows us to read and write to excel sheets. Can anyone help me create a script task that will import multiple .csv files to excel sheets.
I had a procedure which returns 5 result sets. Now i want to export 3 results sets in 1 excel sheet and the remaining 2 resultsets in another excel sheet. Can we do this in reporting services.
at first let me specify my requirement. a) i have an excel file with more than one sheets b) i want to import data from that excel file into sqlserver 2000 using asp.net & c# NOW i need a program that automatically realize total sheets of excel file AND insert into seperate table please help me
I wanted to know if there was a way to import multiple sheets from one excel file into one sql table in one go.
I have an excel file which has 15 sheets and want to import them into one table in one process rather than having to create 15 table and then joining into one table.
I am using Sql Server 2005(enterprise edition) v9 SP2.
I have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container (I guess!).Â
Post data import, I have a set of T-SQL query that I plan to execute via Execute SQL Task.Â
The reason it is poorly designed is the table is used to hold questions and answers, all with a 1:1 relationship. Instead of having ID, ProductType, Question, Answer they have unfortunately adopted the approach of the above i.e:
id 1 thisID 3 parentid nuLL DESCRIPTION: this is a question
id 20 thisID 3_1 parentID 3 DESCRIPTION: this is the answer to the question above
So I am writing a sproc that does this using a temp table. I got this far:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Spencer -- ============================================= ALTER PROCEDURE [dbo].[GetFAQs] -- Add the parameters for the stored procedure here @ProductType varchar(255) AS BEGIN SET NOCOUNT ON; -- Insert statements for procedure here CREATE TABLE TEMP ( IDINT, thisIDvarchar(50) null, parentIDvarchar(50) null, Titlevarchar(255) null, DescriptionQvarchar(8000) null, DescriptionAvarchar(8000) null, ProductTypevarchar(255) null, )
SELECT ID, thisID, parentID, Title, DescriptionQ, DescriptionA, ProductType FROM A2Z WHERE ProductType = @ProductType AND parentID IS NULL END GO
This gets all my questions for that product type.
What I need to do is load the questions into my temp table and then run through the a2z table again gaining the answers to the questions (the parentid holds the question ID). The answers then will also get loaded into the temp table.
I am trying load data from multiple Foxpro tables which are under a folder. I can have multiple folders with 17 foxpro tables. I was able to do it in DTS using ActiveX script. Here is the ACtiveX script.
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Option Explicit Dim conObj,DSNGosfbill,comObj,objRs,HostServer Dim sFolder,sFileFolder, Details,subFolderoccur,sFileFolderDBF,sFileFolderFPT,CheckFile,dFiles,Fil Dim fso, folderObj,subFolderList,dFolderObj Dim objPackage,oStep,objPackage_1,oStep_1,ConnObj_001,ConnObj_004,ConnObj_031,ConnObj_032,ConnObj_033 Dim ConnObj_Hclaimb, ConnObj_HProv, ConnObj_Hids, ConnObj_HCodes, ConnObj_HSpan, ConnObj_002, ConnObj_HCHGB Set conObj = CreateObject("ADODB.Connection") HostServer =DTSGlobalVariables("gvServer").Value
set comObj=CreateObject ("adodb.command") set comObj.ActiveConnection =conObj Function Main() Dim Dir_Name,DirFlag Dir_Name = "" DirFlag = "N" Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(checkFile) Then Else Details = "***** Success.Lst file is missing in Batch folder. BATCH job may not be successfull or there are no folders in UNZIP directory to process. Check the batch run.*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Set folderObj = fso.GetFolder(sFolder) Set subFolderList = folderObj.SubFolders For Each subFolderOccur in subFolderList DirFlag = "Y" Dir_Name = subFolderOccur.Name Call Process_Dir(1,subFolderOccur.Name) Next If DirFlag = "N" Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If If DirFlag = "Y" Then Call Process_Dir(2,Dir_Name) If objRs.Eof Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If While not objRs.EOF set sFileFolder = fso.GetFolder(sFolder & objRs("zip_file_name")) Details = "***** Start-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log Call Update_Process_Flag("L",objRs("zip_file_name")) '*******Execute the package for each directory****************' '********* Call the Package**************' Set objPackage = CreateObject("DTS.Package") Set objPackage_1 = CreateObject("DTS.Package")
Set ConnObj_001 = objPackage.Connections("SSIPATH001") ConnObj_001.DataSource = sFileFolder
Set ConnObj_002 = objPackage.Connections("SSIPATH002") ConnObj_002.DataSource = sFileFolder
Set ConnObj_004 = objPackage.Connections("SSIPATH004") ConnObj_004.DataSource = sFileFolder Set ConnObj_031 = objPackage.Connections("SSIPATH031") ConnObj_031.DataSource = sFileFolder Set ConnObj_032 = objPackage.Connections("SSIPATH032") ConnObj_032.DataSource = sFileFolder Set ConnObj_033 = objPackage.Connections("SSIPATH033") ConnObj_033.DataSource = sFileFolder
Set ConnObj_Hclaimb = objPackage.Connections("SSIPATHCLAIMB") ConnObj_Hclaimb.DataSource = sFileFolder
Set ConnObj_HProv = objPackage.Connections("SSIPATHPROV") ConnObj_HProv.DataSource = sFileFolder Set ConnObj_Hids = objPackage.Connections("SSIPATHHIDS") ConnObj_Hids.DataSource = sFileFolder Set ConnObj_HCodes = objPackage.Connections("SSIPATHCODES") ConnObj_HCodes.DataSource = sFileFolder Set ConnObj_HSpan = objPackage.Connections("SSIPATHSPAN") ConnObj_HSpan.DataSource = sFileFolder
Set ConnObj_HCHGB = objPackage.Connections("SSIPATHCHGB") ConnObj_HCHGB.DataSource = sFileFolder
objPackage.Execute For Each oStep In objPackage.Steps If oStep.ExecutionResult = DTSStepExecResult_Failure Then Details = "***** GOSFBILL_SSI_Staging_Load failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
For Each oStep_1 In objPackage_1.Steps If oStep_1.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Update_FileSource failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
'********************************************' Details = "***** End-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log objPackage.Uninitialize objPackage_1.Uninitialize Set objPackage = Nothing Set objPackage_1 = Nothing sFileFolder = "" sFileFolderDBF = "" sFileFolderFPT = "" objRs.MoveNext Wend objRs.Close End If Call Close_Conn Main = DTSTaskExecResult_Success End Function Sub Process_Dir (Para_cntl,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Dir" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Para_Cntl")= para_cntl comobj.parameters("@Dir_Nm")= Dir_Name comobj.parameters("@File_Type")= "SSI" If (Para_Cntl = 1)Then comObj.Execute() Else If Para_Cntl = 2 Then Set objRs = comObj.Execute() End If End If
End Sub Sub Update_Process_Flag(P_Flag,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Flag" comObj.commandtype = 4 comObj.parameters.Refresh comObj.parameters("@Process_Flag")= P_Flag comobj.parameters("@Dir_Nm")= Dir_Name comObj.Execute() End Sub Sub Write_Log comObj.CommandText ="dbo.usp_etl_write_log" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Text")= Details Comobj.parameters("@NDC_SSI_IND")= "SSI" Comobj.parameters("@Process_Stage")= "Staging" comObj.Execute() End Sub
Sub Close_Conn Set comObj = Nothing Set objRs = Nothing conObj.Close Set conObj = Nothing Set fso = Nothing Set folderObj = Nothing Set subFolderList = Nothing End Sub
When I migrated this code to SSIS, its not working. How can I achive this functionality in SSIS. Any one pls help me.
I have multiple xml data file in a directory say C:XMLData abc1.xml, abc2.xml, abc3.xml etc.
Need to loop through each file in ssis with Foreach loop container, and get the file name say abc1, and load the data of abc1.xml to abc1 table in sql server DB.
Next iteration will pick up the abc2.xml and find the abc2 table in sql server DB then insert the data in abc2 table.
While each iteration, xml source should also point each xsd file correspondingly.
 Tables are already created in DB
I solved my problem up to getting the file name from ech iteration and assigned file name to variable, in oledb destination data access mode I select Table or view name variable, then corresponding table will get selected for data insertation.
Just wanted to know how can I read each xsd file for each xml data files while iteration.Â
I need to make a gigantic collection of excel sheets searchable from a web interface.I need entire rows fetched into a webpage depending on the users query.Mind you I'm not searching the names (filenames) of these excel files, butthe contents inside the excel file. E.g. in a file test.xls, if I search for the word"test1" from the web interface, the entire row in the excel file containingthe word test1 should be displayed.One way out is to dump the contents of the excel sheets into a database, andsearch the database via ASP.For this, can anyone tell me how to efficiently dump the contents of an excelsheet into SQL server?Or if anyone can suggest an alternate strategy for searching this mammothcollection of excel files' contents, if would be great.Thanks a lot.
i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data
and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server
like wise i have many xl files ( which have many sheets ) .
for eg:
excel file 1:
-> sheet 1
-> sheet 2
-> sheet 3
excel file 2:
-> sheet 1
-> sheet 2
-> sheet 3
excel file 3:
-> sheet 1
-> sheet 2
-> sheet 3
now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package
When I export a report which has one of its columns as quanity, whose value is -1/0 which denotes (dozens/pieces), I get an error #DIV/0!..Will making two seperate columns for the dozens and pieces help or is there any other way to avoid this error being displayed in the report?
How to extract data from 3 excel sheets (same excel doc having multiple sheets with different # of columns & rows) using SSIS 2008. The end result will be 6 tables loaded in the database.
Hi allI need to add data from two Excel sheets (both on the same workbook)to an existing table in my SQL DB.The problem is that each sheet holds different fields for the samerecord, though the records are sorted by row numbers.(I had to split the fields to different sheets because Excel has alimit of 256 fields in each sheet)My sheets are quite large (~55,000 rows and 200 columns each) and I'llhave to repeat this action many time, so some kind of an automationwill nice (VB, DTS package?)I am new to SQLServer, so an example will be most helpful.Many thanks,Ilan
I am getting 3 sheets in an excel file. But only the sheet1 contains data and the rest are blank. I want to delete Sheet2 and Sheet3 from excel file using SSIS.
Hi, Would like some help on how do I go about coverting an Excel File with columns of info into my SQL Server Database. The excel file will be uploaded from a user from my web application. I completely have no idea on where to start so any form of help is much appreciated thanks.
Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.
