Import/Export CSV File Via Stored Procedure
Jun 12, 2008
Reading through the forums, I found some great imformation for importing/exporting an excel spreadsheet via a stored procedure, however, the amount of data I have won't fit in excel. Can someone help me Import a CSV file via a stored procedure? and Export a CSV file via a stored procedure? I don't know if XML is the answer or if there is another way. For many reasons, I don't want to use DTS or Bulk. We are currently using SQL Server 2000.
Thank you,
Stacy
Thanks,
Stacy
View 1 Replies
ADVERTISEMENT
Jan 31, 2008
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
View 7 Replies
View Related
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
Aug 25, 2007
Hi frnz ,
i want to know how to import a CSV File and export CSV File From database SQLSERVER-2005 Using ASP.NET With C#
IF any one knows Help me
Vinnu
View 1 Replies
View Related
May 25, 2006
I'm using a DTS package to copy data from a comma delimited text file into a datatable. The text file has close to 160,000 records. The transfer fails at record 120,000. The error message says "Column Delimiter not found". The particular row in the .csv file seems to have nothing wrong with it as far closed quotes and missing commas are concerned. Even if I delete the row, the error is thrown at the same row.
Microsoft has acknowledged this as a bug in SQL Server 2000 and has a service pack to fix it. But installing SP4 is not an option for me at this time. The workaround on Microsoft websire about adding a character or a line didn't work for me.
Is it possible that there's some character or a marker in the text file that stays even if I delete the row? I don't know how to fix this problem. Does anyone here knows a workaround?
View 7 Replies
View Related
Oct 11, 2006
Firstly, I hope this question isn't asked too frequently but I found no existing reference to this situation....
I had a bunch of stored procedures in SQL 2k which imported and exported data to and from flat files using TEXTPTR, READTEXT, UPDATETEXT etc... The flat files were continuously changing so the filepath was a parameter for the sp.
The reason I used the pointer to flat files is because I didn't want to
load the files in memory before commiting them ie. with TEXTPTR and
UPDATETEXT I can import a 1Gb binary file 80000 bytes at a time and
keep (precious) memory usage down.
I was accessing this procs from a C# application.
Since these methods are going to be phased out by the guys at MS what is the best way of importing/exporting very large binary files in SQL 2005?
As far as I can tell SSIS requires a Flat File Source Manager object which needs a static filepath - not good.
Hope you can help,
Paul
View 10 Replies
View Related
Jan 9, 2008
Hi,
I need to export some data from SQL 2005 to a flat file, The data and flat file names will be dynamic and will be be fired programaticaly so I can't use DTS or SSIS.
In SQL2000 I did it using bcp, but that's quite a security hole so I don't want to use external utilities. I'll need to do something similar on another machine to import the data as well.
I find it strange there's no easy way to do this!
Thanks.
View 4 Replies
View Related
Dec 24, 1998
I needs export data on table to text file so I can process this data
with another database engine ie. Informix.
Can anybody help me to solve this problem ?
View 2 Replies
View Related
Aug 23, 2006
Hello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?
Thanks in advance.
View 1 Replies
View Related
Aug 15, 2006
Hello. I am making an application that works towards SQL Express database. These Stored procedures are saved in database. I need to use these SP in another database that I have.
Is it possible of exporting these and importing to my new database?
View 7 Replies
View Related
Jun 13, 2006
i am handling a project where user can choose the excel file and the field in the excel file to export into sql server 2005. which mean there will be dropdownlist where the user can choose the field and so on. anyone know how to do this?
View 2 Replies
View Related
Feb 5, 2007
Hi,
I'm trying to import text file (generated by UNIX - collation ISO LATIN 2) into the database using SQL SERVER 2005 Import and Export Wizard. I have got a problem with importing a decimal number, because in that column are not only decimal numbers (that's OK), but there are also spaces (not null, the column is filled by spaces and it looks like | |). When I'm trying import that file, then will occur the problem of truncation and import stops.
I can import that data by BULK INSERT, but I would like to import it by Import and Export Wizard at once without using subsequent conversions.
View 2 Replies
View Related
Jul 3, 2006
I have a requirement to create many SSIS packages and no datatransform is required so the BCP task looks a good contender providing it can do both import & export
is it possible to parse the values in bold as package variables into the BCP task. If so how?
BULK INSERT ipcs_wvg.dbo.extract
FROM 'D:IPCSextract.csv'
WITH (FORMATFILE = 'D:ipcsqueryextract.xml');
Thanks in advance
Dave
I have a global database called ETL Configuration for all my SSIS packages that uses a single table. So I can create three global variables
USE [ETLConfiguration]
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Here is the data I would put in here
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:IPCSextract.csv'
PackagePath = 'Package.Variables[User::gsFileName].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:ipcsqueryextract.xml'
PackagePath = 'Package.Variables[User::gsFormatFile].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'ipcs_wvg.dbo.extract'
PackagePath = 'Package.Variables[User::gsTableName].Properties[Value]'
ConfiguredValueType = 'String'
--Database connection info
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = '.mssql2005'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[ServerName]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'MyDatabaseName'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[InitialCatalog]'
ConfiguredValueType = 'String'
I have looked at lots of options to automaticly create SSIS packages and have a hunch that that simple can be better: All these solutions look way to complex to what I want to achieve--
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17484&SiteID=1
http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Create DataFlow Package Sample
http://msdn2.microsoft.com/en-us/library/ms161541.aspx
SMOTableList Sample
http://msdn2.microsoft.com/en-us/library/ms161564.aspx
View 4 Replies
View Related
Aug 29, 2006
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".
Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.
What am I missing here? I installed SP1 of SQL server 2005, but that did not help.
Thanks in advance.
View 1 Replies
View Related
Apr 23, 2006
Hi,In SQL 2000 if I wanted to take a complete copy of another running sqldatabase all did was create a new database locally and right-click itand select import and point to another database and click copyeverything (stored procedures as well) and it did it for. I can't seemto find the same functionality in SQL 2005. You can copy tables andviews but not the whole database. Is there another way of doing this?Our SQL database is hosted externaly and they recommend using theimport/export feature to do it. Does anyone know I can copy everything(such stored procedures, data table relations...etc)TanksMA.
View 1 Replies
View Related
Jun 21, 2007
Hi All,
I want to import a txt file data to a sql server database table, to do this i used sql server import and export wizard. In this when we choose a Data Source, the option Flat file source is not coming up in the combo box in the wizard.
I am using sql server 2005, Management Studio to do this.
steps 1. right click on the database --> all tasks --> import data --> sql server import export wizard --> choose data source dialog box....
please help me.
thanks in advance.
View 1 Replies
View Related
May 10, 2008
Can you export the results of a stored procedure using BCP?
If so, what is the syntax? If not, I was trying to convert my SP to a view, but it has a CASE statement within it and so I can't save a view with that (at least not as I've been trying).
Thanks,
Bob Larson
View 7 Replies
View Related
Mar 11, 2008
Hi,
I know how I can export a singe stored procedure to a file, clipboard etc..
But I have several stored procedures and I can't multi select them all
What's the best practice to export all stored procedures from a database?
View 2 Replies
View Related
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
Feb 26, 2006
I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...
e.g Sample XML file required...<Person><Name>Raymond</Name><NickName>The Legend</NickName></Person><Person><Name>Peter</Name><NickName>The King</NickName></Person>
sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Web Assistant Procedures', 1;GORECONFIGUREGOsp_makewebtask @outputfile = 'C:MyExportFile.xml', @query = 'SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS', @templatefile = 'C:Template.tpl'
View 1 Replies
View Related
Mar 2, 2005
hi,
I am new to Sql Server and I want export the stored procedures of my SQL Server 2000 database
to a flat file, so I can import it later to another SQL Server 2000 database (it is not anywhere in the network).
Can anyone explain me the easiest way to do that ?
Thanks !
View 2 Replies
View Related
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
Mar 31, 2006
Hi,
I have a SSIS package which exports data from a view in SQL database into Excel file. It was created by Export Wizard in SQL 2005 Server.
Now I would like to modify this package and change data source from view to stored procedure.
What component from toolbox should I use? it should be placed in Control Flow or Data Flow? And how connetc it with my Excel Destination?
thanks,
Przemo
View 3 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Oct 16, 2006
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
View 4 Replies
View Related
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
Nov 29, 2006
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
View 2 Replies
View Related
May 18, 2015
I have used BCP to perform this, but I now need an SSIS package. Is this possible to use an SSIS package to automate the task?
View 1 Replies
View Related
Jul 20, 2005
I must import some exemplary file to database (MS Srrver 2000) ofcourseusing procedure Transact SQL.This file must:1.Read the xml file2. Create table3. Import this date from xml file to my databasePs. I create procedure who File xml imports to base, but unfortunately sheonly schedule when earlier create a table or table is created.So I need (Ithink) create such mini parser in language transact SQL.Does someone have some ideas?For every help Thanks==== example file xml ===========================================<root><Cust><IDosoby>1</IDosoby><Imie>Lukasz</Imie><Nazwisko>Przypadek</Nazwisko></Cust><Cust><IDosoby>2</IDosoby><Imie>Dariusz </Imie><Nazwisko>Mroz</Nazwisko></Cust><Cust><IDosoby>3</IDosoby><Imie>Tomasz</Imie><Nazwisko>Kolo</Nazwisko></Cust></root>================================================== =========--Luk
View 3 Replies
View Related
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
Jul 20, 2005
I have a table which contains approx 3,00,000 records. I need toimport this data into another table by executing a stored procedure.This stored procedure accepts the values from the table as params. Mycurrent solution is reading the table in cursor and executing thestored procedure. This takes tooooooo long. approx 5-6 hrs. I need tomake it better.Can anyone help ?Samir
View 2 Replies
View Related
Oct 1, 2001
Hey,
can one of you please show me how to import data from a text file into a temp table in a stored proc.
thanks
Zoey
View 1 Replies
View Related
Oct 5, 2007
I'm trying to export data from a SQL Server 2005 database to a DB2 database through SSIS. However, I keep getting an error that says "Could not retrieve table list" with Invalid Conversion. SQLSTATE=07006. Does anybody have any ideas or what the problem could be?
-Kyle
View 7 Replies
View Related
