View Complete Post
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
'firstname.lastname@example.org' 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.
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 ?
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
guys, i am new to sql development
my question is how do i import data from an excel table,
i was trying to do it via a query and just paste the values, but received a syntax error. thanks
data structure is:
I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.
How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?
When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?
Dim DBConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\vb.net\2210201\new marks analysis system\new database\stu_basic.xls;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
Dim SQLString As String = "SELECT TOP 1000 * FROM [Sheet1$]"
Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
Dim DBReader As IDataReader = DBCommand.ExecuteReader()
dg.DataSource = DBReader
i am using this but its doing nothing .
no errors , no data from excel.
whats the problem in it.?
i am loading Data From one my Excel file to DataTable. My Excel file has data in Following Format:
I'm stroing data into DataSet from excel file using Oledb provider in asp.net c# application.
query: SELECT * FROM [Sheet1$]
everything is working fine but my concern is that 1st row should be skiped and 2nd row should be used for heading and rest for datarow.
I need help on urgent basis.