VB Script to convert XLSX files with multiple sheets to CSV file in Script task

Question

Hello,

i have been strunggling to find solution to convert XLSX files with multiple sheets to csv file. can any one please help me.

Requirements

>> Convert XLSX file with multiple sheets to CSV file

>> CSV file names : XLSX filename + '_' + sheet name

>> scirpt has to be in VB as i am using ssis 2005

>> i started develping scirpt using Micorosoft.office.interop.Excel.dll . this dll is referenced to script task.

>> found bellow web link as useful.. because i am not god at progarmming, couldn't understnad some of it.

http://www.mssqltips.com/sqlservertip/2772/importing-data-from-excel-using-ssis--part-2/

http://www.dreamincode.net/forums/topic/278621-help-convert-xls-to-csv-vbnet-by-worksheet/

Thanks in Advance

Jai

Answer 1

Since you are asking about a VBScript, and this is a VB NET forum, I'll give you a PowerShell script that you can convert

Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = "C:\ExcelFiles\" + $excelFileName + ".xlsx"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
        $ws.SaveAs($csvLoc + $n + ".csv", 6)
    }
    $E.Quit()
}
'Usage:
ExportWSToCSV -excelFileName "file" -csvLoc "C:\CSVFiles\"

Answer 2

Hello,

i have been strunggling to find solution to convert XLSX files with multiple sheets to csv file. can any one please help me.

Requirements

>> Convert XLSX file with multiple sheets to CSV file

>> CSV file names : XLSX filename + '_' + sheet name

>> scirpt has to be in VB as i am using ssis 2005

>> i started develping scirpt using Micorosoft.office.interop.Excel.dll . this dll is referenced to script task.

>> found bellow web link as useful.. because i am not god at progarmming, couldn't understnad some of it.

http://www.mssqltips.com/sqlservertip/2772/importing-data-from-excel-using-ssis--part-2/

http://www.dreamincode.net/forums/topic/278621-help-convert-xls-to-csv-vbnet-by-worksheet/

Thanks in Advance

Jai

Hello Jai,

In addition to Devon, there is a forum for script issues, you could post this issue in the following forum to get support.

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/home?category=scripting

Regards.

Carl

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

Does it have to be a script solution. You could connect to the Excel as database and read Tables (sheets) and Rows/Columns and output them as text file. The only requirement would be that you use x64 platform so you get access to ACE drivers (xlsx file support). Of course you still might get some issues with memo fields/comas/quotes/images but if it is a straight text it should work just fine.