Convert csv to xlsx

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello code experts,
I have this csv file which was converted from pdf. The columns are not in one order to get the data in a columnar view. It will take more than a hour to arrange each line in a particular format. JohnnyL's code has helped me a lot to get the columns in order in less than a second. I am facing one problem in one of the conversions. The code is not able to concatenate the Cheque No. to the Description in 3 different rows and in one of the lines it is not taking the amount. Hence the balances are not matching. I have colored the lines with errors. Need your expertise to correct the code. Thanks.
Conver CSV to XLSX.xlsm
 
Last edited:
Sort by date Sort by votes
Please let me know what was wrong and how it should have looked.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
csv to slsx convertor.xlsm
ABCDEF
9521-01-2022BY INST 5020 : CTO386-1DAY LAT Txn No.S6117680Receipt18000.001,99,76,778.55 Dr.
9621-01-2022NEFT_IN:CMS2355644388/0028/ NEFT SAIPRIYADARSHINI PNB Txn No.S5999448Receipt637.001,99,94,778.55 Dr.
9721-01-2022TO TR BAJAJ ALLIANZGENERAL INSURANCE -215410 Txn No.M103376 Branch Name - SARJAPUR ROA,1,99,95
9821-01-2022DHC LOGISTICS PVT LTD Txn No.S2141266 Cheque No. 297835Payment10050.001,99,74,175.55 Dr.
9920-01-2022To:0040005500052250:SAIPRIYADARSHINI Txn No.S98751533Payment1100.001,99,64,125.55 Dr.
JohnnyL


I checked & got the same values missing. Maybe the problem lies in the csv sheet. Must have got edited in that place by mistake I hope. I have kept you on your toes for long now. Hope it is resolved soon.. I will convert the same sheet again from pdf to csv and check tonight.
 
Upvote 0
You do realize that that is is the same line of the csv file that i mentioned a long time ago that needs correcting?
 
Upvote 0
JohnnyL. I tried more than 5 files today. I think I found one common problem in all the conversions. The balance column amount is posted partly only when the row contains Branch Name, The branch name too is partially converted.. Otherwise, all the csv files are converted correctly where there is no branch name.
Check only the rows containing Branch Name.
If you can fix this then it will be great, or you can remove the code connected to the Branch Name completely.
Still if you can't understand or fix the error, I will share the 5 files if required.
 
Upvote 0
One major problem we are facing is that you have no standard for the format of the csv files. This latest csv file has writing in the column A after the transaction #s end. The code is designed to look for 'Txn No.' in the column A and rows after that in column A that are not blank are considered a transaction line.

How would you suggest to determine the last transaction line? I have an idea that I am going to try, but I am curious what your thoughts are considering, as I said, it seems there is no set standard format of the csv files you are getting.

I will look into the other two issues to see what caused them, but I am going to guess that they are caused by a different format being used.
 
Upvote 0
The following is the latest code I came up with:

VBA Code: 
Sub LoadCSV_FileTest4()
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    Dim startTime       As Single
    Dim CSV_FileToOpen  As Variant
'
    CSV_FileToOpen = Application.GetOpenFilename("Text files,*.csv", , "Select file", , False)  ' Save full path of CSV file to CSV_FileToOpen
    If CSV_FileToOpen = False Then                                                              ' Exit Sub if user cancelled
        MsgBox "No file selected - exiting"
        Exit Sub
    End If
'
    startTime = Timer                                                                           ' Start the stopwatch
'
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    Dim ArrayColumn                     As Long, ArrayRow                   As Long, NewArrayRow            As Long
    Dim CSV_ColumnMinus1                As Long, CSV_FileRow                As Long, FirstTransactionRow    As Long
    Dim FreeFileNumber                  As Long
    Dim RowNumber                       As Long
    Dim All_CSV_RowsFromCSV_FileArray   As Variant, CSV_FileRowColumnsArray As Variant
    Dim ShortenedCSV_FileArray          As Variant, CSV_FinalArray          As Variant
    Dim Partitioned_CSV_FileArray       As Variant
    Dim wsDestination                   As Worksheet
'
    Set wsDestination = Sheets("JohnnyL")                                                           ' <--- Set this to the sheet name to dump CSV_FinalArray
'
    FreeFileNumber = FreeFile                                                                       ' Get an unused file number
    Open CSV_FileToOpen For Input As #FreeFileNumber
'
    If Err.Number <> 0 Then                                                                         ' If error occurred then ...
        MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!"                         '   Display error #
        Exit Sub                                                                                    '   Exit sub
    End If
'
    All_CSV_RowsFromCSV_FileArray = Split(Input(LOF(FreeFileNumber), #FreeFileNumber), vbCrLf)      ' Load all Rows in file to All_CSV_RowsFromCSV_FileArray
    Close #FreeFileNumber
'
    RowNumber = 0                                                                                       ' Initialize RowNumber
'
    ReDim Partitioned_CSV_FileArray(1 To UBound(All_CSV_RowsFromCSV_FileArray), 1 To 100)               ' Set rows/columns for Partitioned_CSV_FileArray
'
    For CSV_FileRow = LBound(All_CSV_RowsFromCSV_FileArray) To UBound(All_CSV_RowsFromCSV_FileArray)    ' Loop through all rows of CSV file
        If All_CSV_RowsFromCSV_FileArray(CSV_FileRow) <> vbNullString Then                              '   If CSV row is not blank then ...
            CSV_FileRowColumnsArray = Split(All_CSV_RowsFromCSV_FileArray(CSV_FileRow), ",")            '       Load contents of row to CSV_FileRowColumnsArray
'
            RowNumber = RowNumber + 1                                                                   '       Increment RowNumber
'
            For CSV_ColumnMinus1 = LBound(CSV_FileRowColumnsArray) To UBound(CSV_FileRowColumnsArray)   '       Loop through columns
                Partitioned_CSV_FileArray(RowNumber, CSV_ColumnMinus1 + 1) = _
                        CSV_FileRowColumnsArray(CSV_ColumnMinus1)                                       '           Add values to Partitioned_CSV_FileArray
            Next                                                                                        '       Loop back
        End If
    Next                                                                                                ' Loop back
'
''Sheets("Sheet2").Range("A1").Resize(UBound(Partitioned_CSV_FileArray, 1), UBound(Partitioned_CSV_FileArray, 2)) = Partitioned_CSV_FileArray
'
'-------------------------------------------------------------------------------------------------
'
    ReDim ShortenedCSV_FileArray(1 To UBound(Partitioned_CSV_FileArray, 1), 1 To 33)            ' Set the row #s and # of columns for ShortenedCSV_FileArray
'
    NewArrayRow = 0                                                                             ' Initialize NewArrayRow
'
    For ArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                                    ' Loop through all the rows of the Partitioned_CSV_FileArray
        If Partitioned_CSV_FileArray(ArrayRow, 1) = "Txn No." Then                              '   If 'Txn No.' is found then ...
            FirstTransactionRow = ArrayRow + 1                                                  '       Save Start row of transactions to FirstTransactionRow
            Exit For                                                                            '       Exit loop
        End If
    Next                                                                                        ' Loop back
'
    For ArrayRow = FirstTransactionRow To UBound(Partitioned_CSV_FileArray, 1)                  ' Loop through all the rows of the Partitioned_CSV_FileArray

        If Left(Partitioned_CSV_FileArray(ArrayRow, 1), 1) Like "[A-Za-z]" Then                 '   If first character in column A is alphabetic then ...
            If Mid(Partitioned_CSV_FileArray(ArrayRow, 1), 2, 1) Like "#" Then                  '       If second character in column A is numeric then ...
                NewArrayRow = NewArrayRow + 1                                                   '       Increment NewArrayRow
'
                For ArrayColumn = 1 To UBound(Partitioned_CSV_FileArray, 2)                     '   Loop through all columns of the Partitioned_CSV_FileArray
                    Select Case ArrayColumn                                                     '       Save the needed data into ShortenedCSV_FileArray ...
                        Case 1 To 3
                            ShortenedCSV_FileArray(NewArrayRow, ArrayColumn) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                        Case 6
                            ShortenedCSV_FileArray(NewArrayRow, 4) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some Descriptions
                        Case 8
                            ShortenedCSV_FileArray(NewArrayRow, 5) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some addresses
                        Case 10
                            ShortenedCSV_FileArray(NewArrayRow, 6) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some Dates
                        Case 13
                            ShortenedCSV_FileArray(NewArrayRow, 7) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some cheque #s
                        Case 14
                            ShortenedCSV_FileArray(NewArrayRow, 8) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some cheque #s
                        Case 16
                            ShortenedCSV_FileArray(NewArrayRow, 9) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some Dr amounts
                        Case 17
                            ShortenedCSV_FileArray(NewArrayRow, 10) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some Descriptions
                        Case 18
                            ShortenedCSV_FileArray(NewArrayRow, 11) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                        Case 19
                            ShortenedCSV_FileArray(NewArrayRow, 12) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some CR amounts
                        Case 20
                            ShortenedCSV_FileArray(NewArrayRow, 13) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                        Case 21
                            ShortenedCSV_FileArray(NewArrayRow, 14) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                        Case 22
                            ShortenedCSV_FileArray(NewArrayRow, 15) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                        Case 23
                            ShortenedCSV_FileArray(NewArrayRow, 16) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                        Case 25
                            ShortenedCSV_FileArray(NewArrayRow, 17) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some CR amounts
                        Case 26
                            ShortenedCSV_FileArray(NewArrayRow, 18) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  '
                        Case 27
                            ShortenedCSV_FileArray(NewArrayRow, 19) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  '
                        Case 28
                            ShortenedCSV_FileArray(NewArrayRow, 20) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  '
                        Case 29
                            ShortenedCSV_FileArray(NewArrayRow, 21) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                        Case 30
                            ShortenedCSV_FileArray(NewArrayRow, 22) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some amounts
                        Case 33
                            ShortenedCSV_FileArray(NewArrayRow, 23) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                        Case 34
                            ShortenedCSV_FileArray(NewArrayRow, 24) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                        Case 35
                            ShortenedCSV_FileArray(NewArrayRow, 25) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts, cheque #s
                        Case 36
                            ShortenedCSV_FileArray(NewArrayRow, 26) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  '
                        Case 37
                            ShortenedCSV_FileArray(NewArrayRow, 27) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                        Case 38
                            ShortenedCSV_FileArray(NewArrayRow, 28) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Dr amounts
                        Case 40
                            ShortenedCSV_FileArray(NewArrayRow, 29) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                        Case 41
                            ShortenedCSV_FileArray(NewArrayRow, 30) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Nothing we need
                        Case 42
                            ShortenedCSV_FileArray(NewArrayRow, 31) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances*
                        Case 43
                            ShortenedCSV_FileArray(NewArrayRow, 32) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  '
                        Case 44
                            ShortenedCSV_FileArray(NewArrayRow, 33) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                    End Select
                Next                                                                                '   Loop back
            End If
        End If
    Next                                                                                            ' Loop back
'
''Sheets("Sheet3").Range("A1").Resize(UBound(ShortenedCSV_FileArray, 1), UBound(ShortenedCSV_FileArray, 2)) = ShortenedCSV_FileArray
'
    Debug.Print NewArrayRow & " Rows of data processed from the CSV file." ' Display the # of data lines that were processed to the 'Immediate Window'(CTRL-G)
'
    ReDim CSV_FinalArray(1 To NewArrayRow, 1 To 6)                                                  ' Set the # of rows & columns for the CSV_FinalArray
'
    For ArrayRow = 1 To UBound(CSV_FinalArray, 1)                                                   ' Loop through all the rows of the CSV_FinalArray
        If ShortenedCSV_FileArray(ArrayRow, 3) = vbNullString Then                                  '   If column 3 = blank then ...
            CSV_FinalArray(ArrayRow, 1) = ShortenedCSV_FileArray(ArrayRow, 6)                       '       Save Column 6 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 2) = Replace(Replace(ShortenedCSV_FileArray(ArrayRow, 10), Chr(10), ""), """", "") & _
                    " Txn No." & ShortenedCSV_FileArray(ArrayRow, 1)                                        ' Delete Line Feeds, quotations, append Txn #s as needed
            If ShortenedCSV_FileArray(ArrayRow, 5) <> vbNullString And ShortenedCSV_FileArray(ArrayRow, 5) <> "-" Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Branch Name - " & _
                        Replace(ShortenedCSV_FileArray(ArrayRow, 5), Chr(10), "")                           '           Append Branch Name if needed
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 7) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 7)                                                 '           Append Cheque # if needed
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 25) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 25)                                                 '           Append Cheque # if needed
            End If
'
            CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 28)          '       Save Column 28 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 29)          '       Save Column 29 of ShortenedCSV_FileArray to CSV_FinalArray
'
            If CSV_FinalArray(ArrayRow, 4) <> vbNullString Then CSV_FinalArray(ArrayRow, 3) = "Payment" ' If column 4 is not blank then set "Payment"
            If CSV_FinalArray(ArrayRow, 5) <> vbNullString Then CSV_FinalArray(ArrayRow, 3) = "Receipt" ' If column 5 is not blank then set "Receipt"
'
            If ShortenedCSV_FileArray(ArrayRow, 31) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 30) & _
                        "," & ShortenedCSV_FileArray(ArrayRow, 31)), """", "")              '       Save Column 31 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 33) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 30) & "," & _
                        ShortenedCSV_FileArray(ArrayRow, 31) & "," & ShortenedCSV_FileArray(ArrayRow, 32) & _
                        "," & ShortenedCSV_FileArray(ArrayRow, 33)), """", "")              '       Save Column 33 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
        ElseIf ShortenedCSV_FileArray(ArrayRow, 2) <> vbNullString Then                             '   If column 2 is not blank then ...
            CSV_FinalArray(ArrayRow, 1) = ShortenedCSV_FileArray(ArrayRow, 2)               '       Save Column 2 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 2) = Replace(Replace(ShortenedCSV_FileArray(ArrayRow, 3), Chr(10), ""), """", "") & _
                    " Txn No." & ShortenedCSV_FileArray(ArrayRow, 1)                                        ' Delete Line Feeds, quotations, append Txn #s as needed
'
            If ShortenedCSV_FileArray(ArrayRow, 5) <> vbNullString And ShortenedCSV_FileArray(ArrayRow, 5) <> "-" Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Branch Name - " & _
                        Mid(Replace(ShortenedCSV_FileArray(ArrayRow, 5), Chr(10), ""), 2, Len(Replace(ShortenedCSV_FileArray(ArrayRow, 5), Chr(10), "")) - 2)                           '           Append Branch Name if needed
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 7) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 7)                                                     '           Append Cheque # if needed
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 8) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 8)                                                     '           Append Cheque # if needed
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 9)) > 1 Then
                CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 9)           '       Save Column 9 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 10)) > 1 Then
                CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 10)          '       Save Column 1 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 11)) > 1 Then
                CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 11)          '       Save Column 11 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 17)) > 1 Then
                CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 17)          '       Save Column 17 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If CSV_FinalArray(ArrayRow, 4) <> vbNullString Then CSV_FinalArray(ArrayRow, 3) = "Payment"     ' If column 4 is not blank then set "Payment"
            If CSV_FinalArray(ArrayRow, 5) <> vbNullString Then CSV_FinalArray(ArrayRow, 3) = "Receipt"     ' If column 5 is not blank then set "Receipt"
'
            If ShortenedCSV_FileArray(ArrayRow, 15) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 12) & "," & _
                        ShortenedCSV_FileArray(ArrayRow, 13) & "," & ShortenedCSV_FileArray(ArrayRow, 14) & _
                        "," & ShortenedCSV_FileArray(ArrayRow, 15)), """", "")              '       Save Column 15 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 12)) > 4 Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 12)), """", "") '       Save Column 12 of ShortenedCSV_FileArray as a decimal # to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 13)) > 4 Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 12) & "," & _
                        ShortenedCSV_FileArray(ArrayRow, 13)), """", "")                    '       Save Column 13 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 14)) > 4 Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 12) & "," & _
                        ShortenedCSV_FileArray(ArrayRow, 13) & "," & ShortenedCSV_FileArray(ArrayRow, 14)), """", "")   ' Save Column 14 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 16)) > 4 Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 13) & "," & _
                        ShortenedCSV_FileArray(ArrayRow, 14) & "," & ShortenedCSV_FileArray(ArrayRow, 15) & "," & ShortenedCSV_FileArray(ArrayRow, 16)), """", "")   ' Save Column 16 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 21)) > 4 Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 18) & "," & _
                        ShortenedCSV_FileArray(ArrayRow, 19) & "," & ShortenedCSV_FileArray(ArrayRow, 20) & "," & ShortenedCSV_FileArray(ArrayRow, 21)), """", "")   ' Save Column 21 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
        Else
            CSV_FinalArray(ArrayRow, 1) = ShortenedCSV_FileArray(ArrayRow, 3)
            CSV_FinalArray(ArrayRow, 2) = Replace(Replace(ShortenedCSV_FileArray(ArrayRow, 4), Chr(10), ""), """", "") & _
                    " Txn No." & ShortenedCSV_FileArray(ArrayRow, 1)                                            ' Delete Line Feeds, quotations, append Txn #s as needed
'
            If ShortenedCSV_FileArray(ArrayRow, 5) <> vbNullString And ShortenedCSV_FileArray(ArrayRow, 5) <> "-" Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Branch Name - " & _
                        Replace(ShortenedCSV_FileArray(ArrayRow, 5), Chr(10), "")                               '           Append Branch Name if needed
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 7) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 7)                                                     '           Append Cheque # if needed
            End If
'
            CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 22)          '       Save Column 22 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 23)          '       Save Column 23 of ShortenedCSV_FileArray to CSV_FinalArray
'
            If CSV_FinalArray(ArrayRow, 4) <> vbNullString Then CSV_FinalArray(ArrayRow, 3) = "Payment"         '      If column D is not blank then "Payment"
            If CSV_FinalArray(ArrayRow, 5) <> vbNullString Then CSV_FinalArray(ArrayRow, 3) = "Receipt"         '      If column E is not blank then "Receipt"
'
            If Len(ShortenedCSV_FileArray(ArrayRow, 24)) > 4 Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 24)), """", "")  '       Save Column 24 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 25) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 24) & _
                        "," & ShortenedCSV_FileArray(ArrayRow, 25)), """", "")              '       Save Column 25 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
'
            If ShortenedCSV_FileArray(ArrayRow, 27) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 6) = Replace((ShortenedCSV_FileArray(ArrayRow, 24) & _
                        "," & ShortenedCSV_FileArray(ArrayRow, 25) & "," & ShortenedCSV_FileArray(ArrayRow, 26) & _
                        "," & ShortenedCSV_FileArray(ArrayRow, 27)), """", "")                       '       Save Column 27 of ShortenedCSV_FileArray to CSV_FinalArray
            End If
        End If
    Next
'
    wsDestination.UsedRange.Clear                                                                                       ' Clear the destination sheet
    wsDestination.Range("A1:F1").Value = Array("Txn Date", "Description", "Voucher Type", "Dr Amount", "Cr Amount", "Balance")  ' Add header row to sheet
'
    wsDestination.Columns("A:A").NumberFormat = "@"                                                         ' Set column to text so Excel don't mess up dates
'
    wsDestination.Range("A2").Resize(UBound(CSV_FinalArray, 1), UBound(CSV_FinalArray, 2)).Value = CSV_FinalArray       ' Display CSV_FinalArray to sheet
'
''    wsDestination.Range("E2").CurrentRegion.Sort key1:=wsDestination.Range("E2"), order1:=xlAscending, Header:=xlGuess  ' Sort columns by the E column
''    wsDestination.Range("D2").CurrentRegion.Sort key1:=wsDestination.Range("D2"), order1:=xlAscending, Header:=xlGuess  ' Sort columns by the D column
''    wsDestination.Range("A2").CurrentRegion.Sort key1:=wsDestination.Range("A2"), order1:=xlAscending, Header:=xlGuess  ' Sort columns by the A Column
'
    wsDestination.Columns("D:F").NumberFormat = "0.00"                                                          ' Format Columns D:E to two decimal places
    wsDestination.UsedRange.EntireColumn.AutoFit                                                                ' Autofit used columns
'
    Application.Goto wsDestination.Range("A1")                                                                  ' Go to result sheet
'
    Application.ScreenUpdating = False                                                                          ' Turn ScreenUpdating back on
'
    Debug.Print "Time to complete = " & Timer - startTime & " seconds."                                         ' about .10 seconds
End Sub

Let me know how that works. I had to guess some of the values of columns because as I mentioned, the data is all over the place so no telling what each value represesnts.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Bulk Convert CSV to XLSX Files While Retaining the Leading Zero
Replies
4
Views
2K
joyfu1
J
S
  • Question
Is there any other way to convert xlsb. to xlsx. file
Replies
2
Views
1K
Sinh Le
S
H
  • Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
2K
HeinrichPaul
H
G
  • Question
Converting a ASCII text file with strange delimiters.
2
Replies
14
Views
751
felixstraube
F
N
  • Solved
Multiple statement lines per transaction
Replies
3
Views
446
Nygie
N

Forum statistics

Threads
1,216,756
Messages
6,132,533
Members
449,733
Latest member
Nameless_

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top