Convert csv to xlsx

RAJESH1960 · Feb 16, 2022

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

RAJESH1960 · Feb 22, 2022

Alex Blakenburg said:
Thanks for taking the time to respond. I think I am happy with were my code landed and since you have a working solution I won't worry about adding the Voucher Type column and leave you in Johnny's capable hands.

You mean to say that the ball is in JohnnyL's court now. ?
Thank you very much Alex. It was really great to see and learn so many things from your code.

johnnyL · Feb 22, 2022

How about:

VBA Code:

Sub LoadCSV_FileTest4()
'
    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                                                              ' If user cancelled then ...
        MsgBox "No file selected - exiting"                                                     '   Display message box to user indicating the cancellation
        Exit Sub                                                                                '   Exit the sub
    End If
'
    startTime = Timer                                                                           ' Start the stopwatch
'
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    Dim ArrayRow                        As Long
    Dim CSV_ColumnMinus1                As Long, CSV_FileArrayColumn        As Long, CSV_FileArrayRow   As Long, CSV_FileRow    As Long
    Dim jLeft                           As Long, jCntMid                    As Long, jRight             As Long
    Dim DatesArrayRow                   As Long
    Dim FreeFileNumber                  As Long
    Dim OutputArrayColumn               As Long, OutputArrayRow             As Long
    Dim RowNumber                       As Long
    Dim BranchName                      As String, ChequeNumber             As String
    Dim All_CSV_RowsFromCSV_FileArray   As Variant, CSV_FileRowColumnsArray As Variant, Partitioned_CSV_FileArray               As Variant
    Dim CombinedAndReversedArray        As Variant
    Dim DatesArray                      As Variant, HeaderArray             As Variant, OutputArray()                           As Variant
    Dim destSht                         As Worksheet
'
    Set destSht = ThisWorkbook.Worksheets("JohnnyL")                                            ' <--- Set this to the desired sheet name
'
    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 csv file to All_CSV_RowsFromCSV_FileArray
    Close #FreeFileNumber                                                                       ' Close the csv file
'
    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 csv columns
                Partitioned_CSV_FileArray(RowNumber, CSV_ColumnMinus1 + 1) = _
                        CSV_FileRowColumnsArray(CSV_ColumnMinus1)                                       '           Save 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
'
'   All values from the CSV File are now loaded into Partitioned_CSV_FileArray
'
'-----------------------------------------------------------------------------------------------------
'
'   Intercept CSV file dates before excel gets it's hands on them
'
    ReDim DatesArray(1 To UBound(Partitioned_CSV_FileArray, 1))                                 ' Set # of rows for DatesArray
'
    DatesArrayRow = 0                                                                           ' Initialize DatesArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through Partitioned_CSV_FileArray rows
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            DatesArrayRow = DatesArrayRow + 1                                                   '       Increment DatesArrayRow
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through csv file columns starting from 2nd column
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    DatesArray(DatesArrayRow) = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '            Save the date into DatesArray
                    Exit For                                                                                        '            Exit loop
                End If
            Next                                                                                                    '       Loop back
        End If
    Next                                                                                                            ' Loop back
'
'   Dates needed are now safely stored into DatesArray
'
'-------------------------------------------------------------------------------------------------
'
    Workbooks.Open Filename:=CSV_FileToOpen                                                     ' Open the CSV file into Excel
'
    Partitioned_CSV_FileArray = ActiveWorkbook.ActiveSheet.UsedRange.Value                      ' Save the values from CSV file into Partitioned_CSV_FileArray
'
    ReDim OutputArray(1 To UBound(Partitioned_CSV_FileArray, 1), 1 To 5)                        ' Set rows/columns for OutputArray
'
    OutputArrayRow = 1                                                                          ' Initialize OutputArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through rows of Partitioned_CSV_FileArray
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            OutputArrayColumn = 0                                                               '       Initialize OutputArrayColumn
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through columns of Partitioned_CSV_FileArray
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If value found then ...
                    OutputArrayColumn = OutputArrayColumn + 1                                   '               Increment OutputArrayColumn
'
                    If OutputArrayColumn = 2 Then                                               '               If 'Description' is found then ...
                        OutputArray(OutputArrayRow, OutputArrayColumn - 1) = _
                                Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '                   Save it into OutputArray Column 1
                    End If
'
                    If OutputArrayColumn = 3 Then                                               '               If 4th value in row found then ...
                        If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "-" Then '                       If value found <> '-' then ...
                            BranchName = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)   '                       save it into BranchName
                        End If
'
                        jLeft = CSV_FileArrayColumn + 1                                         '                   Set col # to next ie. col 29->30
                        Exit For                                                                '                   Exit column loop
                    End If
                End If
            Next                                                                                '       Loop back
             
            For CSV_FileArrayColumn = UBound(Partitioned_CSV_FileArray, 2) To jLeft Step -1     '       Loop backwards from last column of csv to 4th value found column + 1
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    OutputArray(OutputArrayRow, 5) = _
                            Left(Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn), _
                            Len(Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)) - 4)  '               Save the value to Column 5 of OutputArray
                    OutputArray(OutputArrayRow, 4) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 1)    '           Save Cr amount to column 4 of OutputArray
                    OutputArray(OutputArrayRow, 3) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 3)    '           Save Dr amount to column 3 of OutputArray
'
                    If OutputArray(OutputArrayRow, 3) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Payment"                          ' If OutputArray column 3 is not blank then set "Payment"
                    If OutputArray(OutputArrayRow, 4) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Receipt"                          ' If OutputArray column 4 is not blank then set "Receipt"
'
                    jRight = CSV_FileArrayColumn - 3 - 1
                    Exit For                                                                    ' Exit loop
                End If
            Next
            
            For jCntMid = jLeft To jRight                                                       ' Loop through columns for a cheque # value between the
'                                                                                               '       Branch Name column and the DR/CR amount column
                 If Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid) <> "" Then             '   If cheque # found then ...
                    ChequeNumber = Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid)         '       Save the cheque # into ChequeNumber
                    Exit For                                                                    '       Exit loop
                 End If
            Next
'
            OutputArray(OutputArrayRow, 1) = OutputArray(OutputArrayRow, 1) & _
                    " Txn No." & Partitioned_CSV_FileArray(CSV_FileArrayRow, 1)                 ' Append Transaction # to column 1 of OutputArray
'
            If BranchName <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Branch Name - " & BranchName             ' If a BranchName was found then Append BranchName to column 1 of OutputArray
'
            If ChequeNumber <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Cheque No. " & ChequeNumber              ' If a ChequeNumber was found then Append ChequeNumber to column 1 of OutputArray
'
            OutputArray(OutputArrayRow, 1) = Replace(OutputArray(OutputArrayRow, 1), Chr(10), "")   ' delete line feeds in description
'
            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
            BranchName = ""                                                                     ' Clear BranchName
            ChequeNumber = ""                                                                   ' Clear ChequeNumber
        End If
    Next
'
'-------------------------------------------------------------------------------------------------
'
'   Combine and Reverse order of 2 arrays ... DatesArray & OutputArray into CombinedAndReversedArray
'
    ReDim CombinedAndReversedArray(1 To UBound(DatesArray), 1 To 6)
'
    RowNumber = 0
'
    For ArrayRow = UBound(DatesArray) To 1 Step -1
        If DatesArray(ArrayRow) <> vbNullString Then
            RowNumber = RowNumber + 1
'
            CombinedAndReversedArray(RowNumber, 1) = DatesArray(ArrayRow)
            CombinedAndReversedArray(RowNumber, 2) = OutputArray(ArrayRow, 1)
            CombinedAndReversedArray(RowNumber, 3) = OutputArray(ArrayRow, 2)
            CombinedAndReversedArray(RowNumber, 4) = OutputArray(ArrayRow, 3)
            CombinedAndReversedArray(RowNumber, 5) = OutputArray(ArrayRow, 4)
            CombinedAndReversedArray(RowNumber, 6) = OutputArray(ArrayRow, 5)
        End If
    Next
'
'-------------------------------------------------------------------------------------------------
'
    HeaderArray = Array("Txn Date", "Description", "Voucher Type", "Dr Amount", "Cr Amount", "Balance") ' Establish HeaderArray
'
    With destSht
        .UsedRange.Clear                                                                        ' Clear the destination sheet
        .Columns("A:A").NumberFormat = "@"                                                      ' Set column A to text so Excel don't mess up the dates
        .Range("A1").Resize(, UBound(HeaderArray) + 1) = HeaderArray                            ' Write the Header columns to destination sheet
        .Range("A1").Resize(, UBound(HeaderArray) + 1).Font.Bold = True                         ' Set the Header to Bold type font
'
        .Range("A2").Resize(UBound(CombinedAndReversedArray, 1), _
                UBound(CombinedAndReversedArray, 2)).Value = CombinedAndReversedArray           ' Display results to destination sheet
'
''        .Range("F:H").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .Columns("D:F").NumberFormat = "0.00"                                                          ' Format Columns D:E to two decimal places
        .UsedRange.Columns.AutoFit                                                              ' Resize the columns to fit the data
    End With
'
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=False                                                     ' Close the csv file that was opened earlier
    Application.DisplayAlerts = True
'
    Application.ScreenUpdating = True                                                           ' Turn ScreenUpdating back on
'
    Debug.Print "Time to complete = " & Timer - startTime & " seconds."                         ' about .5 seconds
End Sub

CopyOfConver CSV to XLSX_VBA_Pasworded.xlsm

A

B

C

D

E

F

G

1

Txn Date

Description

Voucher Type

Dr Amount

Cr Amount

Balance

2

01-01-2022

IMPS-IN/200116132954/9773902678/ramjatan Txn No.S69726393

Receipt

1.00

2,01,37,347.95

3

03-01-2022

IMPS-IN/200312912441/9880980396/B AKBAR Txn No.S97073495

Receipt

3350.00

2,01,33,997.95

4

03-01-2022

NEFT_IN:CMS2323377990/0034/ NEFT SAIPRIYADARSHINI PNB BANK Txn No.S6213774

Receipt

7392.00

2,01,26,605.95

5

03-01-2022

NEFT_IN:CMS2323377378/0034/ NEFT SAIPRIYADARSHINI PNB BANK Txn No.S6260314

Receipt

8936.00

2,01,17,669.95

6

04-01-2022

NEFT_IN:CMS2324991576/0030/ NEFT SAIPRIYADARSHINI Txn No.S21160790

Receipt

2548.00

2,01,15,121.95

7

06-01-2022

NEFT_IN:CMS2328856703/0029/ NEFT SAIPRIYADARSHINI PNB Txn No.S57819510

Receipt

8639.00

2,01,06,482.95

8

07-01-2022

BY INST 9313 : CTO386-1DAY LAT Txn No.S75158828

Receipt

34045.00

2,00,72,437.95

9

07-01-2022

REJECT:9313:FUNDSINSUFFICIENT Txn No.S78275908

Payment

34045.00

2,01,06,482.95

10

11-01-2022

BY INST 703548 : CTO386-1DAY LAT Txn No.S40009419

Receipt

5592.00

2,01,00,890.95

11

11-01-2022

IMPS-IN/201120510599/9894388772/Mr PRAK Txn No.S50001720

Receipt

9750.00

2,00,91,140.95

12

12-01-2022

NEFT_IN:N012221791060836/0025/ PUNJABHANDLOOM WORLD Txn No.S58034732

Receipt

14730.00

2,00,76,410.95

13

12-01-2022

IMPS-IN/201212156665/9449494958/CHANDRAS Txn No.S58438813

Receipt

2200.00

2,00,74,210.95

14

12-01-2022

NEFT_IN:CMS2339567883/0027/ NEFT SAIPRIYADARSHINI PNB BANK Txn No.S59433971

Receipt

1960.00

2,00,72,250.95

15

12-01-2022

IMPS-IN/201215947955/9880980396/B AKBAR Txn No.S60925182

Receipt

950.00

2,00,71,300.95

16

13-01-2022

NEFT_IN:CMS2341384059/0028/ NEFT SAIPRIYADARSHINI PNB BANK Txn No.S78097253

Receipt

398.00

2,00,70,902.95

17

14-01-2022

UPI/201431318251/P2A/8600772299/ifsc.npciSALIM HUS Txn No.S92950893

Receipt

6586.00

2,00,64,316.95

18

14-01-2022

NEFT_IN:BKIDN22014196827/0033/ THE VILLAGEHYPER MARKET Txn No.S99388905

Receipt

53715.00

2,00,10,601.95

19

17-01-2022

NEFT_IN:CMS2347712228/0027/ NEFT SAIPRIYADARSHINI PNB Txn No.S38602282

Receipt

2533.00

2,00,08,068.95

20

17-01-2022

BY INST 2225 : CTO386-1DAY LAT Txn No.S38761291

Receipt

35200.00

1,99,72,868.95

21

17-01-2022

BY INST 6213 : CTO386-1DAY LAT Txn No.S38761291

Receipt

21350.00

1,99,51,518.95

22

17-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S43317822

Receipt

13000.00

1,99,38,518.95

23

17-01-2022

To:0040000400022033:THIRDPARTY Txn No.S43356097

Payment

25000.00

1,99,63,518.95

24

17-01-2022

NEFT_IN:N017221796938992/0034/ SUPER TASTYBAKERY FOODS INDIA PVT Txn No.S44193305

Receipt

8640.00

1,99,54,878.95

25

17-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S47688222

Receipt

13000.00

1,99,41,878.95

26

17-01-2022

To:0040000400022051:THIRDPARTY Txn No.S47704379

Payment

6000.00

1,99,47,878.95

27

17-01-2022

To:0040000400037202:SANJEEV KUMAR Txn No.S47719590

Payment

4848.00

1,99,52,726.95

28

17-01-2022

CDS/CRTR/127710B1127710/2872/17-01-2022 Txn No.S48313360

Receipt

38500.00

1,99,14,226.95

29

17-01-2022

CDS/CRTR/127710B1127710/2876/17-01-2022 Txn No.S48353282

Receipt

45500.00

1,98,68,726.95

30

17-01-2022

NEFT_OUT:PUNBH22017574102/Shivanaa Homes/ICIC0000052/005205009149 Txn No.S48928367

Payment

28650.00

1,98,97,376.95

31

17-01-2022

NEFT_OUT:PUNBH22017574130/Shivanaa Homes/ICIC0000052/005205009149 Txn No.S48927778

Payment

22579.00

1,99,19,955.95

32

17-01-2022

NEFT_OUT:PUNBH22017574163/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S48935235

Payment

32534.00

1,99,52,489.95

33

17-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S48934634

Receipt

28000.00

1,99,24,489.95

34

17-01-2022

NEFT_OUT:PUNBH22017574241/ShyamInnovations/KKBK0001435/0413090674 Txn No.S48942860

Payment

13256.00

1,99,37,745.95

35

17-01-2022

To:0335002100027863:MAYUR TEX Txn No.S48942946

Payment

25000.00

1,99,62,745.95

36

17-01-2022

NEFT_OUT:PUNBH22017574509/Sumit Kumar/HDFC0000076/50100453150353 Txn No.S48970678

Payment

1500.00

1,99,64,245.95

37

18-01-2022

D13152402CRTO004000NG00026214 Txn No.S49438594

Payment

29250.00

1,99,93,495.95

38

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115845 Txn No.S49516917

Payment

236.00

1,99,93,731.95

39

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115844 Txn No.S49516938

Payment

236.00

1,99,93,967.95

40

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115843 Txn No.S49516956

Payment

236.00

1,99,94,203.95

41

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115846 Txn No.S49516971

Payment

236.00

1,99,94,439.95

42

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115847 Txn No.S49516987

Payment

236.00

1,99,94,675.95

43

18-01-2022

SHORTFAL REC- O/W RtrnChrgs: 9313 Txn No.S49517001

Payment

118.00

1,99,94,793.95

44

18-01-2022

SHORTFAL REC- LedgerFolio Charges from 01-10-2021 Txn No.S49517019

Payment

1144.60

1,99,95,938.55

45

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:321801 Txn No.S49517037

Payment

236.00

1,99,96,174.55

46

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404446 Txn No.S49517054

Payment

236.00

1,99,96,410.55

47

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404403 Txn No.S49517065

Payment

236.00

1,99,96,646.55

48

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404479 Txn No.S49517085

Payment

236.00

1,99,96,882.55

49

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404447 Txn No.S49517098

Payment

236.00

1,99,97,118.55

50

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:297834 Txn No.S49517124

Payment

236.00

1,99,97,354.55

51

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404480 Txn No.S49517138

Payment

236.00

1,99,97,590.55

52

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115904 Txn No.S49517150

Payment

236.00

1,99,97,826.55

53

18-01-2022

IW CHQ : 297833 REJ Txn No.S52754065

Payment

236.00

1,99,98,062.55

54

18-01-2022

BY INST 961315 : CTO386-1DAY LAT Txn No.S55077092

Receipt

36080.00

1,99,61,982.55

55

18-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S60472346

Receipt

98000.00

1,98,63,982.55

56

18-01-2022

To:0335002100027863:MAYUR TEX Txn No.S60506843

Payment

100000.00

1,99,63,982.55

57

18-01-2022

CDS/CRTR/120200B1120200/1904/18-01-2022 Txn No.S64677210

Receipt

15000.00

1,99,48,982.55

58

18-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S65303152

Receipt

9500.00

1,99,39,482.55

59

18-01-2022

NEFT_OUT:PUNBH22018894955/IndigoIndustries/HDFC0000251/50200005260507 Txn No.S65322005

Payment

16600.00

1,99,56,082.55

60

18-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S65322115

Receipt

500.00

1,99,55,582.55

61

18-01-2022

NEFT_OUT:PUNBH22018895008/P Rosy/UBIN0902802/520101073176733 Txn No.S65322173

Payment

8000.00

1,99,63,582.55

62

19-01-2022

SHORTFAL REC- Inw RtrnChrgs:297833 Txn No.S65885600

Payment

236.00

1,99,63,818.55

63

19-01-2022

SHORTFAL REC- Inw RtrnChrgs:404458 Txn No.S65886483

Payment

236.00

1,99,64,054.55

64

19-01-2022

SHORTFAL REC- Inw RtrnChrgs:404448 Txn No.S65886548

Payment

236.00

1,99,64,290.55

65

19-01-2022

NEFT_IN:CMS2351761982/0026/ NEFT SAIPRIYADARSHINI PNB Txn No.S71440598

Receipt

15081.00

1,99,49,209.55

66

19-01-2022

To:0040005500052250:SAIPRIYADARSHINI Txn No.S71468006

Payment

10000.00

1,99,59,209.55

67

19-01-2022

NEFT_IN:CMS2351784603/0026/ NEFT SAIPRIYADARSHINI PNB BANK Txn No.S71503430

Receipt

7881.00

1,99,51,328.55

68

19-01-2022

To:0040000400022033:THIRDPARTY Txn No.S71848111

Payment

3067.00

1,99,54,395.55

69

19-01-2022

To:0040000400022033:THIRDPARTY Txn No.S71861881

Payment

2000.00

1,99,56,395.55

70

19-01-2022

BAJAJ ALLIANZ GENERALINSURANCE CO LTD Txn No.M654161 Cheque No. 297837

Payment

39294.00

1,99,95,689.55

71

20-01-2022

NEFT_IN:N020221800403427/0022/ THE INDOORCOLLECTION Txn No.S85418472

Receipt

3000.00

1,99,92,689.55

72

20-01-2022

BY INST 4809 : CTO386-1DAY LAT Txn No.S89723704

Receipt

29664.00

1,99,63,025.55

73

20-01-2022

To:0040005500052250:SAIPRIYADARSHINI Txn No.S98751533

Payment

1100.00

1,99,64,125.55

74

21-01-2022

DHC LOGISTICS PVT LTD Txn No.S2141266 Cheque No. 297835

Payment

10050.00

1,99,74,175.55

75

21-01-2022

TO TR BAJAJ ALLIANZGENERAL INSURANCE -215410 Txn No.M103376 Branch Name - SARJAPUR ROAD,BANGALORE Cheque No. 297839

Payment

21240.00

1,99,95,415.55

76

21-01-2022

NEFT_IN:CMS2355644388/0028/ NEFT SAIPRIYADARSHINI PNB Txn No.S5999448

Receipt

637.00

1,99,94,778.55

77

21-01-2022

BY INST 5020 : CTO386-1DAY LAT Txn No.S6117680

Receipt

18000.00

1,99,76,778.55

78

21-01-2022

BY INST 28600 : CTO386-1DAY LAT Txn No.S6117680

Receipt

13860.00

1,99,62,918.55

79

21-01-2022

REJECT:28600:FUNDSINSUFFICIENT Txn No.S8055280

Payment

13860.00

1,99,76,778.55

80

21-01-2022

OW CHQ : 28600 REJ Txn No.S8826093

Payment

177.00

1,99,76,955.55

81

25-01-2022

SHORTFAL REC- Inw RtrnChrgs:404449 Txn No.S59088095

Payment

236.00

1,99,77,191.55

82

25-01-2022

BY INST 13778 : CTO386-1DAY LAT Txn No.S64215257

Receipt

200000.00

1,97,77,191.55

83

25-01-2022

BY INST 2310 : CTO386-1DAY LAT Txn No.S64215257

Receipt

2730.00

1,97,74,461.55

84

25-01-2022

BY INST 6228 : CTO386-1DAY LAT Txn No.S64215257

Receipt

8692.00

1,97,65,769.55

85

25-01-2022

BY INST 8 : CTO386-1 DAYLAT Txn No.S64215257

Receipt

30000.00

1,97,35,769.55

86

25-01-2022

IW CHQ : 404450 REJ Txn No.S64576849

Payment

236.00

1,97,36,005.55

87

25-01-2022

JAI SHRI KRISHANHANDLOO Txn No.S64604285 Cheque No. 404458

Payment

15000.00

1,97,51,005.55

88

25-01-2022

NEFT_OUT:PUNBH22025654362/MK Fab and FurnP/CNRB0000422/0422201002863 Txn No.S71145240

Payment

8463.00

1,97,59,468.55

89

25-01-2022

NEFT_OUT:PUNBH22025664529/Sumit Kumar/HDFC0000076/50100453150353 Txn No.S72269403

Payment

25000.00

1,97,84,468.55

90

25-01-2022

NEFT_OUT:PUNBH22025664748/Guru Kirpa Texco/HDFC0001419/50200015879735 Txn No.S72305292

Payment

16349.00

1,98,00,817.55

91

25-01-2022

NEFT_OUT:PUNBH22025665078/Dhruv Industries/HDFC0002707/50200008690824 Txn No.S72354267

Payment

22600.00

1,98,23,417.55

92

25-01-2022

To:0040000400022033:THIRDPARTY Txn No.S73539862

Payment

50000.00

1,98,73,417.55

93

25-01-2022

To:0335002100027863:MAYUR TEX Txn No.S73759969

Payment

50000.00

1,99,23,417.55

94

25-01-2022

NEFT_OUT:PUNBH22025679255/Reshma Firdose/IOBA0002838/283801000002794 Txn No.S75096820

Payment

3000.00

1,99,26,417.55

95

25-01-2022

NEFT_OUT:PUNBH22025679273/P Rosy/UBIN0902802/520101073176733 Txn No.S75096946

Payment

6638.00

1,99,33,055.55

96

25-01-2022

NEFT_OUT:PUNBH22025679297/Haji Hamza Cloth/BARB0PILAKH/37090200000553 Txn No.S75097098

Payment

10000.00

1,99,43,055.55

97

25-01-2022

NEFT_OUT:PUNBH22025679303/Das furnishing/FDRL0001546/15465500000540 Txn No.S75097199

Payment

15000.00

1,99,58,055.55

98

25-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S75105293

Receipt

15000.00

1,99,43,055.55

99

26-01-2022

NEFT:PUNBH22026679338/5074103288/Vrindavan Creatio Txn No.S75105463

Payment

11145.00

1,99,54,200.55

100

26-01-2022

To:0040000400017800:THIRDPARTY Txn No.S75105575

Payment

4000.00

1,99,58,200.55

101

26-01-2022

To:0040000400022033:THIRDPARTY Txn No.S75113303

Payment

5000.00

1,99,63,200.55

102

26-01-2022

From:0040000400022033:RAJ KUMAR Txn No.S83672401

Receipt

15000.00

1,99,48,200.55

103

26-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S83734339

Receipt

5000.00

1,99,43,200.55

104

26-01-2022

NEFT_OUT:PUNBH22026706732/K B TEXFAB PVTLT/UBIN0539287/392801010050845 Txn No.S83749201

Payment

20000.00

1,99,63,200.55

105

27-01-2022

DHC LOGISTICS PVT LTD Txn No.S92312801 Cheque No. 297836

Payment

4229.00

1,99,67,429.55

106

27-01-2022

NEFT_IN:000467863512/0028/ DEVI FURNISHING Txn No.S95873762

Receipt

3498.00

1,99,63,931.55

107

27-01-2022

DEEPIKLA TEXTILES-097710 Txn No.M698300 Branch Name - AMRITSAR-RANJITAVENUE Cheque No. 115915

Payment

23940.00

1,99,87,871.55

108

28-01-2022

DHC LOGISTICS PVT LTD Txn No.S8610328 Cheque No. 297841

Payment

2920.00

1,99,90,791.55

109

28-01-2022

IW CHQ : 115925 REJ Txn No.S9240214

Payment

236.00

1,99,91,027.55

110

28-01-2022

BY INST 700 : CTO386-1DAY LAT Txn No.S11116242

Receipt

7945.00

1,99,83,082.55

111

28-01-2022

BY INST 9 : CTO386-1 DAYLAT Txn No.S11116242

Receipt

41363.00

1,99,41,719.55

112

28-01-2022

NEFT_IN:CMS2366034725/0028/ NEFT SAIPRIYADARSHINI PNB Txn No.S12552876

Receipt

10079.00

1,99,31,640.55

113

28-01-2022

NEFT_OUT:PUNBH22028406780/VRL LogisticsLtd/SVCB0000151/115104180000724 Txn No.S17099015

Payment

890.00

1,99,32,530.55

114

28-01-2022

NEFT_OUT:PUNBH22028407183/Balwant Sharma/CSBK0000009/000901014064190001 Txn No.S17122507

Payment

5000.00

1,99,37,530.55

115

28-01-2022

IMPS-IN/202820202905/9008333000/HIT KARA Txn No.S19855554

Receipt

30000.00

1,99,07,530.55

116

28-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S20048431

Receipt

14000.00

1,98,93,530.55

117

28-01-2022

To:0040000400022033:THIRDPARTY Txn No.S20078016

Payment

65000.00

1,99,58,530.55

118

28-01-2022

IMPS-IN/202823229653/9008333000/HIT KARA Txn No.S21646457

Receipt

50000.00

1,99,08,530.55

119

28-01-2022

NEFT_OUT:PUNBH22028444122/Ambujammal GadiC/UBIN0533114/004600101009347 Txn No.S21649604

Payment

25000.00

1,99,33,530.55

120

28-01-2022

To:0335002100027863:MAYUR TEX Txn No.S21652886

Payment

25000.00

1,99,58,530.55

121

29-01-2022

NEFT_IN:0129i27266126981/0020/ HARYANAHANDLOOM CENTRE Txn No.S24127932

Receipt

14157.00

1,99,44,373.55

122

29-01-2022

SAAKAN Txn No.S25594848 Cheque No. 404451

Payment

35805.00

1,99,80,178.55

123

29-01-2022

NRTGS/SIBLR52022012900217708/RONAK KOTHARI Txn No.S27350940

Receipt

1000000.00

1,89,80,178.55

124

29-01-2022

To:0040005500052250:SAIPRIYADARSHINI Txn No.S28330610

Payment

160000.00

1,91,40,178.55

125

29-01-2022

NEFT_IN:CMS2368136936/0028/ NEFT SAIPRIYADARSHINI PNB Txn No.S28662620

Receipt

11226.00

1,91,28,952.55

126

29-01-2022

NEFT_OUT:PUNBH22029571773/SnehaEnterprises/KARB0000715/7157000600342001 Txn No.S29261280

Payment

20000.00

1,91,48,952.55

127

29-01-2022

NEFT_OUT:PUNBH22029669930/Bajrang Textile/SBIN0016970/36189935432 Txn No.S31710586

Payment

27270.00

1,91,76,222.55

128

29-01-2022

NEFT_OUT:PUNBH22029724591/Deutsche Bank/DEUT0784PBC/350032923110028 Txn No.S35546415

Payment

53000.00

1,92,29,222.55

129

29-01-2022

To:3616002100032433:SHRIRAVI TEXTILES Txn No.S36568018

Payment

20000.00

1,92,49,222.55

130

30-01-2022

NEFT_OUT:PUNBH22030738112/Ronak Kothari/SIBL0000008/0008053000019810 Txn No.S38918271

Payment

99000.00

1,93,48,222.55

131

30-01-2022

NEFT_OUT:PUNBH22030738146/ShyamInnovations/KKBK0001435/0413090674 Txn No.S38927953

Payment

23137.00

1,93,71,359.55

132

30-01-2022

NEFT_OUT:PUNBH22030738195/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S38943508

Payment

37128.00

1,94,08,487.55

133

30-01-2022

NEFT_OUT:PUNBH22030738240/Das Furnishing/FDRL0001546/15460200009412 Txn No.S38957395

Payment

41234.00

1,94,49,721.55

134

30-01-2022

NEFT_OUT:PUNBH22030738265/DR DRAPES/KKBK0000961/8011205001 Txn No.S38966951

Payment

34791.00

1,94,84,512.55

135

30-01-2022

NEFT_OUT:PUNBH22030738290/K B TEXFAB PVTLT/UBIN0539287/392801010050845 Txn No.S38978925

Payment

47204.00

1,95,31,716.55

136

30-01-2022

NEFT_OUT:PUNBH22030738330/Dhruv Industries/HDFC0002707/50200008690824 Txn No.S39001769

Payment

47595.00

1,95,79,311.55

137

30-01-2022

To:1456002100112933:TANISHQ FAB Txn No.S39064636

Payment

10790.00

1,95,90,101.55

138

30-01-2022

NEFT_OUT:PUNBH22030738693/SM EXPRESSLOGIST/HDFC0001471/14712560000622 Txn No.S39147626

Payment

17904.00

1,96,08,005.55

139

30-01-2022

IMPS-IN/203016277937/9743495430/NAGARAJA Txn No.S45137001

Receipt

2727.00

1,96,05,278.55

140

30-01-2022

NEFT_OUT:PUNBH22030759225/Rubmini Fabrics/UBIN0904597/560101000102178 Txn No.S48299812

Payment

25000.00

1,96,30,278.55

141

30-01-2022

To:4365002100104033:S BENTERPRISES Txn No.S48332081

Payment

18000.00

1,96,48,278.55

142

30-01-2022

NEFT_OUT:PUNBH22030759359/Jai BharatPrints/HDFC0000926/50200017200515 Txn No.S48373944

Payment

50000.00

1,96,98,278.55

143

30-01-2022

NEFT_OUT:PUNBH22030759414/Anjali Textiles/DCBL0000191/19122900003933 Txn No.S48390952

Payment

25000.00

1,97,23,278.55

144

30-01-2022

NEFT_OUT:PUNBH22030759834/SG Textiles/UBIN0540242/402405010000124 Txn No.S48639497

Payment

20000.00

1,97,43,278.55

145

30-01-2022

NEFT_OUT:PUNBH22030759904/Kapish Fabrics/BARB0PANCHK/03340500014101 Txn No.S48681378

Payment

20000.00

1,97,63,278.55

146

30-01-2022

NEFT_OUT:PUNBH22030759942/Surya Prints/HDFC0000926/09262320000091 Txn No.S48704812

Payment

26240.00

1,97,89,518.55

147

30-01-2022

NEFT_OUT:PUNBH22030759984/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S48727027

Payment

2600.00

1,97,92,118.55

148

30-01-2022

NEFT_OUT:PUNBH22030760012/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S48741255

Payment

2890.00

1,97,95,008.55

149

30-01-2022

To:0040002100096132:SAIPRIYADARSHINI Txn No.S49135071

Payment

15000.00

1,98,10,008.55

150

30-01-2022

NEFT_OUT:PUNBH22030760788/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S49192306

Payment

1400.00

1,98,11,408.55

151

30-01-2022

NEFT_OUT:PUNBH22030760842/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S49210304

Payment

1850.00

1,98,13,258.55

152

30-01-2022

NEFT_OUT:PUNBH22030760882/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S49228683

Payment

1290.00

1,98,14,548.55

153

30-01-2022

To:0040000400022033:THIRDPARTY Txn No.S49606885

Payment

15000.00

1,98,29,548.55

154

30-01-2022

To:0040000400017794:THIRDPARTY Txn No.S49617550

Payment

2500.00

1,98,32,048.55

155

31-01-2022

NEFT_OUT:PUNBH22031769695/Shivanaa Homes/ICIC0000052/005205009149 Txn No.S52731762

Payment

35000.00

1,98,67,048.55

156

31-01-2022

NEFT_OUT:PUNBH22031769728/Sumit Kumar/KKBK0000631/CSG152957522 Txn No.S52731878

Payment

75056.00

1,99,42,104.55

157

31-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S52731921

Receipt

100000.00

1,98,42,104.55

158

31-01-2022

NEFT_OUT:PUNBH22031769901/Indusind BankLtd/INDB0000007/00073564614017 Txn No.S52749740

Payment

25993.00

1,98,68,097.55

159

31-01-2022

NEFT_OUT:PUNBH22031770116/Deutsche Bank/DEUT0784PBC/350032923110028 Txn No.S52791765

Payment

45000.00

1,99,13,097.55

160

31-01-2022

To:0040000400022033:THIRDPARTY Txn No.S52803831

Payment

6000.00

1,99,19,097.55

161

31-01-2022

To:0573008700001276:MAGMA FINCORP LIMITED Txn No.S52811577

Payment

10755.00

1,99,29,852.55

162

31-01-2022

To:0335002100027863:MAYUR TEX Txn No.S52818356

Payment

30000.00

1,99,59,852.55

163

31-01-2022

IMPS-IN/203108352889/9008333000/SUMIT KU Txn No.S52914499

Receipt

100000.00

1,98,59,852.55

164

31-01-2022

NEFT_OUT:PUNBH22031771000/JagdambaFurnishi/HDFC0001416/50200011850889 Txn No.S52922892

Payment

46820.00

1,99,06,672.55

165

31-01-2022

NEFT_OUT:PUNBH22031771036/VrindavanCreatio/HDFC0000076/50200062038863 Txn No.S52931538

Payment

25000.00

1,99,31,672.55

166

31-01-2022

NEFT_OUT:PUNBH22031771064/AEBC376932184342/SCBL0036020/52205899087 Txn No.S52937494

Payment

20000.00

1,99,51,672.55

167

31-01-2022

To:0040000400037211:ABALU Txn No.S52946961

Payment

12000.00

1,99,63,672.55

168

31-01-2022

SRD LOGISTICS PRIVATELIM Txn No.S55190966 Cheque No. 297840

Payment

3063.00

1,99,66,735.55

169

31-01-2022

N PRAKASH Txn No.S55291435 Cheque No. 115927

Payment

25800.00

1,99,92,535.55

170

31-01-2022

0040008700003314:Int.Coll:01-01-2022 to 31-01-202 Txn No.S69538859

Payment

202268.00

2,01,94,803.55

171

JohnnyL

Results from CC Jan 2022_Fixed.csv file.

RAJESH1960 · Feb 22, 2022

Rich (BB code):

Sheets("Sheet2").Range("A1").Resize(UBound(Partitioned_CSV_FileArray, 1), UBound(Partitioned_CSV_FileArray, 2)) = Partitioned_CSV_FileArray

run time error.

RAJESH1960 · Feb 22, 2022

JohnnyL. FYI,
Your last code is working perfectly. Everything is good.
Only 2 things left to complete this with your yesterday's last code . One is the Line number column which you can insert at the end of the code before column A, then sort the data by line number largest to smallest and then finally delete the line number column. I want the dates from descending to ascending order but in the same order as in the csv sheet but reversed.
Please note that the line number should insert numbers only till the last row with date in that particular conversion. The range of last row will be different in each conversion.
Don’t tamper the balance column with Dr. & Cr. If possible, with the help of a code or formula in cell H2 remove the Dr. & Cr. And get the amount in number format with 2 decimal places and resize column H with G.
That way the sheet will have
Secondly, each time I press the convert button, I have to rename the sheet, insert a new sheet and rename it to JohnnyL. So, I a code to be added at the end of your code.
This will complete the project.
Please continue with this code.....

Rich (BB code):

Sub LoadCSV_FileTest4()
'solved by JohnnyL except for sort data by date
    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                                                              ' If user cancelled then ...
        MsgBox "No file selected - exiting"                                                     '   Display message box to user indicating the cancellation
        Exit Sub                                                                                '   Exit the sub
    End If
'
    startTime = Timer                                                                           ' Start the stopwatch
'
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    Dim CSV_ColumnMinus1                As Long, CSV_FileArrayColumn        As Long, CSV_FileArrayRow   As Long, CSV_FileRow    As Long
    Dim jLeft                           As Long, jCntMid                    As Long, jRight             As Long
    Dim DatesArrayRow                   As Long
    Dim FreeFileNumber                  As Long
    Dim OutputArrayColumn               As Long, OutputArrayRow             As Long
    Dim RowNumber                       As Long
    Dim BranchName                      As String, ChequeNumber             As String
    Dim All_CSV_RowsFromCSV_FileArray   As Variant, CSV_FileRowColumnsArray As Variant, Partitioned_CSV_FileArray               As Variant
    Dim DatesArray                      As Variant, HeaderArray             As Variant, OutputArray()                           As Variant
    Dim destSht                         As Worksheet
'
    Set destSht = ThisWorkbook.Worksheets("JohnnyL")                                            ' <--- Set this to the desired sheet name
'
    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 csv file to All_CSV_RowsFromCSV_FileArray
    Close #FreeFileNumber                                                                       ' Close the csv file
'
    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 csv columns
                Partitioned_CSV_FileArray(RowNumber, CSV_ColumnMinus1 + 1) = _
                        CSV_FileRowColumnsArray(CSV_ColumnMinus1)                                       '           Save values to Partitioned_CSV_FileArray
            Next                                                                                        '       Loop back
        End If
    Next                                                                                                ' Loop back
'
'   All values from the CSV File are now loaded into Partitioned_CSV_FileArray
'
'-----------------------------------------------------------------------------------------------------
'
'   Intercept CSV file dates before excel gets it's hands on them
'
    ReDim DatesArray(1 To UBound(Partitioned_CSV_FileArray, 1))                                 ' Set # of rows for DatesArray
'
    DatesArrayRow = 0                                                                           ' Initialize DatesArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through Partitioned_CSV_FileArray rows
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            DatesArrayRow = DatesArrayRow + 1                                                   '       Increment DatesArrayRow
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through csv file columns starting from 2nd column
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    DatesArray(DatesArrayRow) = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '            Save the date into DatesArray
                    Exit For                                                                                        '            Exit loop
                End If
            Next                                                                                                    '       Loop back
        End If
    Next                                                                                                            ' Loop back
'
'   Dates needed are now safely stored into DatesArray
'
'-------------------------------------------------------------------------------------------------
'
    Workbooks.Open Filename:=CSV_FileToOpen                                                     ' Open the CSV file into Excel
'
    Partitioned_CSV_FileArray = ActiveWorkbook.ActiveSheet.UsedRange.Value                      ' Save the values from CSV file into Partitioned_CSV_FileArray
'
    ReDim OutputArray(1 To UBound(Partitioned_CSV_FileArray, 1), 1 To 5)                        ' Set rows/columns for OutputArray
'
    OutputArrayRow = 1                                                                          ' Initialize OutputArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through rows of Partitioned_CSV_FileArray
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            OutputArrayColumn = 0                                                               '       Initialize OutputArrayColumn
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through columns of Partitioned_CSV_FileArray
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If value found then ...
                    OutputArrayColumn = OutputArrayColumn + 1                                   '               Increment OutputArrayColumn
'
                    If OutputArrayColumn = 2 Then                                               '               If 'Description' is found then ...
                        OutputArray(OutputArrayRow, OutputArrayColumn - 1) = _
                                Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '                   Save it into OutputArray Column 1
                    End If
'
                    If OutputArrayColumn = 3 Then                                               '               If 4th value in row found then ...
                        If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "-" Then '                       If value found <> '-' then ...
                            BranchName = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)   '                       save it into BranchName
                        End If
'
                        jLeft = CSV_FileArrayColumn + 1                                         '                   Set col # to next ie. col 29->30
                        Exit For                                                                '                   Exit column loop
                    End If
                End If
            Next                                                                                '       Loop back
         
            For CSV_FileArrayColumn = UBound(Partitioned_CSV_FileArray, 2) To jLeft Step -1     '       Loop backwards from last column of csv to 4th value found column + 1
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    OutputArray(OutputArrayRow, 5) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '               Save the value to Column 5 of OutputArray
                    OutputArray(OutputArrayRow, 4) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 1)    '           Save Cr amount to column 4 of OutputArray
                    OutputArray(OutputArrayRow, 3) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 3)    '           Save Dr amount to column 3 of OutputArray
'
                    If OutputArray(OutputArrayRow, 3) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Payment"                          ' If OutputArray column 3 is not blank then set "Payment"
                    If OutputArray(OutputArrayRow, 4) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Receipt"                          ' If OutputArray column 4 is not blank then set "Receipt"
'
                    jRight = CSV_FileArrayColumn - 3 - 1
                    Exit For                                                                    ' Exit loop
                End If
            Next
        
            For jCntMid = jLeft To jRight                                                       ' Loop through columns for a cheque # value between the
'                                                                                               '       Branch Name column and the DR/CR amount column
                 If Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid) <> "" Then             '   If cheque # found then ...
                    ChequeNumber = Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid)         '       Save the cheque # into ChequeNumber
                    Exit For                                                                    '       Exit loop
                 End If
            Next
'
            OutputArray(OutputArrayRow, 1) = OutputArray(OutputArrayRow, 1) & _
                    " Txn No." & Partitioned_CSV_FileArray(CSV_FileArrayRow, 1)                 ' Append Transaction # to column 1 of OutputArray
'
            If BranchName <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Branch Name - " & BranchName             ' If a BranchName was found then Append BranchName to column 1 of OutputArray
'
            If ChequeNumber <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Cheque No. " & ChequeNumber              ' If a ChequeNumber was found then Append ChequeNumber to column 1 of OutputArray
'
            OutputArray(OutputArrayRow, 1) = Replace(OutputArray(OutputArrayRow, 1), Chr(10), "")   ' delete line feeds in description
'
            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
            BranchName = ""                                                                     ' Clear BranchName
            ChequeNumber = ""                                                                   ' Clear ChequeNumber
        End If
    Next
 
    HeaderArray = Array("Txn Date", "Description", "Voucher Type", "Dr Amount", "Cr Amount", "Balance") ' Establish HeaderArray

    With destSht
        .UsedRange.Clear                                                                        ' Clear the destination sheet
        .Columns("A:A").NumberFormat = "@"                                                      ' Set column A to text so Excel don't mess up the dates
        .Range("A1").Resize(, UBound(HeaderArray) + 1) = HeaderArray                            ' Write the Header columns to destination sheet
        .Range("A1").Resize(, UBound(HeaderArray) + 1).Font.Bold = True                         ' Set the Header to Bold type font
'
        .Range("A2").Resize(UBound(DatesArray)).Value = Application.Transpose(DatesArray)       ' Display the dates to destination sheet Column A
'
        .Range("B2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)).Value = OutputArray ' Display other results to destination sheet column B
'
''        .Range("F:H").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .UsedRange.Columns.AutoFit                                                              ' Resize the columns to fit the data
    End With
'
    
'
    Application.ScreenUpdating = True                                                           ' Turn ScreenUpdating back on
'
    Debug.Print "Time to complete = " & Timer - startTime & " seconds."                         ' about .4 seconds
End Sub

johnnyL · Feb 22, 2022

RAJESH1960 said:
Rich (BB code):

Sheets("Sheet2").Range("A1").Resize(UBound(Partitioned_CSV_FileArray, 1), UBound(Partitioned_CSV_FileArray, 2)) = Partitioned_CSV_FileArray

run time error.

Delete that line

RAJESH1960 · Feb 22, 2022

johnnyL said:
Delete that line

Yes I did that. Working now. Let me check a few more csv files.

RAJESH1960 · Feb 22, 2022

This is not the only sheet I have to convert. There will be 25 -30 sheets each time to convert. So, I was thinking if the JohnnyL sheet is copied and pasted to a new sheet which will be named depending on the first date and the last date, it would be great. Example sheet name = 01-01-2021 to 31-12-2021

RAJESH1960 · Feb 22, 2022

RAJESH1960 said:
Secondly, each time I press the convert button, I have to rename the sheet, insert a new sheet and rename it to JohnnyL. So, I a code to be added at the end of your code.

This is why I need by the new sheet name.

johnnyL · Feb 22, 2022

RAJESH1960 said:
This is not the only sheet I have to convert. There will be 25 -30 sheets each time to convert. So, I was thinking if the JohnnyL sheet is copied and pasted to a new sheet which will be named depending on the first date and the last date, it would be great. Example sheet name = 01-01-2021 to 31-12-2021

How about:

VBA Code:

Sub LoadCSV_FileTest4()
'
    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                                                              ' If user cancelled then ...
        MsgBox "No file selected - exiting"                                                     '   Display message box to user indicating the cancellation
        Exit Sub                                                                                '   Exit the sub
    End If
'
    startTime = Timer                                                                           ' Start the stopwatch
'
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    Dim ArrayRow                        As Long
    Dim CSV_ColumnMinus1                As Long, CSV_FileArrayColumn        As Long, CSV_FileArrayRow   As Long, CSV_FileRow    As Long
    Dim jLeft                           As Long, jCntMid                    As Long, jRight             As Long
    Dim DatesArrayRow                   As Long
    Dim FreeFileNumber                  As Long
    Dim OutputArrayColumn               As Long, OutputArrayRow             As Long
    Dim RowNumber                       As Long
    Dim BranchName                      As String, ChequeNumber             As String
    Dim All_CSV_RowsFromCSV_FileArray   As Variant, CSV_FileRowColumnsArray As Variant, Partitioned_CSV_FileArray               As Variant
    Dim CombinedAndReversedArray        As Variant
    Dim DatesArray                      As Variant, HeaderArray             As Variant, OutputArray()                           As Variant
    Dim destSht                         As Worksheet
'
    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 csv file to All_CSV_RowsFromCSV_FileArray
    Close #FreeFileNumber                                                                       ' Close the csv file
'
    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 csv columns
                Partitioned_CSV_FileArray(RowNumber, CSV_ColumnMinus1 + 1) = _
                        CSV_FileRowColumnsArray(CSV_ColumnMinus1)                                       '           Save 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
'
'   All values from the CSV File are now loaded into Partitioned_CSV_FileArray
'
'-----------------------------------------------------------------------------------------------------
'
'   Intercept CSV file dates before excel gets it's hands on them
'
    ReDim DatesArray(1 To UBound(Partitioned_CSV_FileArray, 1))                                 ' Set # of rows for DatesArray
'
    DatesArrayRow = 0                                                                           ' Initialize DatesArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through Partitioned_CSV_FileArray rows
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            DatesArrayRow = DatesArrayRow + 1                                                   '       Increment DatesArrayRow
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through csv file columns starting from 2nd column
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    DatesArray(DatesArrayRow) = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '            Save the date into DatesArray
                    Exit For                                                                                        '            Exit loop
                End If
            Next                                                                                                    '       Loop back
        End If
    Next                                                                                                            ' Loop back
'
'   Dates needed are now safely stored into DatesArray
'
'-------------------------------------------------------------------------------------------------
'
    Workbooks.Open Filename:=CSV_FileToOpen                                                     ' Open the CSV file into Excel
'
    Partitioned_CSV_FileArray = ActiveWorkbook.ActiveSheet.UsedRange.Value                      ' Save the values from CSV file into Partitioned_CSV_FileArray
'
    ReDim OutputArray(1 To UBound(Partitioned_CSV_FileArray, 1), 1 To 5)                        ' Set rows/columns for OutputArray
'
    OutputArrayRow = 1                                                                          ' Initialize OutputArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through rows of Partitioned_CSV_FileArray
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            OutputArrayColumn = 0                                                               '       Initialize OutputArrayColumn
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through columns of Partitioned_CSV_FileArray
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If value found then ...
                    OutputArrayColumn = OutputArrayColumn + 1                                   '               Increment OutputArrayColumn
'
                    If OutputArrayColumn = 2 Then                                               '               If 'Description' is found then ...
                        OutputArray(OutputArrayRow, OutputArrayColumn - 1) = _
                                Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '                   Save it into OutputArray Column 1
                    End If
'
                    If OutputArrayColumn = 3 Then                                               '               If 4th value in row found then ...
                        If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "-" Then '                       If value found <> '-' then ...
                            BranchName = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)   '                       save it into BranchName
                        End If
'
                        jLeft = CSV_FileArrayColumn + 1                                         '                   Set col # to next ie. col 29->30
                        Exit For                                                                '                   Exit column loop
                    End If
                End If
            Next                                                                                '       Loop back
             
            For CSV_FileArrayColumn = UBound(Partitioned_CSV_FileArray, 2) To jLeft Step -1     '       Loop backwards from last column of csv to 4th value found column + 1
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    OutputArray(OutputArrayRow, 5) = _
                            Left(Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn), _
                            Len(Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)) - 4)  '               Save the value to Column 5 of OutputArray
                    OutputArray(OutputArrayRow, 4) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 1)    '           Save Cr amount to column 4 of OutputArray
                    OutputArray(OutputArrayRow, 3) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 3)    '           Save Dr amount to column 3 of OutputArray
'
                    If OutputArray(OutputArrayRow, 3) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Payment"                          ' If OutputArray column 3 is not blank then set "Payment"
                    If OutputArray(OutputArrayRow, 4) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Receipt"                          ' If OutputArray column 4 is not blank then set "Receipt"
'
                    jRight = CSV_FileArrayColumn - 3 - 1
                    Exit For                                                                    ' Exit loop
                End If
            Next
            
            For jCntMid = jLeft To jRight                                                       ' Loop through columns for a cheque # value between the
'                                                                                               '       Branch Name column and the DR/CR amount column
                 If Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid) <> "" Then             '   If cheque # found then ...
                    ChequeNumber = Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid)         '       Save the cheque # into ChequeNumber
                    Exit For                                                                    '       Exit loop
                 End If
            Next
'
            OutputArray(OutputArrayRow, 1) = OutputArray(OutputArrayRow, 1) & _
                    " Txn No." & Partitioned_CSV_FileArray(CSV_FileArrayRow, 1)                 ' Append Transaction # to column 1 of OutputArray
'
            If BranchName <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Branch Name - " & BranchName             ' If a BranchName was found then Append BranchName to column 1 of OutputArray
'
            If ChequeNumber <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Cheque No. " & ChequeNumber              ' If a ChequeNumber was found then Append ChequeNumber to column 1 of OutputArray
'
            OutputArray(OutputArrayRow, 1) = Replace(OutputArray(OutputArrayRow, 1), Chr(10), "")   ' delete line feeds in description
'
            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
            BranchName = ""                                                                     ' Clear BranchName
            ChequeNumber = ""                                                                   ' Clear ChequeNumber
        End If
    Next
'
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=False                                                     ' Close the csv file that was opened earlier
    Application.DisplayAlerts = True
'
'-------------------------------------------------------------------------------------------------
'
'   Combine and Reverse order of 2 arrays ... DatesArray & OutputArray into CombinedAndReversedArray
'
    ReDim CombinedAndReversedArray(1 To UBound(DatesArray), 1 To 6)
'
    RowNumber = 0
'
    For ArrayRow = UBound(DatesArray) To 1 Step -1
        If DatesArray(ArrayRow) <> vbNullString Then
            RowNumber = RowNumber + 1
'
            CombinedAndReversedArray(RowNumber, 1) = DatesArray(ArrayRow)
            CombinedAndReversedArray(RowNumber, 2) = OutputArray(ArrayRow, 1)
            CombinedAndReversedArray(RowNumber, 3) = OutputArray(ArrayRow, 2)
            CombinedAndReversedArray(RowNumber, 4) = OutputArray(ArrayRow, 3)
            CombinedAndReversedArray(RowNumber, 5) = OutputArray(ArrayRow, 4)
            CombinedAndReversedArray(RowNumber, 6) = OutputArray(ArrayRow, 5)
        End If
    Next
'
'-------------------------------------------------------------------------------------------------
'
    Sheets.Add(After:=ActiveSheet).Name = CombinedAndReversedArray(1, 1) & " to " & CombinedAndReversedArray(RowNumber, 1)
'
    Set destSht = ActiveSheet                                                                   ' Set the sheet name to display results into
'
    HeaderArray = Array("Txn Date", "Description", "Voucher Type", "Dr Amount", "Cr Amount", "Balance") ' Establish HeaderArray
'
    With destSht
        .UsedRange.Clear                                                                        ' Clear the destination sheet
        .Columns("A:A").NumberFormat = "@"                                                      ' Set column A to text so Excel don't mess up the dates
        .Range("A1").Resize(, UBound(HeaderArray) + 1) = HeaderArray                            ' Write the Header columns to destination sheet
        .Range("A1").Resize(, UBound(HeaderArray) + 1).Font.Bold = True                         ' Set the Header to Bold type font
'
        .Range("A2").Resize(UBound(CombinedAndReversedArray, 1), _
                UBound(CombinedAndReversedArray, 2)).Value = CombinedAndReversedArray           ' Display results to destination sheet
'
''        .Range("F:H").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .Columns("D:F").NumberFormat = "0.00"                                                          ' Format Columns D:E to two decimal places
        .UsedRange.Columns.AutoFit                                                              ' Resize the columns to fit the data
    End With
'
    Application.ScreenUpdating = True                                                           ' Turn ScreenUpdating back on
'
    Debug.Print "Time to complete = " & Timer - startTime & " seconds."                         ' about .5 seconds
End Sub

You will get an error if it works out that more than one sheet is trying to be named the same as a previously named sheet.

RAJESH1960 · Feb 22, 2022

I

johnnyL said:

How about:

VBA Code:

Sub LoadCSV_FileTest4()
'
    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                                                              ' If user cancelled then ...
        MsgBox "No file selected - exiting"                                                     '   Display message box to user indicating the cancellation
        Exit Sub                                                                                '   Exit the sub
    End If
'
    startTime = Timer                                                                           ' Start the stopwatch
'
    Application.ScreenUpdating = False                                                          ' Turn ScreenUpdating off
'
    Dim ArrayRow                        As Long
    Dim CSV_ColumnMinus1                As Long, CSV_FileArrayColumn        As Long, CSV_FileArrayRow   As Long, CSV_FileRow    As Long
    Dim jLeft                           As Long, jCntMid                    As Long, jRight             As Long
    Dim DatesArrayRow                   As Long
    Dim FreeFileNumber                  As Long
    Dim OutputArrayColumn               As Long, OutputArrayRow             As Long
    Dim RowNumber                       As Long
    Dim BranchName                      As String, ChequeNumber             As String
    Dim All_CSV_RowsFromCSV_FileArray   As Variant, CSV_FileRowColumnsArray As Variant, Partitioned_CSV_FileArray               As Variant
    Dim CombinedAndReversedArray        As Variant
    Dim DatesArray                      As Variant, HeaderArray             As Variant, OutputArray()                           As Variant
    Dim destSht                         As Worksheet
'
    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 csv file to All_CSV_RowsFromCSV_FileArray
    Close #FreeFileNumber                                                                       ' Close the csv file
'
    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 csv columns
                Partitioned_CSV_FileArray(RowNumber, CSV_ColumnMinus1 + 1) = _
                        CSV_FileRowColumnsArray(CSV_ColumnMinus1)                                       '           Save 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
'
'   All values from the CSV File are now loaded into Partitioned_CSV_FileArray
'
'-----------------------------------------------------------------------------------------------------
'
'   Intercept CSV file dates before excel gets it's hands on them
'
    ReDim DatesArray(1 To UBound(Partitioned_CSV_FileArray, 1))                                 ' Set # of rows for DatesArray
'
    DatesArrayRow = 0                                                                           ' Initialize DatesArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through Partitioned_CSV_FileArray rows
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            DatesArrayRow = DatesArrayRow + 1                                                   '       Increment DatesArrayRow
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through csv file columns starting from 2nd column
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    DatesArray(DatesArrayRow) = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '            Save the date into DatesArray
                    Exit For                                                                                        '            Exit loop
                End If
            Next                                                                                                    '       Loop back
        End If
    Next                                                                                                            ' Loop back
'
'   Dates needed are now safely stored into DatesArray
'
'-------------------------------------------------------------------------------------------------
'
    Workbooks.Open Filename:=CSV_FileToOpen                                                     ' Open the CSV file into Excel
'
    Partitioned_CSV_FileArray = ActiveWorkbook.ActiveSheet.UsedRange.Value                      ' Save the values from CSV file into Partitioned_CSV_FileArray
'
    ReDim OutputArray(1 To UBound(Partitioned_CSV_FileArray, 1), 1 To 5)                        ' Set rows/columns for OutputArray
'
    OutputArrayRow = 1                                                                          ' Initialize OutputArrayRow
'
    For CSV_FileArrayRow = 1 To UBound(Partitioned_CSV_FileArray, 1)                            ' Loop through rows of Partitioned_CSV_FileArray
        If IsNumeric(Right(Partitioned_CSV_FileArray(CSV_FileArrayRow, 1), 6)) Then             '   If transaction # found then ...
            OutputArrayColumn = 0                                                               '       Initialize OutputArrayColumn
'
            For CSV_FileArrayColumn = 2 To UBound(Partitioned_CSV_FileArray, 2)                 '       Loop through columns of Partitioned_CSV_FileArray
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If value found then ...
                    OutputArrayColumn = OutputArrayColumn + 1                                   '               Increment OutputArrayColumn
'
                    If OutputArrayColumn = 2 Then                                               '               If 'Description' is found then ...
                        OutputArray(OutputArrayRow, OutputArrayColumn - 1) = _
                                Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)    '                   Save it into OutputArray Column 1
                    End If
'
                    If OutputArrayColumn = 3 Then                                               '               If 4th value in row found then ...
                        If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "-" Then '                       If value found <> '-' then ...
                            BranchName = Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)   '                       save it into BranchName
                        End If
'
                        jLeft = CSV_FileArrayColumn + 1                                         '                   Set col # to next ie. col 29->30
                        Exit For                                                                '                   Exit column loop
                    End If
                End If
            Next                                                                                '       Loop back
            
            For CSV_FileArrayColumn = UBound(Partitioned_CSV_FileArray, 2) To jLeft Step -1     '       Loop backwards from last column of csv to 4th value found column + 1
                If Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn) <> "" Then  '           If a value is found then ...
                    OutputArray(OutputArrayRow, 5) = _
                            Left(Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn), _
                            Len(Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn)) - 4)  '               Save the value to Column 5 of OutputArray
                    OutputArray(OutputArrayRow, 4) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 1)    '           Save Cr amount to column 4 of OutputArray
                    OutputArray(OutputArrayRow, 3) = _
                            Partitioned_CSV_FileArray(CSV_FileArrayRow, CSV_FileArrayColumn - 3)    '           Save Dr amount to column 3 of OutputArray
'
                    If OutputArray(OutputArrayRow, 3) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Payment"                          ' If OutputArray column 3 is not blank then set "Payment"
                    If OutputArray(OutputArrayRow, 4) <> vbNullString Then _
                            OutputArray(OutputArrayRow, 2) = "Receipt"                          ' If OutputArray column 4 is not blank then set "Receipt"
'
                    jRight = CSV_FileArrayColumn - 3 - 1
                    Exit For                                                                    ' Exit loop
                End If
            Next
           
            For jCntMid = jLeft To jRight                                                       ' Loop through columns for a cheque # value between the
'                                                                                               '       Branch Name column and the DR/CR amount column
                 If Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid) <> "" Then             '   If cheque # found then ...
                    ChequeNumber = Partitioned_CSV_FileArray(CSV_FileArrayRow, jCntMid)         '       Save the cheque # into ChequeNumber
                    Exit For                                                                    '       Exit loop
                 End If
            Next
'
            OutputArray(OutputArrayRow, 1) = OutputArray(OutputArrayRow, 1) & _
                    " Txn No." & Partitioned_CSV_FileArray(CSV_FileArrayRow, 1)                 ' Append Transaction # to column 1 of OutputArray
'
            If BranchName <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Branch Name - " & BranchName             ' If a BranchName was found then Append BranchName to column 1 of OutputArray
'
            If ChequeNumber <> "" Then OutputArray(OutputArrayRow, 1) = _
                    OutputArray(OutputArrayRow, 1) & " Cheque No. " & ChequeNumber              ' If a ChequeNumber was found then Append ChequeNumber to column 1 of OutputArray
'
            OutputArray(OutputArrayRow, 1) = Replace(OutputArray(OutputArrayRow, 1), Chr(10), "")   ' delete line feeds in description
'
            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
            BranchName = ""                                                                     ' Clear BranchName
            ChequeNumber = ""                                                                   ' Clear ChequeNumber
        End If
    Next
'
    Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges:=False                                                     ' Close the csv file that was opened earlier
    Application.DisplayAlerts = True
'
'-------------------------------------------------------------------------------------------------
'
'   Combine and Reverse order of 2 arrays ... DatesArray & OutputArray into CombinedAndReversedArray
'
    ReDim CombinedAndReversedArray(1 To UBound(DatesArray), 1 To 6)
'
    RowNumber = 0
'
    For ArrayRow = UBound(DatesArray) To 1 Step -1
        If DatesArray(ArrayRow) <> vbNullString Then
            RowNumber = RowNumber + 1
'
            CombinedAndReversedArray(RowNumber, 1) = DatesArray(ArrayRow)
            CombinedAndReversedArray(RowNumber, 2) = OutputArray(ArrayRow, 1)
            CombinedAndReversedArray(RowNumber, 3) = OutputArray(ArrayRow, 2)
            CombinedAndReversedArray(RowNumber, 4) = OutputArray(ArrayRow, 3)
            CombinedAndReversedArray(RowNumber, 5) = OutputArray(ArrayRow, 4)
            CombinedAndReversedArray(RowNumber, 6) = OutputArray(ArrayRow, 5)
        End If
    Next
'
'-------------------------------------------------------------------------------------------------
'
    Sheets.Add(After:=ActiveSheet).Name = CombinedAndReversedArray(1, 1) & " to " & CombinedAndReversedArray(RowNumber, 1)
'
    Set destSht = ActiveSheet                                                                   ' Set the sheet name to display results into
'
    HeaderArray = Array("Txn Date", "Description", "Voucher Type", "Dr Amount", "Cr Amount", "Balance") ' Establish HeaderArray
'
    With destSht
        .UsedRange.Clear                                                                        ' Clear the destination sheet
        .Columns("A:A").NumberFormat = "@"                                                      ' Set column A to text so Excel don't mess up the dates
        .Range("A1").Resize(, UBound(HeaderArray) + 1) = HeaderArray                            ' Write the Header columns to destination sheet
        .Range("A1").Resize(, UBound(HeaderArray) + 1).Font.Bold = True                         ' Set the Header to Bold type font
'
        .Range("A2").Resize(UBound(CombinedAndReversedArray, 1), _
                UBound(CombinedAndReversedArray, 2)).Value = CombinedAndReversedArray           ' Display results to destination sheet
'
''        .Range("F:H").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .Columns("D:F").NumberFormat = "0.00"                                                          ' Format Columns D:E to two decimal places
        .UsedRange.Columns.AutoFit                                                              ' Resize the columns to fit the data
    End With
'
    Application.ScreenUpdating = True                                                           ' Turn ScreenUpdating back on
'
    Debug.Print "Time to complete = " & Timer - startTime & " seconds."                         ' about .5 seconds
End Sub

You will get an error if it works out that more than one sheet is trying to be named the same as a previously named sheet.

JohnnyL. It is Perfect. Finally, an end of a wonderful era. Thanks a Million JohnnyL.

Convert csv to xlsx

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

Excel Facts

johnnyL

Well-known Member

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

johnnyL

Well-known Member

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

johnnyL

Well-known Member

RAJESH1960

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

Convert csv to xlsx

Banned for repeated rules violations

Banned for repeated rules violations

Excel Facts

Well-known Member

Banned for repeated rules violations

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Banned for repeated rules violations

Banned for repeated rules violations

Well-known Member

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock