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
As I expected, the missing cheque #s were a result of the cheque #s located in a different column than the other ones.

I added code to handle that.

As far as the other line that doesn't have the amount and stuff, that is a result of the line in the csv file. Somewhere in that line, an extra column was inserted, so the values are off by 1 column. Fix that line in the csv file and it should fix that issue.

VBA Code: 
Sub LoadCSV_FileTest4a()
    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
    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 18)            ' 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)
                    Case 8
                        ShortenedCSV_FileArray(NewArrayRow, 5) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 10
                        ShortenedCSV_FileArray(NewArrayRow, 6) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 13
                        ShortenedCSV_FileArray(NewArrayRow, 7) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 16
                        ShortenedCSV_FileArray(NewArrayRow, 8) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 17
                        ShortenedCSV_FileArray(NewArrayRow, 9) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 18
                        ShortenedCSV_FileArray(NewArrayRow, 10) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 19
                        ShortenedCSV_FileArray(NewArrayRow, 11) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 30
                        ShortenedCSV_FileArray(NewArrayRow, 12) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 33
                        ShortenedCSV_FileArray(NewArrayRow, 13) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 34
                        ShortenedCSV_FileArray(NewArrayRow, 14) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
'
                    Case 35
                        ShortenedCSV_FileArray(NewArrayRow, 15) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
'
                    Case 38
                        ShortenedCSV_FileArray(NewArrayRow, 16) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 40
                        ShortenedCSV_FileArray(NewArrayRow, 17) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                    Case 41
                        ShortenedCSV_FileArray(NewArrayRow, 18) = Partitioned_CSV_FileArray(ArrayRow, ArrayColumn)
                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 5)                                                  ' 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, 15) <> vbNullString Then
                CSV_FinalArray(ArrayRow, 2) = CSV_FinalArray(ArrayRow, 2) & " Cheque No. " & _
                        ShortenedCSV_FileArray(ArrayRow, 15)                                                 '           Append Cheque # if needed
            End If
'
            CSV_FinalArray(ArrayRow, 4) = ShortenedCSV_FileArray(ArrayRow, 16)          '       Save Column 15 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 17)          '       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"
'
        ElseIf ShortenedCSV_FileArray(ArrayRow, 2) <> vbNullString Then                             '   If column 2 = 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"
'
        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, 12)          '       Save Column 12 of ShortenedCSV_FileArray to CSV_FinalArray
            CSV_FinalArray(ArrayRow, 5) = ShortenedCSV_FileArray(ArrayRow, 13)          '       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"
        End If
    Next
'
    wsDestination.UsedRange.Clear                                                                                       ' Clear the destination sheet
    wsDestination.Range("A1:E1").Value = Array("Txn Date", "Description", "Voucher Type", "Dr Amount", "Cr Amount")     ' 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:E").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 .05 seconds
End Sub
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Yes, Delete one comma before '297839', and then save the csv file.
 
Upvote 0
There ain't any comma in the number. And the line below that the data is in the same order. The below line has been converted correctly. I wonder why it has not taken the above line (Lines 153 and 154)
CC Jan 2022.csv
ABCDEFGHIJKLMNOPQR
152S5999448########NEFT_IN:CMS2355644388/0 028/ NEFT SAI PRIYADARSHINI PNB-637
153M103376########TO TR BAJAJ ALLIANZ GENERAL INSURANCE - 215410SARJAPUR ROAD, BANGALORE29783921240
154S2141266########DHC LOGISTICS PVT LTD-29783510050
155S98751533########To:0040005500052250:SAI PRIYADARSHINI-1100
CC Jan 2022


Conver CSV to XLSX.xlsm
ABCDE
7521-01-2022DHC LOGISTICS PVT LTD Txn No.S2141266 Cheque No. 297835Payment10050.00
7621-01-2022REJECT:28600:FUNDSINSUFFICIENT Txn No.S8055280Payment13860.00
7721-01-2022NEFT_IN:CMS2355644388/0028/ NEFT SAIPRIYADARSHINI PNB Txn No.S5999448Receipt637.00
7821-01-2022BY INST 28600 : CTO386-1DAY LAT Txn No.S6117680Receipt13860.00
7921-01-2022BY INST 5020 : CTO386-1DAY LAT Txn No.S6117680Receipt18000.00
8021-01-2022TO TR BAJAJ ALLIANZGENERAL INSURANCE -215410 Txn No.M103376 Branch Name - SARJAPUR ROA
JohnnyL
 
Upvote 0
Check lines 153 and 154 and match with 75 and 80.
 
Upvote 0
open the csv file with notepad and you will see a ton of commas, go down to the line needing corrected and make the change I suggested, then save the file.
 
Upvote 0
Can I suggest you something.? Each Txn No. contains all the other details in the same row but in different columns but they are in one particular order. You can write the code to write and get line by line in xlsx. That way it will be in the same order and will display the closing balance accurately.
 
Upvote 0
I noticed that, If there is data in 6 cells in each row then there is no cheque no. and if there is data in 7 rows then there is cheque no. The amount in the end is the closing balance, one cell before is credit and 3 cells before is debit. and so on
 
Upvote 0
Even the cheque number is in a fixed postion in the row, 6th cell to the left of closing balance The branch name is the only one that is not fixed. That you can include in ELSE if you are using the if formula. This way you can include the closing balance row too in the JohnnyL sheet but without the Dr and Cr, (display Number format). That way I don't have to check the balance if it is correct or mismatch.
 
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,496
Messages
6,130,982
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