Parsing .xlsx and writing to .txt

[jhutchings]

Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Office.Core
Imports Excel
Imports System.Collections
Imports System.Data.OleDb
Imports ParseTool.GlobalVariables

Public Class VJHoldCode

    Public Shared formatDate As String
    Public Shared j, c As Integer
    Public Shared dtSet As DataSet
    Public Shared conn As OleDbConnection
    Public Shared CMD As OleDbDataAdapter
    Public Shared LineToWrite As String

    Public Shared Sub Process()
        formatDate = DateTime.Now.ToString("yyMMdd")
        Dim files As String() = Directory.GetFiles("P:\inetpub\wwwroot\PrintTrack\VJHold\", "*.xlsx")
        Dim newLoc As String = "F:\IHADIR\IHJobs\COB_Survey\_" & formatDate
        Dim fileName As String = ("F:\IHADIR\IHJobs\COB_Survey\_" & formatDate & "\COB_" & formatDate & ".txt")
        c = 1
        Dim xlsxConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & files(0) & ";Extended Properties=" & Chr(34) & "Excel 12.0;HDR=NO" & Chr(34) & ";"
        Dim myTableName = getTableName(files(0), xlsxConn)
        CMD = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", myTableName), conn)
        CMD.TableMappings.Add("Table", "VJHold")
        dtSet = New DataSet
        CMD.Fill(dtSet)
        conn.Close()

        Directory.CreateDirectory(newLoc)

        FileOpen(1, fileName, OpenMode.Append, OpenAccess.ReadWrite)

        For _Row As Integer = 0 To dtSet.Tables(0).Rows.Count - 1
            LineToWrite = String.Empty
            For _column As Integer = 0 To dtSet.Tables(0).Columns.Count - 1
                If _column = 0 And dtSet.Tables(0).Rows(_Row).Item(_column).ToString = "Grpnum" Then
                    Exit For
                End If
                If LineToWrite = "" Then
                    LineToWrite = dtSet.Tables(0).Rows(_Row).Item(_column).ToString
                Else
                    LineToWrite &= "," & dtSet.Tables(0).Rows(_Row).Item(_column).ToString
                End If

            Next

            If LineToWrite <> String.Empty Then
                Dim newline As Array = LineToWrite.Split(",")
                For i = 0 To UBound(newline)
                    If i = dtSet.Tables(0).Columns.Count - 1 Then
                        Write(1, newline(i))
                        WriteLine(1, (c.ToString()))
                        c += 1
                    Else
                        Write(1, newline(i))
                    End If
                Next i
            End If
            j += 1
        Next

        FileClose(1)

        File.Move(files(0), newLoc & "\" & Path.GetFileName(files(0)))

        mailBody = bodyBuild(fileName, (j - 1))

        emailer.sendEmail("VJ Hold Code", mailBody, VJMailTo)
    End Sub

    Public Shared Function getTableName(ByVal file As String, ByVal xlsxConn As String) As Object
        Dim conn As OleDbConnection
        conn = New OleDbConnection(xlsxConn)
        conn.Open()
        Dim myTableName = conn.GetSchema("Tables").Rows(0)("TABLE_NAME")
        conn.Close()
        Return myTableName
    End Function

    Public Shared Function bodyBuild(ByVal path1 As String, ByVal trc As Integer) As String
        formatDate = DateTime.Now.ToString("yyMMdd")
        Dim sb As String
        Dim builder As New StringBuilder
        Dim result As String

        builder.Clear()

        result = Path.GetFileNameWithoutExtension(path1)
        Dim outputDir As String = "O:\Outputs\PS\psfile\" & result & ".ps"

        builder.Append("Total Records Processed: " & trc.ToString)
        builder.AppendLine()
        builder.Append(outputDir)
        builder.AppendLine()

        sb = builder.ToString
        Return sb
    End Function

End Class

This process picks up a .xlsx file -> reads the data -> takes all data of a row and creates a string -> breaks the string apart into separate string to write to .txt files as comma delimited -> adds a sequence number at the end.

After it creates the file, I then build the body to an email message and then send an email (through a different method).

I am currently not using a stream reader/writer for this because I couldn't figure out a way to properly write to the .txt file the way I wanted it to.

I need the output to be formatted like this:

"XXXX","XXXX","XXXX"