How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package - SSIS Tutorial

Scenario: Script to Download

You are working as ETL Developer / SSIS developer. You got this requirement where you get single or multiple Excel files in Source or Input folder. Each File Can have single Sheet or Multiple Sheets.  If there are multiple Sheets they have same metadata( Same Columns).
You need to create csv file per Excel file and load all sheets from it to newly created csv file. 

Here are sample files with multiple Sheet that I am going to use for this post. The Sheets on each Excel file has same number or columns.

How to create CSV File for each Excel File in SSIS Package by using Script Task

Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.
Create CSV files dynamically from Excel Files and Sheets - Script Task C#


Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.
How to Create Single CSV File per Excel Sheet in SSIS Package and load data - Script Task C#

Solution:

We are going to use Script Task with C# as scripting language in our SSIS Package to preform this requirement. 

Step 1:
Create three variables as shown below.
SourceFolderPath: This is the folder where your Excel files should exists. 
DestinationFolderPath: This variable should hold the folder path where you want to create csv files
FileDelimited: You can provide comma(,) or Pipe( | ) or any other character you like to use as delimiter in your csv file. This will give us opportunity to change to any delimiter without changing the package. I have used comma in this post.

How to use variable in Script Task to Create CSV File for Excel Sheets Dynamically in SSIS


Step 2:
Get the Script Task from Toolbox and drag to Control Flow Pane. Open Script Task by double click and then map the variables as shown. 

How to map variables in Script Task for Creating csv file from Excel Sheet dynamically in SSIS


Step 3:
Click on Edit Script Button in above snapshot and it will open Script Task Editor. Under 
#region Namespaces
add
using System.IO;
using System.Data.Oledb;

Then go to public void Main()
{
and paste below code


string SourceFolderPath=Dts.Variables["User::SourceFolderPath"].Value.ToString();         
string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
            string FileDelimited = Dts.Variables["User::FileDelimited"].Value.ToString();
            var directory = new DirectoryInfo(SourceFolderPath);
            FileInfo[] files = directory.GetFiles();
            
            //Declare and initilize variables
            string fileFullPath = "";
            
            //Get one Book(Excel file at a time)
            foreach (FileInfo file in files)
            {
                    string filename = "";
                    fileFullPath = SourceFolderPath+"\\"+file.Name;
                    filename = file.Name.Replace(".xlsx","");
                    MessageBox.Show(fileFullPath);
               
                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR="YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);

                
                //Get Sheet Name
                   cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname;
                sheetname="";
                Int16 sheetcnt = 0;
           foreach (DataRow drSheet in dtSheet.Rows)
            {
                sheetcnt += 1;
                if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                {
                     sheetname=drSheet["TABLE_NAME"].ToString();
                     //Display Sheet Name , you can comment it out
                    // MessageBox.Show(sheetname);

                     //Load the DataTable with Sheet Data
           OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + "]", cnn);
                     //cnn.Open();
                     OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                     DataTable dt = new DataTable();
                     adp.Fill(dt);

                    //drop $from sheet name
                     sheetname = sheetname.Replace("$", "");
                     
                    //Create CSV File and load data to it from Sheet
      StreamWriter sw = new StreamWriter(DestinationFolderPath+"\\"+filename+".csv", true);
                     int ColumnCount = dt.Columns.Count;
                    //we are checking SheetCnt=1, so put header in csv for only one time
                     if (sheetcnt == 1)
                     {
                         // Write the Header Row to File
                         for (int i = 0; i < ColumnCount; i++)
                         {
                             sw.Write(dt.Columns[i]);
                             if (i < ColumnCount - 1)
                             {
                                 sw.Write(FileDelimited);
                             }
                         }
                         sw.Write(sw.NewLine);
                     }
                     

                     // Write All Rows to the File
                     foreach (DataRow dr in dt.Rows)
                     {
                         for (int i = 0; i < ColumnCount; i++)
                         {
                             if (!Convert.IsDBNull(dr[i]))
                             {
                                 sw.Write(dr[i].ToString());
                             }
                             if (i < ColumnCount - 1)
                             {
                                 sw.Write(FileDelimited);
                             }
                         }
                         sw.Write(sw.NewLine);
                     }
                     sw.Close();
                } 
            }

}
Step 5:
Save the script in Script Task Editor and then close the Editor windows. Now you are all set to run your SSIS Package to create csv file per excel file that will load multiple sheets data to it.

As I have two sample files with two sheets per file,If I will run the package, It should create two csv files and load data from 4 sheets.

How to create csv file per Excel File and load data to it in SSIS Package - Script Task C#






Things we learnt in this post
How to create variables in SSIS Package and use them in Script Task
How to read data from single/ multiple Excel files in SSIS Package
How to read data from single or multiple excel sheets for a excel file in SSIS Package
How to create csv file and load data to it from datatable by using C# language
How to avoid writing header again to csv file when loading multiple excel sheets in SSIS Package
How to use C# to read data from Excel file and write to csv files



Check out our other Excel Related Posts
  1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
  2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
  3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
  4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
  5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
  6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
  7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
  8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
  9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
  10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
  11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
  12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
  13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
  14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
  15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
  16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
  17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
  18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
  19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task

4 comments:

  1. i love your site and you guys / gals are great but i would point out to anyone who got an error in the code that mine was due to the fact that the 'using' is case sensitive and the ole db line should read 'using System.Data.OleDb'. notice the capital 'D' in Db. thanks.

    ReplyDelete
  2. Looks like there is an issue with this script, If the number of columns in the excel are more then 255 it is truncating the columns to 255, I'm looking for a script for more than 400 columns and also data needs to be written in multiple csv sheets rather than 1, if we have more than 1 sheets in 1 excel file. thanks for you help!

    ReplyDelete
  3. I only have one sheet so I think i can leave code as is, but does not show column headers on CSV when I run?

    ReplyDelete