How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task - SSIS Tutorial

Scenario: Download Script

You are working as ETL Developer / SSIS Developer for Car Insurance company. They receive text files in their source folder. You need to create an SSIS Package that should convert these text files into Excel Files. The Excel File Name should be the same as Text file and Sheet Name should also be same as file name.

Here are couple of samples files I am using for test

Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created.The name of log file will be same like your source file that Package was processing.


Solution:  

We care going to use Script Task in SSIS Package to convert Text/CSV/Tab delimited files to Excel Files. We are going to create variable in our SSIS Package such as FileExtension and FileDilimeter. By changing the values of these variable we can use our SSIS Package to handle different type of text files.


Step 1: Create new SSIS Package with Variables to Make it Dynamic 
Open SSDT ( Sql Server Data Tools) and create new SSIS Package. After that create below variables

DestinationFolderPath: Folder path where you would like to create your Excel Files
FileDelimiter : Provide the delimiter such as comma (,), Pipe( | ) Whatever your files are using.
FileExtension : Provide the extension of files you would like to convert such .txt, .csv
SourceFolderPath : Source folder path where text files exists

Create Variables in SSIS Package to use in Script Task to Convert Text Files to Excel Files


Step 2: Add Script Task to SSIS Package and Map Variables
Bring Script Task to Control Flow Pane and open it by double clicking. Add the SSIS Package variables to it so we can use inside.
Add variables to Script Task in SSIS Package to use for converting CSV files to Excel Files


Step 3: Add Script to Script task Editor in SSIS Package to Convert Text Files to Excel Files 
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;
using System.Data.OleDb;


Under public void Main() { 
I have added below code. 

try
            {

                //Declare Variables
                string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
                string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
                string FileExtension= Dts.Variables["User::FileExtension"].Value.ToString();
                string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
                string CreateTableStatement = "";
                string ColumnList = "";
                
                //Reading file names one by one
                string SourceDirectory = SourceFolderPath;
                string[] fileEntries = Directory.GetFiles(SourceDirectory,"*"+FileExtension);
                foreach (string fileName in fileEntries)
                {
                    // do something with fileName
                    //MessageBox.Show(fileName);
                    
                    //Read first line(Header) and prepare Create Statement for Excel Sheet
                    System.IO.StreamReader file = new System.IO.StreamReader(fileName);
                    string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
                    CreateTableStatement = (" Create Table [" + filenameonly + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";
                    file.Close();
                    //MessageBox.Show(CreateTableStatement.ToString());


                    //Construct ConnectionString for Excel
                    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly
                        + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
                    OleDbConnection Excel_OLE_Con = new OleDbConnection();
                    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();



                    //drop Excel file if exists
                    File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xlsx");
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;

                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Cmd.CommandText = CreateTableStatement;
                    Excel_OLE_Cmd.ExecuteNonQuery();

                    //Writing Data of File to Excel Sheet in Excel File
                    int counter = 0;
                    string line;

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
                            
                        }
                        else
                        {
                            string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "')";
                           // MessageBox.Show(query.ToString());
                            var command = query;
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();

                        }
                        counter++;
                    }
                    Excel_OLE_Con.Close();
                    SourceFile.Close();
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            }
            catch (Exception exception)
            {


                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString() 
                    + "\\" +"ErrorLog_"+DateTime.Now.ToString("yyyyMMddHHmmss")+".log"))
         
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;

                }
                               
            }


Step 4: Save Script and Run your SSIS Package to Convert Text Files to Excel Files 
Save the script in Script Task Editor and execute your SSIS Package. It should read each of the file from Source Folder and create new excel file in Destination Folder. The package do not delete the files from Source Folder. You can add that script to Script Task or use File system task to do that.

As I had two text files, It created two excel files for me. One for each Text file.

How to convert CSV files to Excel Files in SSIS Package by using Script Task- C# scripting language



Check out our other posts/videos for Dynamic Excel Source and Destination
  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
  20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
  21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
  22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
  23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
  24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package

18 comments:

  1. Hello Aamir,

    Good Morning Thanks for the SSIS videos with great knowledge to learn and implement in our projects. I have few queries in regards to one of the SSIS Video (Converting CSV File to Excel using Script Task).

    I have a scenario where their is huge CSV File with data in some fields and with no data in some other fields, and i see that the script that you have provided has limitations and in regards to my requirement i need to add few more lines of code in the script.

    As i have no knowledge on C Sharp, i am facing difficulty in making those changes. It would be a great help if you could help with those kind of videos as well.

    ReplyDelete
  2. Hi, This script is brilliant and works very well, thank you very much for sharing this.

    One question I have is what do I do if the csv file i'm trying to convert doesn't have a delimiter?
    I cannot leave the variable blank as the code errors out, do you have any suggestions?

    ReplyDelete
  3. So you can play swf record on windows with Adobe Flash Player effectively, however it is a hard thing to watch them on macintosh, similar to the folks cry:
    anyconv.com

    ReplyDelete
  4. Looking for something a little closer to 100% perfect video to text conversion? Our standard transcription service is still a cost-effective way to convert video to text and provides specialised transcription services. Don't forget to check today!

    ReplyDelete
  5. I have crated excel files. they are accessible in excel 2010 but when I am trying to upload it in Marketplace website it is not recognizing the format. I have checked it is xlsx.

    When i open and "SAVE AS" it and try to upload, it gets successfully upload. Can anyone tell me what i am missing here?

    ReplyDelete
  6. I have notice that..that orignal file which this SSIS package is creating is showing (.XLSX) extension in capital letters...and when i "SAVE AS" it , the extension gets small letters. please advise what do i change in the package to get small letter xlsx.

    ReplyDelete
  7. its very helpfull and how convert the xlsb to xlsx file and sheet name should be constanmt

    ReplyDelete
  8. Looks like good code...outside of the Excel 12 hardcode in the middle of it.

    ReplyDelete
  9. Hello Sir, am using your example to convert my text files to Excel files, my text files are pipe delimited. However in my text files for one single row there are some values which are null (| |) this causing an error when running the package. Also I have decimal numbers in the text files this too causing errors. And finally I have the word "EOF" at the end of the text file to indicate End of file which is causing errors too. Can you please advise how to solve these 3 problems please.

    ReplyDelete
  10. Anyone have any problems with the script only processing 50 files? I even put the script task in a For Each container and only converts 50 files.

    ReplyDelete
  11. can you figure this out using a text qualifier. if you have a single quote in a text field then this will fail.

    ReplyDelete
  12. Happy for know your article. As you wish, i have download lagu online music mp3. Can you review that?

    ReplyDelete
  13. Amazing blog! I really like the way you explained such information about this post with us. And blog is really helpful for us this website
    blackmagic-design-fusion-studio-crack

    ReplyDelete
  14. I am very impressed with your post because this post is very beneficial for me and provide a new knowledge…
    advanced-xls-converter-crack

    ReplyDelete
  15. I'm really impressed with your writing skills, as smart as the structure of your weblog.
    MAGIX Video Pro Crack
    Movavi Video Editor Plus Crack
    Adobe InCopy Crack
    ascrack.org

    ReplyDelete
  16. Great post by the great author, it is very massive and informative about Image Converter Online Free but still preaches the way to sounds like that it has some beautiful thoughts described so I really appreciate this post .

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. it is not converting to Excel when we have 2 same column names in csv file and it is giving error as column already exists.. please help me.

    ReplyDelete