Convert multiple .xls files to .xlsx in ssis

Question

I have a folder that receives multiple excel files in .xls format. I need to change the format type to .xlsx in order to load the excel data into SQLvia SSIS. I know how to rename the file using "File System Task" but that works for a specific file. but my file contains a file # and date as well that needs to stay same as source file, I only want the file type to change and the file move to a processed folder. Can anyone help me?

Source Path: C:\Documents\TestFolder
Source File: TestSegRpt_0001_2017_02_22.xls

Destination Path: C:\Documents\TestFolderProcessed
Destination File: TestSegRpt_0001_2017_02_22.xlsx

score 0 · Answer 1 · answered Feb 27 '17 at 18:12

0

Hoping i understood your problem correctly.

I think below link will help.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/batch-convert-xls-to-xlsx/1d9b3d78-daf0-4014-8fb2-930aca6493b0

answered Feb 27 '17 at 18:12

Tajinder

2,248
4
33
54

is that a VB Script? sorry I'm new to scripting – Syed Jafri Feb 27 '17 at 18:17
@Syed Jafri If you are new to scripting or creating macros i think you can use some kind of software. Just found this link on google http://ccm.net/download/download-18703-convert-xls-to-xlsx. Please check if this can help. – Tajinder Feb 27 '17 at 18:28

Hadi · Answer 2 · 2017-03-10T20:33:03.850

You have to add a Script Task, loop over files, and use a function like the following to create precessed directory and convert files (code in Vb.net):

Public Sub ConvertXlsToXlsx(ByVal strpath as string)

    Dim strDirectory as string = System.IO.Path.GetDirectoryName(strpath) & "Processed"

    If Not System.IO.Directory.Exists(strDirectory) Then System.IO.Directory.CreateDirectory(strDirectory)

    Dim xl As New Microsoft.Office.Interop.Excel.Application
    Dim xlBook As Microsoft.Office.Interop.Excel.Workbook

    xlWorkBook = xl.Workbooks.Open(strpath)
    xlBook.SaveAs(strDirectory & "\" & System.IO.Path.GetFilename(strpath) & "x")
    xl.Application.Workbooks.Close()
    xl.Application.Quit()

End Sub

Your code will look like:

Public Sub Main

    Dim strFolder as string = Dts.Variables.Item("FolderPath").Value

    Dim strXlsFiles() as string = IO.Directory.GetFiles(strFolder,"*.xlsx",SearchOption.TopDirectoryOnly)

    For each strFile as String in strXlsFiles

        If strFile.EndsWith("xlsx") The Continue For 

        ConvertXlsToXlsx(strFile)

    Next


End Sub

Reference:

https://social.msdn.microsoft.com/Forums/office/en-US/a73f846c-91ee-4dad-bd7b-c04d418d0561/convert-xls-into-xlsx?forum=exceldev

Convert multiple .xls files to .xlsx in ssis

2 Answers2