How to Convert Multiple excel files to CSV using SSIS

Question

I have several excel files that I need converted to CSV prior to load. I will like this done on SSIS script. Can someone please assist me?

Ram Boyapati · Answer

Please refer below link having clear explanation and code also. http://www.msbistudent.com/2017/10/how-to-convert-excel-file-to-csv-file.html https://www.youtube.com/channel/UCJigaT6ES1qj9tVCbmKJAtA/videos?disable_polymer=1 www.eclasess.com

roopa bo · Answer

Thanks, Gowtham but those link are already there, do u know any other?

Gowtham Rajamanickam · Answer

http://social.msdn.microsoft.com/forums/sqlserver/en-US/b6edae1c-1b3a-4a78-b7fc-1df081ba074e/using-ssis-convert-excel-in-to-csv

Gowtham Rajamanickam · Answer

http://stackoverflow.com/questions/17843053/ssis-using-script-task-convert-xls-to-csv

Gowtham Rajamanickam · Answer

http://msbi-tips.blogspot.com/2012/02/convert-csv-to-excel-dynamically.html

roopa bo · Answer

I have a similar requirement, But in my case I have multiple sheets in a excel workbook.I need to convert each sheet in to separate csv file using ssis script task. is it gonna be similar to this script?

Candy Brown · Answer

Hi, I'm not sure if you want to convert each sheet of those excel files into a separate CSV file, or each excel file to a CSV or everything into a single CSV, but nevertheless here is a Script Task in C# that will convert everything into a single CSV: string [] excelFilePaths = { "Book1.xls" , "Book2.xls" , "Book3.xlsx" }; var csvFile = new ExcelFile (); var csvSheet = csvFile.Worksheets.Add( "Sheet" ); int csvRowI = 0; foreach ( var ef in excelFilePaths.Select(path => ExcelFile .Load(path))) foreach ( var ws in ef.Worksheets) for ( int ri = 0; ri for ( int ci = 0; ci csvSheet.Cells[csvRowI, ci].Value = ws.Cells[ri, ci].GetFormattedValue(); csvFile.Save( "Result.csv" ); You need to add a reference to this Excel component for .NET and also as you can see it reads both Excel formats (older XLS and newer XLSX) in same manner in .NET .

Summer Amy · Answer

Thanks, I have already seen these however my concern is that I have multiple files that I need converted into comma separated .csv

Ramesh Maruthi · Answer

http://msbi-tips.blogspot.com/2012/02/convert-csv-to-excel-dynamically.html http://stackoverflow.com/questions/17843053/ssis-using-script-task-convert-xls-to-csv http://social.msdn.microsoft.com/forums/sqlserver/en-US/b6edae1c-1b3a-4a78-b7fc-1df081ba074e/using-ssis-convert-excel-in-to-csv

How to Convert Multiple excel files to CSV using SSIS

Answers (9)