CODEWITHSUNDEEP
c#excelvb.netssisvb.net-2010
user1254579
user1254579

Reputation: 4009

How to convert Excel file with multiple sheets to a set of TSV files?

What addition clde to be added to cconvert excel file with multiple sheets to Tsv file in the below code.

note:The below code convert an excel file with one sheet to Tsv.It does not handle an excel sheet with multiple sheet

Public Sub Main()

        Dim oExcel As Object
        Dim oBook As Object

        Dim sFileName As String
        Dim sFileNameOnly As String


        Dim sXlsPath As String
        Dim sTsvPath As String



        sFileName = CStr(Dts.Variables("User::Xls_File_Name").Value)


        sXlsPath = "H:\Xls_Files\" + sFileName

        sFileNameOnly = Path.GetFileNameWithoutExtension(sFileName)

        sTsvPath = "H:\Xls_Files\" + sFileNameOnly + ".Txt"


        oExcel = CreateObject("Excel.Application")


        oBook = oExcel.Workbooks.Open(sXlsPath)

        oBook.SaveAs(sTsvPath, -4158)

        oBook.Close(False)
    enter code here
        oExcel.Quit()

        Dts.TaskResult = ScriptResults.Success
    End Sub

Upvotes: 1

Views: 796

Answers (1)

Jonathan Applebaum
Jonathan Applebaum

Reputation: 5986

you can use Microsoft.Office.Interop.Excel for that task (note that you need to reference that namespace first).

as @stuartd suggest you can create a summary worksheet that will contain all the sheets according to your sheets structure (please see my comment inside the code) or alternatively (if it suits your needs) you can save each worksheet on a different tsv file using the For Each loop.
here is a tested example that shows the principle of that:

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

     Public Sub Main()
            Dim xlApp As New Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            xlApp.Visible = False

            ' create a new workbook (you can open existing workbooks using the Open() method) '
            xlWorkBook = xlApp.Workbooks.Add()
            ' create sheet 1 '
            xlWorkSheet = xlWorkBook.Worksheets.Add()
            xlWorkSheet.Name = "ws1"
            xlWorkSheet.Range("a1").Value = "hello from worksheet1"
            ' create sheet 2 '
            xlWorkSheet = xlWorkBook.Worksheets.Add()
            xlWorkSheet.Name = "ws2"
            xlWorkSheet.Range("a1").Value = "hello from worksheet2"
            ' worksheet that contain both worksheets '
            xlWorkSheet = xlWorkBook.Worksheets.Add()
            xlWorkSheet.Name = "summary"

            ' paste all sheets data inside the summary sheets (i dont know how your excel sheets are structured but you can select entire ranges and arrange them on the summary worksheet) '
            Dim counter As Int16 = 1
            For Each w As Worksheet In xlWorkBook.Worksheets
                If w.Name <> "summary" Then
                    xlWorkSheet.Cells(counter, 1) = w.Range("a1")
                    counter += 1
                End If
            Next
            xlApp.DisplayAlerts = False
            ' save as tsv file '
            xlWorkBook.SaveAs("c:\xxx\xxx\Book4.tsv", XlFileFormat.xlTextWindows, CreateBackup:=False)
        End Sub

Upvotes: 1

Related Questions