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
Check out our other posts/videos for Dynamic Excel Source and Destination
- How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
- How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
- How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
- How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
- How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
- How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
- How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task
- How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
- How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
- How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
- How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
- How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
- Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
- How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
- How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task
- How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
- How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
- How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
- How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
- How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
- How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
- How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
- How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
- How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
Hello Aamir,
ReplyDeleteGood 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.
Hi, This script is brilliant and works very well, thank you very much for sharing this.
ReplyDeleteOne 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?
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:
ReplyDeleteanyconv.com
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!
ReplyDeleteI 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.
ReplyDeleteWhen i open and "SAVE AS" it and try to upload, it gets successfully upload. Can anyone tell me what i am missing here?
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.
ReplyDeleteits very helpfull and how convert the xlsb to xlsx file and sheet name should be constanmt
ReplyDeleteLooks like good code...outside of the Excel 12 hardcode in the middle of it.
ReplyDeleteHello 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.
ReplyDeleteAnyone 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.
ReplyDeletecan you figure this out using a text qualifier. if you have a single quote in a text field then this will fail.
ReplyDeleteHappy for know your article. As you wish, i have download lagu online music mp3. Can you review that?
ReplyDeleteI'm really impressed with your writing skills, as smart as the structure of your weblog.
ReplyDeleteMAGIX Video Pro Crack
Movavi Video Editor Plus Crack
Adobe InCopy Crack
ascrack.org
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 .
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteit 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.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteusing System;
ReplyDeleteusing System.Data;
using System.IO;
using ExcelDataReader;
public class ExcelToCsvConverter
{
private static readonly int MaxRecordsPerFile = 50;
public static void Convert(string excelFilePath, string outputDirectory)
{
if (!File.Exists(excelFilePath))
{
throw new FileNotFoundException($"Excel file not found: {excelFilePath}");
}
using (var stream = File.OpenRead(excelFilePath))
{
// Detect Excel format (xls or xlsx)
IExcelDataReader reader;
string fileExtension = Path.GetExtension(excelFilePath);
if (fileExtension == ".xls")
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (fileExtension == ".xlsx")
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
throw new NotSupportedException($"Unsupported Excel format: {fileExtension}");
}
for (int sheetIndex = 0; sheetIndex < reader.IsFirstResult; sheetIndex++)
{
reader.Read(); // Move to the first result (sheet)
DataSet result = reader.AsDataSet(null); // Read only schema (optional)
var sheetName = result?.Tables[0]?.TableName ?? "Sheet"; // Get sheet name (handle potential null)
var data = reader.AsDataReader(); // Get data as DataReader
int fileNumber = 1;
int recordCount = 0;
using (var sw = new StreamWriter(Path.Combine(outputDirectory, $"{sheetName}.csv")))
{
// Write header row (assuming schema is available)
if (result?.Tables[0] != null)
{
sw.WriteLine(string.Join(",", result.Tables[0].Columns.Cast().Select(c => c.ColumnName)));
}
while (data.Read())
{
if (recordCount == MaxRecordsPerFile)
{
sw.Flush(); // Flush existing data to file
sw.Close(); // Close current file
// Create new file with incremented number
fileName = $"{sheetName}{fileNumber++}.csv";
sw = new StreamWriter(Path.Combine(outputDirectory, fileName));
if (result?.Tables[0] != null)
{
sw.WriteLine(string.Join(",", result.Tables[0].Columns.Cast().Select(c => c.ColumnName)));
}
recordCount = 0;
}
// Write comma-separated values
sw.WriteLine(string.Join(",", data.GetValues().Select(v => v?.ToString() ?? "")));
recordCount++;
}
sw.Flush();
}
}
}
}
public static void Main(string[] args)
{
if (args.Length != 2)
{
Console.WriteLine("Usage: ExcelToCsvConverter ");
return;
}
string excelFilePath = args[0];
string outputDirectory = args[1];
try
{
Convert(excelFilePath, outputDirectory);
Console.WriteLine("Conversion completed successfully!");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}