how to get csv files from zip file

  • 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?

  • 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

  • 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..

     

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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