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
Post an example of some lines of what you want the final output to look like.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Conver CSV to XLSX.xlsm
ABCDEF
1Txn DateDescriptionVoucher TypeDr AmountCr AmountBalance
201-01-2022IMPS-IN/200116132954/9773902678/ramjatan Txn No.S69726393Receipt1.002,01,37,347.95
303-01-2022IMPS-IN/200312912441/9880980396/B AKBAR Txn No.S97073495Receipt3350.002,01,33,997.95
JohnnyL

If you have understood what I am telling then it will be easy to insert an extra row as in the image and get the lines too in order as in csv.
 
Upvote 0
Again, your wording is not matching your expected results.

You said to insert an extra row. You mean COLUMN F right?

You also said to get the lines in order as in csv. The result you showed is from the bottom of the csv. The csv starts with dates of 31-01-2022 at the top.

Anyways, I came up with some code for you to test out. It starts out from the top of the csv, removed the sorting, and added in the Balance column.

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 BalanceOnly
    Dim CSV_ColumnMinus1                As Long, CSV_FileRow                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 21)            ' 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 IsDate(Partitioned_CSV_FileArray(ArrayRow, 2)) Or _
                IsDate(Partitioned_CSV_FileArray(ArrayRow, 3)) Or _
                IsDate(Partitioned_CSV_FileArray(ArrayRow, 10)) Then                            '   If Date found in Column 2, 3, or 10 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 16
                        ShortenedCSV_FileArray(NewArrayRow, 8) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some Dr amounts
                    Case 17
                        ShortenedCSV_FileArray(NewArrayRow, 9) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)   ' Some Descriptions
                    Case 18
                        ShortenedCSV_FileArray(NewArrayRow, 10) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                    Case 19
                        ShortenedCSV_FileArray(NewArrayRow, 11) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Nothing we need
                    Case 22
                        ShortenedCSV_FileArray(NewArrayRow, 12) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                    Case 30
                        ShortenedCSV_FileArray(NewArrayRow, 13) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Nothing we need
                    Case 33
                        ShortenedCSV_FileArray(NewArrayRow, 14) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                    Case 34
                        ShortenedCSV_FileArray(NewArrayRow, 15) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Nothing we need
                    Case 35
                        ShortenedCSV_FileArray(NewArrayRow, 16) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some cheque #s
                    Case 37
                        ShortenedCSV_FileArray(NewArrayRow, 17) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                    Case 38
                        ShortenedCSV_FileArray(NewArrayRow, 18) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Dr amounts
                    Case 40
                        ShortenedCSV_FileArray(NewArrayRow, 19) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Cr amounts
                    Case 41
                        ShortenedCSV_FileArray(NewArrayRow, 20) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Nothing we need
                    Case 44
                        ShortenedCSV_FileArray(NewArrayRow, 21) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)  ' Some Balances
                End Select
            Next                                                                                    '   Loop back
        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, 9), 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, 16) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 16)                                                 '           Append Cheque # if needed
            End If
'
            CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 18)          '       Save Column 15 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 19)          '       Save Column 16 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"
'
            BalanceOnly = Left(ShortenedCSV_FileArray(ArrayRow, 21), Len(ShortenedCSV_FileArray(ArrayRow, 21)) - 5)
            CSV_FinalArray(ArrayRow, 6) = BalanceOnly                       '       Save Column 21 of ShortenedCSV_FileArray as a decimal # to CSV_FinalArray
'
        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
'
            CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 8)           '       Save Column 8 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 10)          '       Save Column 10 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"
'
            BalanceOnly = Left(ShortenedCSV_FileArray(ArrayRow, 12), Len(ShortenedCSV_FileArray(ArrayRow, 12)) - 5)
            CSV_FinalArray(ArrayRow, 6) = BalanceOnly                       '       Save Column 12 of ShortenedCSV_FileArray as a decimal # to CSV_FinalArray
'
        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, 13)          '       Save Column 12 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 14)          '       Save Column 13 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"
'
            BalanceOnly = Left(ShortenedCSV_FileArray(ArrayRow, 17), Len(ShortenedCSV_FileArray(ArrayRow, 17)) - 5)
            CSV_FinalArray(ArrayRow, 6) = BalanceOnly                       '       Save Column 17 of ShortenedCSV_FileArray as a decimal # to CSV_FinalArray
        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
 
Upvote 0
Did you test the code, because I am getting an error at
Rich (BB code): 
            BalanceOnly = Left(ShortenedCSV_FileArray(ArrayRow, 12), Len(ShortenedCSV_FileArray(ArrayRow, 12)) - 5)
Run time error 5, . Invalid procedure call or argument.
Dim BalanceOnly also not defined in the code. By adding as long, to it, still getting an error.
 
Upvote 0
Did I test the code? Now you got jokes?

I wonder if you ever made the correction to the csv file as I previously mentioned.
 
Upvote 0
JohnnyL sir, Correcting or editing a csv file is out of the question. If one file is to be corrected. I would, but every time converting a csv file and checking and correcting will be a tedious task which is just not possible.
 
Upvote 0
At least share the csv file you have edited. Maybe I will come up with a solution.
 
Upvote 0
I checked other csv files which were working correctly with the new code. I am getting the error at the same line. When I run the old code it is running perfect.
 
Upvote 0
RAJESH1960 said:
JohnnyL sir, Correcting or editing a csv file is out of the question.
Click to expand...

If you can't take 30 seconds out of your time to correct a mistake made in the csv file, I am now wondering why I spend so much time on the code. :(
 
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
721
felixstraube
F
N
  • Solved
Multiple statement lines per transaction
Replies
3
Views
442
Nygie
N

Forum statistics

Threads
1,216,495
Messages
6,130,979
Members
449,611
Latest member
Bushra

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