?
Solved

UNZIP .txt.z files using SQL server tool

Posted on 2006-05-13
15
Medium Priority
?
2,562 Views
Last Modified: 2012-05-05
Hi experts,

I have 72 .txt.z files in c: \data\ftp_download folder and need to unzipped to the same folder. I have winzip pro and command line add- on installed in my PC, because Wzunzip support .zip files only. Is any simple way can unzip them by using any SQL server tools?
Thanks.
/*****/
DECLARE @FileName VARCHAR(50)
DECLARE @SQL VARCHAR(2000)

   set @FileName = '*.txt.z'
   SET @SQL ='C:\Progra~1\WinZip\wzunzip -yb -o C:\DATA\FTP_Download\' + @FileName + ' C:\DATA\FTP_Download\'
EXEC master..xp_cmdshell @SQL
/*** output***/
  End-of-central-directory signature not found.  Either this file is not a Zip file, or it constitutes one disk of a multi-part Zip file.


0
Comment
Question by:LIULIHUA
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16676343
LIULIHUA,
> C:\DATA\FTP_Download\
replace the above with

 C:\DATA\FTP_Do~1\
0
 

Author Comment

by:LIULIHUA
ID: 16679410
Hi aneeshattingal,

I got same output:

  End-of-central-directory signature not found.  Either this file is not a Zip file, or it constitutes one disk of a multi-part Zip file.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16679479
Make sure that it is not a corrupted zip file, try to extract it to the same directory (not from TSQL) use the same in command prompt and / or from windows
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16679672
Can you actually unzip these .z files from Winzip??? i.e. using the windows interface?

They are probably not of a kind supported by winzip.
0
 
LVL 35

Expert Comment

by:James0628
ID: 16680792
.z and .Z are the standard extensions used by two old Unix file compression programs (pack and compress).  If these files are coming from a Unix or Linux system, I'd guess that's what they are.  I'm surprised Winzip can't handle them.  Have you tried manually using Winzip to decompress one of these files?  Maybe it's the command line addon that doesn't support them.

 If they were created by pack or compress and Winzip can't handle them for some reason, I'd suggest trying gzip (GNU zip).  It should be able to handle them.  You can get it for free from www.gzip.org .  If you're going to try it, go ahead and get the Windows version, as opposed to MS-DOS.  The Windows version should be a command line program (the standard gzip doesn't come with a GUI interface).

 If neither Winzip nor gzip can decompress the files, they may be corrupt.  Maybe you could check with the source and see if your copies match the originals.  If the files don't contain propietary information, maybe you could post one somewhere so someone else could check it.

 James
0
 

Author Comment

by:LIULIHUA
ID: 16683485
Hi, James0628

1. The fact is Winzip32 can handle .z file when I use command prompt in DOS. And it only works when I just unzip one file, that's means if I try unzip all *.z or *.txt.z files Winzip came up a box said " Cannot create new ARC, TAR, CAB, WMZ, WSZ,YES, gzip or Microsoft Compressed files."
****this one worked in DOS command prompt*****
c:\progra~1\winzip\winzip32 -e -o c:\data\ftp_download\account.txt.z c:\data\ftp_download


2. if i download the gzip MS-DOS version, can I use it with SQL batch file?

thanks.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16688860
1. Does that one work if you type this:

EXEC master.dbo.xp_cmdshell 'c:\progra~1\winzip\winzip32 -e -o c:\data\ftp_download\account.txt.z c:\data\ftp_download'


2. If it says it supports a command line interface then yes, you can use it from SQL



>> if I try unzip all *.z or *.txt.z files Winzip came up a box said " Cannot create new ARC, TAR, CAB, WMZ, WSZ,YES, gzip or Microsoft Compressed files."

So that is why your thing was freezing. It was getting to the .Z files, then coming up with a msgbox. There was no one to click OK on the message box so it just 'froze'.


The confusing thing is your code is unzipping the files one at a time so that shouldn't be happening.



0
 

Author Comment

by:LIULIHUA
ID: 16690103
Hi,

>>1. Does that one work if you type this:

EXEC master.dbo.xp_cmdshell 'c:\progra~1\winzip\winzip32 -e -o c:\data\ftp_download\account.txt.z c:\data\ftp_download'

It hang in QA when I type the code above. However, it works in MS-DOS.
0
 
LVL 35

Expert Comment

by:James0628
ID: 16690413
I've never used the Winzip command line, so I don't know if it should be able to uncompress multiple .z files in one command.  However, it sounds like you can't even get it to uncompress a single file from SQL.  I have no idea why.

 I expect gzip will work from SQL.  The standard gzip is a command line program.  A few details:

 You'll probably want to run gunzip, as opposed to gzip.  The distribution you d/l may include a gunzip.exe, but if it doesn't, just make a copy of gzip.exe .  That's all gunzip is - A copy of gzip under a different name.  The difference is that the program uses the name it's called by to decide the default actions to take.  gzip defaults to compressing and gunzip defaults to decompressing.  You can use command line options to tell gzip to decompress, but the "norm" is to run gunzip to decompress.

 Note that the default action for gzip/gunzip is to compress/decompress the specified file(s) and then delete the original(s).  For example, if you have file.txt and run "gzip file.txt", you'll be left with file.txt.gz (.gz is the default extension for gzip) and file.txt will be gone.  Likewise, if you have file.txt.z and run "gunzip file.txt.z", you'll be left with file.txt and file.txt.z will be gone.  If you want the compressed files to remain, there may be an option to keep the original files.  Try "gzip --help" (or "gunzip --help") to see the options.  FWIW, I just checked the version I have and I didn't see an option like that, but that version is pretty old.

 James
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16696724
So so far we have:

-Unzipping .ZIP files from SQL or CMD        OK
-Unzipping .Z files from CMD                      OK
-Unzipping .Z files from SQL                    HANGS


Can you verfiy, is that where we are?



I am a bit confused by this statement (a coupld of posts ago):

--
And it only works when I just unzip one file, that's means if I try unzip all *.z or *.txt.z files Winzip came up a box said " Cannot create new ARC, TAR, CAB, WMZ, WSZ,YES, gzip or Microsoft Compressed files."
--

Are you:

1. Trying to unzip multiple files from a single .Z file
2. Trying to unzip multiple .Z files at once?


0
 

Author Comment

by:LIULIHUA
ID: 16699448

Hi nmcdermaid,
Thnak you for the response, the current situation is:
****Using winzip32****
-Unzipping one .ZIP files from  CMD        OK ,  
-Unzipping multiple .Zip files from CMD will prompt out a "Add" Box saying" Adding file :c:\Data\FTP_Download\*.zip", seems want me to zipping file instead of unzipping.  
-Unzipping one .Z files from CMD                      OK
-Unzipping multiple .Z files from CMD, Winzip came up a box said " Cannot create new ARC, TAR, CAB, WMZ, WSZ,YES, gzip or Microsoft Compressed files."
-Unzipping one or multiple .ZIP files from SQL                    HANGS
-Unzipping one or multiple .Z files from SQL                    HANGS

Look like Winzip32 is totally dead in TSQL

**** Using WZUNZIP*****
-Unzipping one or multiple .ZIP files from both CMD and SQL OK
--Unzipping one or multiple .Z files from CMD get error: End-of-central-directory signature not found.  Either this file is not a Zip file, or it constitutes one disk of a multi-part Zip file.
--Unzipping one or multiple .Z files from SQL get error:

Zip file: C:\DATA\FTP_Download\account.txt.z
Searching...            
  End-of-central-directory signature not found.  Either this file is not a Zip file, or it constitutes one disk of a multi-part Zip file.
seems TSQL can read the file name, but WZUNZIP couldn't unzip it.



Thank you for the help!
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 16705058
So basically:


- Forget about using winzip32 (the doco actually indicates that command line features are 'undocumented' i.e. unsupported)
- Use WZUNZIP to unzip .ZIP files as this works.
- Try GUnzip as suggested by James0628 to unzip your .Z files as it looks ilke WZUNZIP doesn't recognise that file type.


So:

-Go find GUnzip
-Try extracting your Z files in a command prompt.
-When that works, try it using xp_cmdshell.
-When that works, integrate it into your existing script









0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16705500
nmcdermaid,

>>Forget about using winzip32 (the doco actually indicates that command line features are 'undocumented' i.e. unsupported)<<
The questioner has been told by many (including yourself) the same in numerous different occasions.  See here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21809665.html#16627385
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21821646.html#16512994
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21842551.html#16654084
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21833466.html#16592678

But as you can see, not much progress.

All told they have asked this smae question with different angles six times (including this thread)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16706479
hmm I think I've posted in one of those threads too.  :/

LIULIHUA I guess you should read this and the other posts carefully and see if you can identify the issue.
0
 

Author Comment

by:LIULIHUA
ID: 16709400
It works perfectly with gunzip in both CMD and TSQL. thank you all.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question