import csv file by calling store proc
-
Hi,
I need to create a small program to import csv file and export it back with fewer data.
For example when we import file contains A-L, when I export back only A,C,F,H
How do we import csv file in SQL server by calling store proc? Is it possible we give the user flexibility to choose the file path by themelves or we have to specify it.
And how do we export it back?
Is it possible to use visual studio for the front end. I am thinking that user only has 2 buttons imports and exports buttons.\
Please help
Thanks,
Susan
-
Since DTS may or may not be something you are familiar with (couldn't tell in your posting) I'll give an ugly crash course.
Use DTS (Data Transformation Services) to create two DTS Packages.
[From books on line (BOL); "A DTS package is an organized collection of connections, DTS tasks, DTS transformations, and workflow constraints assembled either with a DTS tool or programmatically and saved to Microsoft® SQL Server™, SQL Server 2000 Meta Data Services, a structured storage file, or a Microsoft Visual Basic® file."]
Basically in enterprise manager right click your database go to all tasks and select import or export data. Use BOL to assist in building your package (or if you're thirsty for knowledge a great book is "Microsoft SQL Server 2000 DTS Step By Step" by Carl Rabeler)
You will need to create two DTS Packages that you will save to SQL Server (see BOL). One DTS Package will be created to import your data (A-L) and the other will be to export your data (A,C,F,H).
If you are familiar with DTS Package designing you should use the transformation options here to massage your data, if not, build a Stored Procedure that will massage the data.
Now to your question:
Below are two procedures that you can use to execute DTS pakages from T-SQL. Both will work and is up to your preference, the first uses the dtsrun options and the second uses the OLE Objects.
This assumes that you have specified the import file path for the user in your code and have not made it configurable.
If you want to make the DTS Properties configurable and are proficient with Visual Basic (now to your second question of using Visual Studio, absolutely you can) save the DTS Package as a Visual Basic file (.bas) that you can modify and mold into a complete application. Your VB Application will actually execute the DTS Package so the below procs won't apply here.
The Procedures
---------------------------------------------------
-- DTS RUN
---------------------------------------------------
CREATE PROCEDURE spExecDtsPkg
@PkgName nvarchar(255) = '%'
,@PkgPWD nvarchar(255) = ''
,@ServerHostname nvarchar(255) = ''
,@ServerLogin nvarchar(255) = ''
,@ServerPWD nvarchar(255) = Null
AS
DECLARE @vcExecSQL VARCHAR(8000)
SET @vcExecSQL = 'master.dbo.xp_cmdshell ''dtsrun /S'+@ServerHostname+' /U'+@ServerLogin+' /P'+@ServerPWD+' /N'+@PkgName+' /M'+@PkgPWD+''''
EXEC (@vcExecSQL)
---------------------------------------------------
-- OLE Object
---------------------------------------------------
IF EXISTS(Select * from sysobjects where id = OBJECT_ID(N'spExecDTSPkg'))
DROP PROCEDURE spExecDTSPkg
GO
CREATE PROCEDURE spExecDTSPkg
@PkgName nvarchar(255)
,@PkgPWD nvarchar(255) = ''
,@ServerHostname nvarchar(255) = '(local)'
,@ServerLogin nvarchar(255) = 'sa'
,@ServerPWD nvarchar(255) = Null
AS
DECLARE @hr INT
DECLARE @oPKG int
DECLARE @output varchar(255)
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0 GOTO HandleErr
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer', NULL, @ServerName=@ServerHostname,@ServerUserName=@ServerLogin,@ServerPassword=@ServerPWD,@PackageName=@PkgName,@Flags=0,@PackagePassword=@PkgPWD
IF @hr <> 0 GOTO HandleErr
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0 GOTO HandleErr
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0 GOTO HandleErr
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0 GOTO HandleErr
RETURN
HandleErr:
RAISERROR('Failed to execute package, execute the package from MS Enterprise Manager for specific details',16,1)
RETURN
Hope this helps!
Jason
-
-
Or you could utilize the OPENROWSET function. Example for XLS file below. Read in BOL about CSV file.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\someserver\somepath\somefile.xls', 'select * from [Sheet1$b35:q50]')
where f1 in (''a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l')
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT ColumnList FROM [SheetName$]') select ColumnList from SQLServerTable where field1 in ('a', 'c', 'f', 'h')
N 56°04'39.16"
E 12°55'05.25" -
I'm having difficulties trying to run the DTS package from a procedure. I've tried both methods listed above, but to varying results.
The first returns an error, stating that the package does not exist. After looking into this further, I've found that it could quite possibly be due to the fact that apparently no one ever updated our server to use the latest service packages. Said server packages supposedly fix a bug that would potentially cause the error I'm recieving.
In the second method, I get a message that the DTS package failed and that I should try to run it via enterprise manager to see what the error might be. I had tried it previously and seen it work and it worked just fine after recieving the error as well. As I am incredibly new to databasing in general, I'm not quite sure what's happening in the script and therefore can't really be sure why I would get the error. My crazy, wild guess is that it also isn't being detected here.
Any help would be much appreciated.
-
I guess BULK INSERT and OSQL would be way to complicated, huh
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
When I say new to databasing, I mean heavy emphasis on the new. At the time, executing the DTS package seemed more straightforward to and also easier to understand. In any case though, I worked it out and it turned out to be a communications problem with the server. Thanks for the help.
-
Depending on your security setup and what version of SQL you're running you could use the good ole Bulk Copy (BCP) Utility (http://msdn2.microsoft.com/en-us/library/ms162802.aspx)
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Susan,
Cole kinda hijacked your post... do you still need help or are you all set?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks all.
I get the idea what should I do.
Thanks heaps
-
All apologies, I just try to keep like questions in the same place instead of opening up a new topic.
-
Not a problem, Cole... "hijacked" was probably the wrong word... just wanted to make sure the original poster got what she needed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No prob.
I can learn other thing too at the same time
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply