Insert Procedure From Excel

Sep 29, 2007

Hi all

I need to create a stored procedure that will insert from an Excel document. And I'm not exaclty sure of how to do that.


CREATE PROCEDURE [Insert_ActiveSuspensions]

AS
INSERT INTO [GamingCommissiondb].[dbo].[License_SuspensionsView]
([TM #],
[FIRSTNAME],
[LASTNAME],
[SS #],
[REASONFORSUSPENSION],
[ENDDATE]
[BEGINDATE])


SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM "C:Documents and SettingsDesiree StevensonMy DocumentsTerminationInserts.xls"
IF @@Error <> '0'



is this correct??

View 7 Replies


ADVERTISEMENT

Excel Range Insert

May 16, 2008



Hi I was wondering if anyone else was running into the problem where SSIS inserts data into a range incorrectly.

I have a Raw File Source in a Data Flow. The data is manipulated and then defined to insert into a named range within an Excel Worksheet. I have a task prior to the data flow that takes a template and copies it to another location. Within the Template I have defined several named ranges.

The Data Flow inserts the data in the line below the named range. For instance if I have a named range defined as B1:C1
the insert occurs on B2:C2

Does anyone know why this is happening or how I can get it to insert into the Range I have specified?

Thanks in advance!

View 3 Replies View Related

Need Help With Insert Or Update From Excel

Nov 26, 2006

Hello -

I have an Excel data source from a customer. The data source is a daily dump from the customer's HR system - bascially data about people. Some of these are new (to the data source) and some already exist in my database (from a previous import).

What I am trying to do is: If the row in Excel is new, then INSERT the data into my table. If the row in Excel already exists in my database, then I just want to UPDATE the data in my table.

The Excel data contains an ID field from the HR system that I store in my table for lookups. However, I also generate an ID from my table, when the data is inserted.

I have a Lookup task that uses the HR system ID from the Excel file and this will return the rows that already exist. These are the rows I want to update. One question is: Do I use the OLE DB Command task for this operation?

Second, how do I determine the rows that don't exist? I am assuming that the Lookup task only returns those rows that match. And, unlike the conditional split, there doesn't appear to be another path that the unmatched data can be sent to.

thanks

- will

View 4 Replies View Related

Insert Into Statement From Excel File

May 16, 2006

Hi all.
I am having trouble with an insert statement involving an excel file.

Insert into tpa00175 (intJobKey,BusinessFormKey,chrCustPONumber,chrExem ptNo,chrJobDescription1, chrJobName,
chrJobNumber,chrNxtVariationNum,chrUserDef1,chrUse rDef2,hrUserDef3,chrUserDef4,chrUserDef5,chrUserDe f6,chrUserDef7,
chrUserDef8,chrUserDef9,chrUserDef10,chrUserDef11, chrUserDef12,chrUserDef13,CompanyID,CustKey,dteCmp ltnDueDate,
dteCompletionDate,dteJobCommencement,dteReviewDate ,intClientContctKey,intDivisionKey,intEmployeeKey, intJobClassKey,
intMasterJobKey,MSTaxClassKey,numActualVarCosts,nu mActualVarSales,numCostsRecognized,numCreditLimit, numJobBudget,
numJTDActualCost,numJTDActualSale,numJTDCommitedCs ts,numJTDEstimatedCst,numJTDEstimatedSal,numLaborA ctual,
numLaborEstimate,numLaborForcast,numLastForcastCos t,numLastForcastSale,numMaterialActual,numMaterial Est,
numMaterialForcast,numOverHeadActual,numOverHeadEs t,numOverHeadForcast,numPercentComplete,numRetenti onAmtTTD,
numRetainageAmt,numRetentionBldTTD,numRetainagePrc nt,numSalesRecognized,numUser1ActlClass,numUser1Es tClass,
numUser1FrcstClass,numUser2ActlClass,numUser2EstCl ass,numUser2FrcstClass,numUser3ActlClass,numUser3E stClass,
numUser3FrcstClass,OpenOrClosed,PostIMGL,Retention AcctKey,siInvoiceLayout,siJobStatus,siJobType,siMa rginType,
siProfitRecMethod,siRetainageType,STaxClassKey,tiP rofRecByJobLine,TranID,UpdateCounter,WhseKey)
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:Book1.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')


its a long one for sure. 2 questions.

1)Is there a way to just say insert all rather than naming each row like the above example.

2)With this above example i am getting a syntax error near 'From'. What am I missing??

Thanks alot.

Tibor

View 10 Replies View Related

How To Insert Excel Data Into SQL Database?

Dec 24, 2006

Hello to All,

I'm searching a way get Excel data into SQL database and tried this "insert" process that given me error. Already create a table call "original_purged" contain column fields. Can anyone give me some tips to show the problem?

INSERT Original_Purged

SELECT OP_ID,RBDI,Title,Address,City,State,Zip,Plus4,Walkseq,Crrt,Endorse,City_rural,Dpb,Dpbc,updatedate
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:Original_Purged.xls";User ID=Ryan;Password=dellonee1405;Extended properties=Excel 5.0')...[52117639]
==========================================================
Error occur.............

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].


thank you
ryan,


RV

View 5 Replies View Related

Insert Data To Excel Through Execute Sql Task

Jun 7, 2006

How can i insert data into an excel sheet using an Execute Sql Task? is it possible?

View 1 Replies View Related

Update Or Insert Into SQL Table From Excel Source

Aug 29, 2006

Hi all,



I've had little success gooling/searching for this (so far).



Given a simple spreadsheet:

StoreNumber StoreName

1
UPDStoreName_1

2
UPDStoreName_2

3
UPDStoreName_3

4
NEWStoreName_4



I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))

StoreNumber StoreName

1
StoreName_1

2
StoreName_2

3
StoreName_3

5 StoreName_5



.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :



StoreNumber StoreName

1
UPDStoreName_1

2
UPDStoreName_2

3
UPDStoreName_3

4
NEWStoreName_4

5 StoreName_5



(the UPD and NEW are added to simplify the example).



Now the default action of an excel source into an ole db destination is
an insert into the table - so PK constraints causes failures.



Now, given that the table is referred to by other table, and is in a
24x7 website, how do I change the SSIS package such that, on a
row-by-row basis, anUpSert (update or insert) is performed?



The only idea I have so far is:



create temp table

insert excel data into temp table

iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP



Isn't there a better way?

View 7 Replies View Related

Create, Insert And Format Into 1,000 Excel Files

Feb 24, 2008

Hello,

I need to create about 1,000 (literately) Excel files that each contain 5 tabs. The data being placed on the tabs will always be the same (meaning the columns are static).
I am fairly advanced at Excel VBA so I can write code that does all the following in Excel (looped 1,000 times):


Open an Excel template

Bring data in from the tables

Filter, then copy-paste the appropriate rows into each tab.

Save the new Excel file.

Email the file to appropriate individual (it is a Microsoft Exchange Server).
As I started this in VBA, I thought that I might be able to do it with SSIS. My concern is I need to have the rows formatted (font, border, etc.) and the number of rows change.

My questions are:
Is it possible to format Excel with SSIS?
Can I email the files even if it is not with an SMTP protocol?
Would SSIS process this data faster then Excel?
Does this approach even make sense? Am I better just doing it with VBA?


Thank you for the help.

-Gumbatman

View 4 Replies View Related

Transact SQL :: Insert Data Into Excel Using OPENDATASOURCE

May 14, 2015

While trying to insert data into existing XLS file, using below command, i am getting following error.

Insert into OPENDATASOURCE( 'Microsoft. ACE.OLEDB.12.0','Data Source=e:ediuploadhello1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
Select top 50 product_no From product_mst
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". Unknown provider error.

View 2 Replies View Related

Problems Trying To Insert Data Into The Database Using Excel

Oct 31, 2007


Hi all,

I have encountered a problem. In the past i run a sql query to select all the data from the excel file and insert them into my SQL database. However recently i encountered an error when i run the query.

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "excel_ls" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "excel_ls".

How i solve this error. Any ideas?


Thanks

View 1 Replies View Related

Transact SQL :: Incremental Primary Key Insert From Excel To DB?

Jul 6, 2015

I have a table Employee which has following values:

Employee ID Employee Name
1                     A
2                     B
3                     D

Now I have a excel file which will have the new data and I have to write a stored procedure to insert data from Excel file to this table.

Where Employee Id should be auto calculated and the value shud insert from 4.

View 9 Replies View Related

Transact SQL :: Raw Excel File - Create Automated Insert Into DB

Sep 17, 2015

I have a raw excel file which I am trying to create an automated insert into a DB.

Data in excel file is two columns, Row_Data, Row_ID

I am trying to split rows and store them in columns in a table.

Row_ID column is simply row identifier column

E.g.:

Row_Data  Row ID
Frank          2
Seattle            3
WA           4
34     5
1010.00   6

How can i insert this data into a table where i`ll have columns of Name, City, State, Age and Amount?

View 18 Replies View Related

Can We Use Excel File (source) For Bulk Insert Task

Jun 1, 2007

Can an excel file be used as a source for a Bulk Insert Task in SSIS instead of a delimited flat file?

View 6 Replies View Related

(urgent)Insert Data From A Excel Spread Sheet To SQl Server

Oct 4, 2007

Hi,
 
  I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called
Received an invalid column length from the bcp client for colid 1.
 
How should i fix it.
Regards
Karen

View 7 Replies View Related

SQL 2012 :: Insert Outstanding Totals Into Different Columns In Excel Sheet

Jul 21, 2014

I have a data table which has list of bills with date and outstanding total.

If the bills dates between 0 and 30 I like to insert the Outstanding total into Colum K ,
If the bills dates between 31 and 60 I like to insert the Outstanding total into Colum L ,
If the bills dates between 61 and 90 I like to insert the Outstanding total into Colum M and so on ..

I tried below formula but it is not working

CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')

View 9 Replies View Related

DB Engine :: How To Insert Excel File Data Into Temp Table

Jul 9, 2015

I have an Excel file with .csv extension . it has on sheet with name Sheet1.

Now, I'm trying to insert this Excel data into one #temp table. I tried with syntax:

----------------
Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1; 

[Code] ...

But, I'm getting error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

If I'm executing for .xls file this statement is working finr and rows are inserted into #temp table. How to take excel file of .csv extension??

View 3 Replies View Related

Power Pivot :: Insert Hierarchy Slicer In Excel 2010?

Sep 23, 2015

We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?

View 5 Replies View Related

Export To Excel Using A Stored Procedure

Mar 5, 2008

I am looking (and I have looked everywhere) to run a SP and get the data from it and open an Excel spreadsheet (manual column property editing is fine and preferred) and allow the user to save it wherever they want. 
 I am using VS 2003
 Thanks in advance,

View 4 Replies View Related

Excel File & Stored Procedure

Aug 5, 2002

Anyone have the code used in DTS to make a connection to Excel? Or perhaps a better idea? I need to pass different file names to a stored procedure which would upload the spreadsheet data into SQL7 on demand.

View 1 Replies View Related

Import From Excel In Stored Procedure

May 2, 2007

I'm trying to automate a data entry process. We get annual or semi-anual fee schedules from our clients. Sometimes it's an excel file, sometimes it's csv or other text, sometimes it's from the web. We clean up these files a bit and then import them to a new table in a sql server database. Then someone writes a custom insert to take specific columns from that temp table, do some transformations, and put them in the correct place in the normal database. Then the imported table is deleted or archived. We'll still have to do the clean up phase, but I should be able to automate most everything after the import.

What I want to know is, can I pass a filename to a stored procedure, along with perhaps a few other parameters, and have the procedure import data from that file? If so, how would I go about it?

View 2 Replies View Related

Procedure To Transfer Data From Excel To SQL

May 1, 2008

Hi

I have an excel file that I would like to put into SQL so I can run queries on it. Is there any way I can create a procedure that will pull my info from my excel file to SQL? Any suggestions would be helpful.

Thanks!

Wendy

View 4 Replies View Related

Excel Source And Stored Procedure

Oct 4, 2007

Hello,
I'm a SSIS beginner.

I need to create users in my database from an Excel file source.
I have an Excel source with user name and email.

I would like to use SSIS to go though the Excel file and execute a store procedure (create_user @name @email @password OUTPUT) for each row and then create an output file with the new created password (a random password is created for each user and the stored procedure has an output @password)

I tried to "plug" an "Excel source" to an "Execute SQL task" which execute my store procedure.
But as a SSIS beginner I don't really understand how to pass the parameters (name and email in the Excel file) to my stored procedure. There is the concept of variable a for each loop thing... but i dont really know how to start.

I would be greatfull if someone could tell me a solution to my problem.

Cheers

Fabrice

View 4 Replies View Related

Stored Procedure/Query Results To PDF Or Excel

May 2, 2007

I'm looking for a tool that can schedule stored procedures or queries and save the results to pdf or excel (preferrably both). SQL Server 2000, so 2005 reporting services are out, though if that has the capability I'd like to know because that might convince some of the powers that be to upgrade.

View 4 Replies View Related

Export Procedure Results To Excel Dynamically

May 16, 2007

Hi,

I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.

The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.

Can this be done using DTS or do i have to try another method?

thanks
scott

View 1 Replies View Related

Stored Procedure In Excel From Sql Server 2005

Jan 3, 2008

I need to run a SQL Server 2005 stored procedure by pressing a command button in excel 2003. Basically, I want stored procedures run from Excel to get the view/queries from sql.

Searched and tried many solutions here and in other web resources, read about opening the datasource, but I cannot find the exact solution for my problem.

Any help would be greatly appreciated,
Thanks

View 4 Replies View Related

EXcel Sheet Into Database With Stored Procedure

Jun 24, 2006

Hi,What is the syntax of storing the content of an Excelsheet in a predefinedtablewith a stored prcedure.Arno de Jong, The Netherlands.

View 1 Replies View Related

Export In Excel With Using OPENROWSET From Stored Procedure

Nov 22, 2007



Hi
I am Utarsh Gajjar.
I am working on SQL Server 2005.

I have following Stored Procedure.

----------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ExportInExcel]
@QueryString VarChar(8000) =''
AS
BEGIN TRY

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Customers.xls;IMEX=1','SELECT * FROM [Sheet1$]')
EXEC (@QueryString )


END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(8000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH

-------------------------------------------------------------------------------

Error is

-------------------------------------------------------------------------------
(0 row(s) affected)

Msg 50000, Level 16, State 2, Procedure ExportInExcel, Line 31
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.

(1 row(s) affected)



how can i solve this problem?


Thanks in advance.

View 16 Replies View Related

Exporting Stored Procedure Results To Excel

Nov 9, 2007

I'm new to Integration Services and going through the learning challenges one often faces when employing unfamiliar technology. I've created a simple package that executes a parameterized stored procedure and returns the results (multiple rows) into an object variable. How do I export the results to an XLS file? Do I have to use a script task to do this?

Regards,
Orlanzo

View 12 Replies View Related

SQL Stored Procedure DOES NOT Execute Using ADODB In Excel VBA

Nov 15, 2007

Hi to All,

I have the codes below in excel VBA that always returns a "-1" value of the rs.RecordCount. The sql connection is open, BUT IS NOT ABLE TO EXECUTE THE STORED PROCEDURE. Anyone please I need help ;(

Database: SQL Server 2005
Programming language: VBA (Excel 2003)


Dim cn as new ADODB.Connection
Dim cmd as new ADODB.Command
Dim rs as new ADODB.Recordset
Dim connstr as string

connstr = "Provider=SQLOLEDB;" _
& "Data Source=myDataSource;" _
& "Uid=sa;Pwd=mysource;"_
& "Initial Catalog=Emp;"

With cn

.ConnectionString = str
.CommandTimeout = 0
.Open
End With


cmd.CommandText = "sp_LoadEmp"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cn

Set rs = cmd.Execute

If rs.RecordCount > 0 then

< my codes here >
End If



My Stored Procedure:

SELECT * FROM Emp WHERE EmpAge > 30

View 13 Replies View Related

Exporting Data From SQL Server To Excel From A Stored Procedure

Oct 1, 2004

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

Thanks

View 1 Replies View Related

Stored Procedure - Import CSV File Into Table In Excel

Mar 3, 2014

I have the following code to import .csv file into my table in excel. It's being inserted into a table. dbo.ImportedPromoPricing. Table and the .csv file have 3 fields price, code and selling price.

Once import is completed I want to use the data in my dbo.ImportedPromoPricing to update another table dbo.MasterPricing. Records need to be compared and updated or appended if needed. in case of update only price will be updated. this is the beginning of my code

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[ImportPromoPricing] Script Date: 03/03/2014 14:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] .....

View 2 Replies View Related

SQL 2012 :: Executing Parameterized Stored Procedure From Excel

Aug 12, 2013

I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.

Create a new connection to SQL Server, then in the Connection Properties dialog, specify

Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"

but if I want to pass a parameter, I would normally do something like

SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()

but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)

View 9 Replies View Related

Stored Procedure To Import Excel Sheet Content To

Oct 17, 2007

Hi,
I have created a stored procedure which imports the contents of the excel sheet into the sql server. I am using the syntax as
"declare cur_Inoperative cursor for Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0; Database=D:Nupur_GRAPV2.0Inoperative.xls',Sheet1$) order by 1".
as of now I am hard coding the database path inside the cursor, but i need to pass this path as stored procedure input parameter. Passing the path is working but i am not able to use that passed parameter inside the cursor. can anyone help me in replacing the actual path with the input path variable.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved