Post reply

Merge multiple .CSV files into 1 Excel workbook (with each CSV having its own sheet)

  • April 4, 2016 at 7:40 am

    #317667

    I have multiple CSV files that I would like to combine into a single Excel workbook - with each CSV having its own sheet.

    For example, I have 4 .CSV files: FilingsByDay, FilingByHour, NumberofFilings, and StatsByCounty. I would like to create one Excel workbook from these files and have each represented (with the data that lies in the CSV) on 4 separate sheets.

    Is this possible using SSIS? And if so, any suggestions on how to accomplish it?

    Thanks,

  • April 5, 2016 at 6:50 am

    #1869790

    Yes you can do this in SSIS.

    Within your package you will need a connection for each CSV file and one for your Excel workbook.

    In a data flow, you will need a source of one of the CSV files with a destination of the Excel file. In the Excel destination you can specify the sheet name and create a new sheet as necessary.

    You will need a separate source and separate destination components for each CSV file. In each destination component you can specify the same Excel file (but with different sheets).

    If you have a small number of CSV files then this is quite simple. If you have hundreds of CSV files or a varying number of files or contents then it will be a problem as data flow tasks don't like changes at run time.

    Jez

  • April 5, 2016 at 2:28 pm

    #1869995

    Thanks for the reply. The solution I've gone with is to use a PowerShell script and call this through a process task in SSIS and it seems to work nicely.

    FYI, the script is here in case anyone would like to view/use it:

    http://poshcode.org/2123

    Thanks again,

Viewing 3 posts - 1 through 2 (of 2 total)

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