CODEWITHSUNDEEP
excelvbacsv
lisovaccaro
lisovaccaro

Reputation: 33956

Sheet to CSV with custom delimiter?

I need to create a CSV string with a custom delimiter from a Workbook.

I wrote this function which does the trick but problem is that looping through rows is just too slow:

Public Function CSVStringFromWB(wb As Workbook, FieldTerminator As String) As String
    Dim Line As String
    Dim r As Long
    Dim ColsCount As Long
    Dim RowsCount As Long
    ColsCount = wb.Sheets(1).UsedRange.Columns.Count
    RowsCount = wb.Sheets(1).UsedRange.Rows.Count
    With wb.Sheets(1)
        For r = 1 To RowsCount
            Line = Join(Application.Transpose(Application.Transpose(.Range(.Cells(r, 1), .Cells(r, ColsCount)).Value)), FieldTerminator)
            CSVStringFromWB = CSVStringFromWB + Line + vbNewLine
        Next r
    End With
End Function

Is there any faster alternatives to create transform a range to a CSV string with a custom delimiter?

Upvotes: 0

Views: 361

Answers (2)

Tim Williams
Tim Williams

Reputation: 166146

This is approx 20x faster (using 6k rows x 20 cols) 

Public Function FasterCSVStringFromWB(wb As Workbook, _
                                      FieldSep As String) As String
    Dim data, lines() As String, line As String, sep As String
    Dim ColsCount As Long, r As Long, c As Long
    Dim RowsCount As Long

    data = wb.Sheets(1).UsedRange.Value
    ColsCount = UBound(data, 2)
    RowsCount = UBound(data, 1)
    ReDim lines(1 To RowsCount)

    For r = 1 To RowsCount
        sep = ""
        line = ""
        For c = 1 To ColsCount
            line = line & sep & data(r, c)
            If c = 1 Then sep = FieldSep
        Next c
        lines(r) = line
    Next r

    FasterCSVStringFromWB = Join(lines, vbNewLine)
End Function

Upvotes: 2

Joe
Joe

Reputation: 871

Reading through the entire excel sheet cell by cell is slow. If you can, save the desired excel rows/cols as a separate csv file, then write a separate small program to change the commas in the csv to something else.

If you have to go through the entire excel sheet with vba, then what you have done is probably the only way.

If you have to deal with a large amount of excel files, try to copy the rows/cols you want, then paste into a new worksheet, then save the worksheet as csv then change the delimiter afterwards.

Upvotes: 0

Related Questions