Unzip and Import CSV-changing filenames? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Unzip and Import CSV-changing filenames?

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-05-02 : 13:52:08

A *.z file is daily FTPed to the FTPRoot of my SQL Server box. It has a different filename every day. The filename format is

F_CCYY-MM-DD.log.z

The server has the unregistered Winzip 8.0 program on it.

I need to write something in DTS that will calculate the filename, unzip it to it's CSV format, then import it into an existing table each day. the files are quite large: 40,000 rows, 7 - 9 columns.

In the CSV file, there are 9 different row types--The rows either have 7, 8, or 9 columns. the last columns may have junk in them like numbers separated by commas.

Please advise the optimal method of import in your opinion.

Thank you so much,
Shaun M.
The City of Seattle

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-02 : 14:34:03

Take a look here:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=15151

Also look at Dynamic SQL:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

You can use dynamic SQL to create the appropriate file name based on the date, then build the BULK INSERT command to incorporate it. You *should* be able to execute Winzip using xp_cmdshell, but you'll have to check if it has any command-line parameters that will perform the unzip WITHOUT opening the window list (you cannot use xp_cmdshell to execute a program that requires user interaction) If it doesn't, then you'll have to find another way to get Winzip to run.

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-05-14 : 14:09:29

Here is my answer:
.Z files are written by GZIP. I had to avoid WinZip and their new commandline interface altogether.
Therefore, I used the following code:


CREATE PROCEDURE stp_Unzip 
@Date_In  DATETIME
 AS
/*
    Making the following assumption:
    - The files that come from the UNIX system are GZIP formatted
*/
SET NOCOUNT ON
DECLARE @Filename1 VARCHAR(250)
DECLARE @Command1 VARCHAR(250)
DECLARE @Result1 INT
DECLARE @Dir VARCHAR(30)

-- The local directory that is receiving the FTP output from the UNIX system:
SELECT @Dir = 'D:\InetPub\ftproot\iProcess\'

--Calculate output filenames.  Input filenames are the same with a '.Z' on the end:

SELECT @FileName1= CONVERT (CHAR(10), @Date_in, 121) -- CCYY-MM-DD

SELECT @FileName1 = @Dir + 'RPMv1_' + @FileName1 + '_235800.log'

--Unzip using GNU Zip executable from [url]http://www.gzip.org[/url]:
--option d = decompress
--option a = 
--option c = send the output to STDOUT, which I pipe to the output filename

SELECT @Command1 = 'D:\GZip\GZip.EXE -dac ' + @FileName1 + '.Z > ' + @Filename1

EXEC @Result1 = master..xp_cmdshell @Command1 --will be =0 if successful, =1 if failure

SET NOCOUNT OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources