how to get csv files from zip file

Question

how to get csv files from zip file

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
June 2, 2022 at 6:48 pm

#4043392

I have a case where i have multiple .zip files in one of folder
example - c:\Test\1234.zip like this we have multiple zip files under Test folder
In each of this zip file we have single folder under that multiple folders and .csv file
example - c:\Test\1234.zip\1234\emp.csv
Is there a way in SSIS to loop through all zip files and retrieve only .csv files?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

Yes - it can be done but you have to write some C# code in a script task to open the archive, loop through through the results - and extract the files.

See this https://docs.microsoft.com/en-us/dotnet/api/system.io.compression.zipfile.openread?view=net-6.0 as a starting point.

Once you have that CSV file, what are you going to be doing with it?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Once i got all csv files from all the zip files into a folder. i will have to loop through again all csv files and loads the content in that csv file into table

because in each csv file we have some content where i need to extarct and do some more logic to pull .pdf data and send to sftp..

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

I would probably set this up with 2 packages in the project. The first package uses a script task to traverse the folder(s) where the zip archives live - using the code in the link above to extract the CSV files to a specified location. The second package processes any files found in that specified location.

It will take some time to write that code - but once it has been written the other package(s) should be fairly simple. If all CSV files have the exact same format - one package to process that file type. If you have different formats then a separate package for each format - with separate locations where you drop the files.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

There's gotta be a way to read the manifest of files from a zip file in SSMS. Almost everything else has that capability.

--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)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

Jeff Moden wrote:

There's gotta be a way to read the manifest of files from a zip file in SSMS. Almost everything else has that capability.

Not sure what you are getting at - or why. The OP needs to look through a directory where there are archived (zip) files - search each zip file for a specific (assuming it is specific) CSV file in the archive, extract that CSV file and load it into the database.

How would SSMS being able to read a zip archive help with that process?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Jeffrey Williams wrote:

Jeff Moden wrote:
There's gotta be a way to read the manifest of files from a zip file in SSMS. Almost everything else has that capability.
Not sure what you are getting at - or why. The OP needs to look through a directory where there are archived (zip) files - search each zip file for a specific (assuming it is specific) CSV file in the archive, extract that CSV file and load it into the database.
How would SSMS being able to read a zip archive help with that process?

Apologies... what I'm getting at is that you shouldn't have to expand a zip file to get the list of files names it contains. Most unzip software has a "manifest" command that will return the list of files contained in a zip file.

--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)