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

• treyagrimes

  Ten Centuries

  Points: 1001

  More actions

  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,

• Jez-448386

  SSCarpal Tunnel

  Points: 4596

  More actions

  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

• treyagrimes

  Ten Centuries

  Points: 1001

  More actions

  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,