Read Large excel data and save into csv with delimiter "|"

[Mallesh23]

Hi Team,

I have one hundred thousands rows of data in excel. and 20 columns in it

I want to read excel data and write it into csv file with delimiter "Pipe symbol"

what is best way of doing it
array,dictionary, any alternate solution


data starts from (Range("a10:a1,00,000")

My attempted VBA Code:

Sub ExportActiveSheetToCSV()
    Dim data() As Variant
    Dim output As String
    Dim fileName As String
    Dim fso As Object
    Dim file As Object
    Dim i As Long
    Dim j As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Get the active workbook
    Dim wb As Workbook
    Set wb = ThisWorkbook 'Change to ActiveWorkbook if needed
    
    'Get the active worksheet
    Dim ws As Worksheet
    Set ws = wb.ActiveSheet
    
    'Read the data to an array
    data = ws.Range("A11").CurrentRegion.Value
    
    'Create the output string from the array
    For i = 1 To UBound(data, 1)
        For j = 1 To UBound(data, 2)
            output = output & data(i, j)
            If j < UBound(data, 2) Then output = output & "|"
        Next j
        output = output & vbNewLine
    Next i
    
    'Export the data to a CSV file
    fileName = ws.Name & ".csv"
    
    'Get the directory path for the CSV file
    Dim csvPath As String
    csvPath = wb.Sheets("Sheet1").Range("C7").Value & "\"
    
    'Create the directory if it doesn't exist
    If Not fso.FolderExists(csvPath) Then
        fso.CreateFolder csvPath
    End If
    
    'Save the CSV file
    Set file = fso.CreateTextFile(csvPath & fileName, True, True)
    file.Write output
    file.Close
    
    'Save the workbook as an .xlsm file with the original file name and path
    wb.SaveAs ThisWorkbook.FullName, FileFormat:=52
End Sub

Thanks
mg

 

[Joe4]

Have a look here: Macro to Convert Excel to Pipe Delimited Text File (3 Methods)
There are a few different VBA methods to choose from.