How do i convert an xlsx file into a csv file, using powershell?

Question

How do I convert a xlsx file to a csv file and add double quotes to every cell using Powershell?

I need to convert a lot of files from .xlsx to csv. In addition thereto every cell has to get quoted with double quotes, and there has to be added a semicolon delimiter.

I made an VBA script to do the conversion from .xlsx to .csv and add the double quotes, but it took up to multiple hours per file.

My hope was that i would be much faster using powershell.

Does someone know how to rewrite this functionality in Powershell?

Thanks a lot for the help!

My way of doing it in VBA:

Sub ConvertToCSV()
 Dim DestFile As String
 Dim FileNum As Integer
 Dim ColumnCount As Integer
 Dim RowCount As Long
 Dim StrFile As String


Application.ScreenUpdating = False

StrFile = Dir("C:\Users\example\*PLZ*")

Do While Len(StrFile) > 0

        Workbooks.Open ("C:\Users\example\" & StrFile)

            Range("A1").Select
            Selection.CurrentRegion.Select


            NameWithoutExtension = Left(StrFile, Len(StrFile) - 5)
            DestFile = "C:\Users\example\" & NameWithoutExtension
            FileNum = FreeFile()
            Open DestFile For Output As #FileNum


            If Err <> 0 Then
            MsgBox "Cannot open filename " & DestFile
            End
            End If
            On Error GoTo 0



                For RowCount = 1 To Selection.Rows.Count

                       ' Loop for each column in selection.
                          For ColumnCount = 1 To Selection.Columns.Count

                            OldText = Selection.Cells(RowCount, ColumnCount).Text
                            MiddleText = Replace(OldText, "\", "/")
                            NewText = Replace(MiddleText, """", "\""")

                            ' Write current cell's text to file with quotation marks.
                             Print #FileNum, """" & NewText & """";

                             ' Check if cell is in last column.
                             If ColumnCount = Selection.Columns.Count Then
                                ' If so, then write a blank line.
                                Print #FileNum,
                             Else
                                ' Otherwise, write a comma.
                                Print #FileNum, ";";
                             End If
                          ' Start next iteration of ColumnCount loop.
                          Next ColumnCount
                       ' Start next iteration of RowCount loop.
                       Next RowCount

                       ' Close destination file.
                       Close #FileNum


        ActiveWorkbook.Close


    StrFile = Dir

Loop

MsgBox ("Done")
End Sub

Answer 1

There really is no need to use powershell for this, you just need to use the correct methods in VBA...

Code untested but think it should work out fine...

Sub ConvertToCSV()
  Application.ScreenUpdating = False

  Dim StrFile As String
  StrFile = Dir("C:\Users\example\*PLZ*")

  Do While Len(StrFile) > 0
    'Open workbook
    Dim wb as workbook
    set wb = Workbooks.Open("C:\Users\example\" & StrFile)

    'Save workbook to new location
    Dim DestFile As String
    DestFile = "C:\Users\example\" & Left(StrFile, Len(StrFile) - 5)
    wb.saveAs destFile, xlFileFormat.xlCSV

    'Ensure no alerts while close
    Application.displayAlerts = false
      wb.close false
    Application.displayAlerts = true

    'Continue loop
    StrFile = Dir
  Loop
  Application.ScreenUpdating = True
End Sub

Edit:

Apologies I didn't see the requirement that strings need to be quoted. A few patches to your code can be done which will make it run significantly faster, the key is to convert the file to an array first:

I've tried to keep your code as close to it was before. Note you are currently semi-colon delimiting the files...

Sub ConvertToCSV()
    Dim DestFile As String
    Dim FileNum As Integer
    Dim ColumnCount As Integer
    Dim RowCount As Long
    Dim StrFile As String


  Application.ScreenUpdating = False

  StrFile = Dir("C:\Users\example\*PLZ*")

  Do While Len(StrFile) > 0
    'Open workbook and store in variable
    Dim wb as workbook
    set wb = Workbooks.Open("C:\Users\example\" & StrFile)

    'Get data as array
    Dim r as range, v as variant
    set r = wb.ActiveSheet.Range("A1").CurrentRegion
    v = r.value2

    'Get dest path
    NameWithoutExtension = Left(StrFile, Len(StrFile) - 5)
    DestFile = "C:\Users\example\" & NameWithoutExtension
    FileNum = FreeFile()

    'Try open file
    On Error Resume Next
      Open DestFile For Output As #FileNum

      'If error then end
      If Err <> 0 Then
        MsgBox "Cannot open filename " & DestFile
        End
      End If
    On Error GoTo 0

    'Loop over array
    Dim i as long, j as long
    For i = 1 To ubound(v,1)
      For j = 1 To ubound(v,2)
        OldText = v(i,j)
        MiddleText = Replace(OldText, "\", "/")
        NewText = Replace(MiddleText, """", "\""")

        ' Write current cell's text to file with quotation marks.
        Print #FileNum, """" & NewText & """";

        ' Check if cell is in last column.
        If j = ubound(v,2) Then
          ' If so, then write a blank line.
          Print #FileNum,
        Else
          ' Otherwise, write a comma.
          Print #FileNum, ";";
        End If
      Next j 'Next column
    Next i 'Next row

    ' Close destination file.
    Close #FileNum

    'Close workbook
    wb.Close false

    'Get next file path
    StrFile = Dir    
  Loop

  MsgBox ("Done")
End Sub

Note: If your dataset is huge, this could cause an out of memory error. If it does, go with powershell.

Import-Excel .\Book1.xlsx | Export-Csv .\book1.csv