View Complete Post
Private Sub DoMSExcel(ByRef ds As DataSet)
Dim selectConnection As New OleDbConnection
selectConnection.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & Me.myFilePath & "; Extended Properties=""Excel 8.0;""")
enumerator = Me.TablesMapped.Keys.GetEnumerator
Do While enumerator.MoveNext
Dim dataSetTable As String = Conversions.ToString(enumerator.Current)
Dim sourceTable As String = Conversions.ToString(Operators.ConcatenateObject
I downloaded the Delimiter File Read and followed the instruction and installed it. The file is on the correct directory (>:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents)
The file DelimitedFileReader.dll is there.
However, when I go to BI Development Studio and try to add the new task I can't see it in the SSIS Data Flow Items...it is not visible. Does anyone know why I can add/see it?
I have a web app where user uploads a file.
File is saved in server locally and I want to read some data from it.
Currently my development server has office 2003.
So do I need office 2007 in order to be able to read an excel from office 2007 ?
What are other requirements ?
I tried a test, using
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();Microsoft.Office.Interop.Excel.Workbook wrkBook; // = new Microsoft.Office.Interop.Excel.Workbook();wrkBook = excelApp.Workbooks.Open(@"C:\www\excel_files\b.xls", ....);
But When trying to open the file I get the error:
Exception from HRESULT: 0x800A03EC
So I'm wondering what I'm missing.
My requirement is that i upload an excel file then i need to validate that data.
1. Uploading file using using upload control saving binary in SqlServer
2. Then i am creating file from binary data on Sql Server using below command and then using OPENROWSET to dump data to Sql table
3. Then reading Sql Table row wise and validating data.
Alter Procedure spUploadExcelFile
Declare @VarBin varbinary(max)
Declare @FileName varchar(100)
DECLARE @sql nvarchar(MAX)
DECLARE @errMsg nvarchar(MAX)
DECLARE @ObjectReturn INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @ObjectToken INT
Select @VarBin = Content , @FileName = [FileName] from MultilangBinaryData Where PKID = @PKID
SET @FileName = 'C:\' + @FileName
EXEC sp_OACreate 'ADODB.Stream' @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @VarBin
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
SET @sql = 'INSERT INTO dbo.UserBulkData SELECT 0,NULL,'+ @BatchI
I am importing the data from excel file using following code.
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;" conn = New OleDbConnection(connstr) Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]" Dim cmd As New OleDbCommand(strSQL, conn) Dim da As New OleDbDataAdapter(cmd) da.Fill(datatable)
Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.
eg. The excel column value Column1
Before I start, I'm using SQL 2008.
I have a Excel file with email addresses that need to acts at input parameters to a Lookup transformation. I have set the Excel Source to my file and specified the email field to be the output. I have dropped the Lookup Transformation Data Flow and connected
the both. I'm going to execute a very simple stored procedure, and under the Connections section my SQL query looks like follows: EXEC Test_GetUserName ?
When I run that I get an error saying that no parameter was provided. But when I run EXEC Test_GetUserName
'email@example.com' everything executes great, for the obvious part that the email is hard coded. How do I pass the excel input as the parameter?
Thanks for all the help.
I want to read Excel document using C#. I am not a professional so I need examples to learn the technique. If anyone provides useful link, information, api library, tutorial it will be very helpful to me.
Thank you very much.
Hello Every One,
I have a scenario in which i am executing a Stored Procedure which returned a result set.
I want to create a Excel file [name_date_time] dynamically, and fill result set data into Excel file. And then finally sent Excel file to Specific user[Email ID].
Point is that the excel file should have unique name so i want to give name as name_date_time.
So every time when package run new file is created with unique name.
Can any one suggest me who to create Excel file and fill result set data into Excel file.
And then finally send that Excel file ?