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

Alex Blakenburg · Feb 21, 2022

What is your region date setting ? Is it dd/mm/yyyy ?

RAJESH1960 · Feb 21, 2022

Alex Blakenburg said:
What is your region date setting ? Is it dd/mm/yyyy ?

yes. It is dd-mm-yyyy. 50% of the dates are right dd-mm-yyyy in the code and rest are all mm-dd-yyyy.

RAJESH1960 · Feb 21, 2022

If it is December file the dates from 1 to 12th of the month the dates display mm-dd-yyyy and the rest are good dd-mm-yyyy. It depends on the month of the file. I hope you are understanding.

RAJESH1960 · Feb 21, 2022

Working csv to xlsx convertor 22.02.2022.xlsm

B

109

13-04-2021

110

13-04-2021

111

13-04-2021

112

13-04-2021

113

13-04-2021

114

13-04-2021

115

13-04-2021

116

13-04-2021

117

04-12-2021

118

04-12-2021

119

04-12-2021

120

04-12-2021

121

04-12-2021

122

04-12-2021

123

04-12-2021

124

04-12-2021

125

04-12-2021

126

04-12-2021

127

04-12-2021

128

04-11-2021

129

04-11-2021

130

04-11-2021

131

04-11-2021

132

04-11-2021

133

04-11-2021

134

04-11-2021

135

04-11-2021

136

04-11-2021

137

04-10-2021

138

04-09-2021

139

04-09-2021

140

04-09-2021

141

04-09-2021

142

04-09-2021

143

04-08-2021

144

04-08-2021

145

04-08-2021

146

04-08-2021

147

04-08-2021

148

04-08-2021

149

04-07-2021

150

04-07-2021

151

04-07-2021

152

04-07-2021

153

04-06-2021

154

04-06-2021

155

04-05-2021

156

04-05-2021

157

04-05-2021

158

04-05-2021

159

04-05-2021

160

04-05-2021

161

04-05-2021

162

04-05-2021

163

04-05-2021

164

04-05-2021

165

04-03-2021

166

04-03-2021

167

04-03-2021

168

04-03-2021

169

04-02-2021

170

04-02-2021

171

04-02-2021

172

04-02-2021

173

04-02-2021

JohnnyL

Check this. These are the dates of April as the result of the code. Till the 116th row they are correct and below that the day and month have interchanged.

Alex Blakenburg · Feb 21, 2022

It's a common occurrence, any day > 12 will be fine, the others will be reversed. Will have a look this afternoon.

RAJESH1960 · Feb 21, 2022

Alex Blakenburg said:
It's a common occurrence, any day > 12 will be fine, the others will be reversed. Will have a look this afternoon.

Okies. Time to hit the sack. See you tomorrow. By the way, it is 3 AM here in India.

johnnyL · Feb 21, 2022

Here is the latest version of code I came up with. It is basically a hybrid of previous code posted here. Probably not the most eloquent way to handle the situation but it seems to work. It loads the CSV file to get the dates, so Excel can't mess with the dates, then it loads the CSV file into Excel to get the rest of the data. That approach slows the performance quite a bit. Roughly about a 400% increase in time. The actual time involved though should still be under 1/2 second:

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 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.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 .4 seconds
End Sub

Maybe someone else can suggest a better way than my caveman attack I employed.

Results:

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

31-01-2022

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

Payment

202268

2,01,94,803.55 Dr.

3

31-01-2022

N PRAKASH Txn No.S55291435 Cheque No. 115927

Payment

25800

1,99,92,535.55 Dr.

4

31-01-2022

SRD LOGISTICS PRIVATELIM Txn No.S55190966 Cheque No. 297840

Payment

3063

1,99,66,735.55 Dr.

5

31-01-2022

To:0040000400037211:ABALU Txn No.S52946961

Payment

12000

1,99,63,672.55 Dr.

6

31-01-2022

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

Payment

20000

1,99,51,672.55 Dr.

7

31-01-2022

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

Payment

25000

1,99,31,672.55 Dr.

8

31-01-2022

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

Payment

46820

1,99,06,672.55 Dr.

9

31-01-2022

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

Receipt

100000

1,98,59,852.55 Dr.

10

31-01-2022

To:0335002100027863:MAYUR TEX Txn No.S52818356

Payment

30000

1,99,59,852.55 Dr.

11

31-01-2022

To:0573008700001276:MAGMA FINCORP LIMITED Txn No.S52811577

Payment

10755

1,99,29,852.55 Dr.

12

31-01-2022

To:0040000400022033:THIRDPARTY Txn No.S52803831

Payment

6000

1,99,19,097.55 Dr.

13

31-01-2022

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

Payment

45000

1,99,13,097.55 Dr.

14

31-01-2022

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

Payment

25993

1,98,68,097.55 Dr.

15

31-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S52731921

Receipt

100000

1,98,42,104.55 Dr.

16

31-01-2022

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

Payment

75056

1,99,42,104.55 Dr.

17

31-01-2022

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

Payment

35000

1,98,67,048.55 Dr.

18

30-01-2022

To:0040000400017794:THIRDPARTY Txn No.S49617550

Payment

2500

1,98,32,048.55 Dr.

19

30-01-2022

To:0040000400022033:THIRDPARTY Txn No.S49606885

Payment

15000

1,98,29,548.55 Dr.

20

30-01-2022

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

Payment

1290

1,98,14,548.55 Dr.

21

30-01-2022

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

Payment

1850

1,98,13,258.55 Dr.

22

30-01-2022

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

Payment

1400

1,98,11,408.55 Dr.

23

30-01-2022

To:0040002100096132:SAIPRIYADARSHINI Txn No.S49135071

Payment

15000

1,98,10,008.55 Dr.

24

30-01-2022

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

Payment

2890

1,97,95,008.55 Dr.

25

30-01-2022

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

Payment

2600

1,97,92,118.55 Dr.

26

30-01-2022

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

Payment

26240

1,97,89,518.55 Dr.

27

30-01-2022

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

Payment

20000

1,97,63,278.55 Dr.

28

30-01-2022

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

Payment

20000

1,97,43,278.55 Dr.

29

30-01-2022

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

Payment

25000

1,97,23,278.55 Dr.

30

30-01-2022

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

Payment

50000

1,96,98,278.55 Dr.

31

30-01-2022

To:4365002100104033:S BENTERPRISES Txn No.S48332081

Payment

18000

1,96,48,278.55 Dr.

32

30-01-2022

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

Payment

25000

1,96,30,278.55 Dr.

33

30-01-2022

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

Receipt

2727

1,96,05,278.55 Dr.

34

30-01-2022

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

Payment

17904

1,96,08,005.55 Dr.

35

30-01-2022

To:1456002100112933:TANISHQ FAB Txn No.S39064636

Payment

10790

1,95,90,101.55 Dr.

36

30-01-2022

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

Payment

47595

1,95,79,311.55 Dr.

37

30-01-2022

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

Payment

47204

1,95,31,716.55 Dr.

38

30-01-2022

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

Payment

34791

1,94,84,512.55 Dr.

39

30-01-2022

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

Payment

41234

1,94,49,721.55 Dr.

40

30-01-2022

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

Payment

37128

1,94,08,487.55 Dr.

41

30-01-2022

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

Payment

23137

1,93,71,359.55 Dr.

42

30-01-2022

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

Payment

99000

1,93,48,222.55 Dr.

43

29-01-2022

To:3616002100032433:SHRIRAVI TEXTILES Txn No.S36568018

Payment

20000

1,92,49,222.55 Dr.

44

29-01-2022

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

Payment

53000

1,92,29,222.55 Dr.

45

29-01-2022

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

Payment

27270

1,91,76,222.55 Dr.

46

29-01-2022

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

Payment

20000

1,91,48,952.55 Dr.

47

29-01-2022

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

Receipt

11226

1,91,28,952.55 Dr.

48

29-01-2022

To:0040005500052250:SAIPRIYADARSHINI Txn No.S28330610

Payment

160000

1,91,40,178.55 Dr.

49

29-01-2022

NRTGS/SIBLR52022012900217708/RONAK KOTHARI Txn No.S27350940

Receipt

1000000

1,89,80,178.55 Dr.

50

29-01-2022

SAAKAN Txn No.S25594848 Cheque No. 404451

Payment

35805

1,99,80,178.55 Dr.

51

29-01-2022

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

Receipt

14157

1,99,44,373.55 Dr.

52

28-01-2022

To:0335002100027863:MAYUR TEX Txn No.S21652886

Payment

25000

1,99,58,530.55 Dr.

53

28-01-2022

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

Payment

25000

1,99,33,530.55 Dr.

54

28-01-2022

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

Receipt

50000

1,99,08,530.55 Dr.

55

28-01-2022

To:0040000400022033:THIRDPARTY Txn No.S20078016

Payment

65000

1,99,58,530.55 Dr.

56

28-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S20048431

Receipt

14000

1,98,93,530.55 Dr.

57

28-01-2022

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

Receipt

30000

1,99,07,530.55 Dr.

58

28-01-2022

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

Payment

5000

1,99,37,530.55 Dr.

59

28-01-2022

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

Payment

890

1,99,32,530.55 Dr.

60

28-01-2022

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

Receipt

10079

1,99,31,640.55 Dr.

61

28-01-2022

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

Receipt

41363

1,99,41,719.55 Dr.

62

28-01-2022

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

Receipt

7945

1,99,83,082.55 Dr.

63

28-01-2022

IW CHQ : 115925 REJ Txn No.S9240214

Payment

236

1,99,91,027.55 Dr.

64

28-01-2022

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

Payment

2920

1,99,90,791.55 Dr.

65

27-01-2022

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

Payment

23940

1,99,87,871.55 Dr.

66

27-01-2022

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

Receipt

3498

1,99,63,931.55 Dr.

67

27-01-2022

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

Payment

4229

1,99,67,429.55 Dr.

68

26-01-2022

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

Payment

20000

1,99,63,200.55 Dr.

69

26-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S83734339

Receipt

5000

1,99,43,200.55 Dr.

70

26-01-2022

From:0040000400022033:RAJ KUMAR Txn No.S83672401

Receipt

15000

1,99,48,200.55 Dr.

71

26-01-2022

To:0040000400022033:THIRDPARTY Txn No.S75113303

Payment

5000

1,99,63,200.55 Dr.

72

26-01-2022

To:0040000400017800:THIRDPARTY Txn No.S75105575

Payment

4000

1,99,58,200.55 Dr.

73

26-01-2022

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

Payment

11145

1,99,54,200.55 Dr.

74

25-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S75105293

Receipt

15000

1,99,43,055.55 Dr.

75

25-01-2022

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

Payment

15000

1,99,58,055.55 Dr.

76

25-01-2022

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

Payment

10000

1,99,43,055.55 Dr.

77

25-01-2022

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

Payment

6638

1,99,33,055.55 Dr.

78

25-01-2022

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

Payment

3000

1,99,26,417.55 Dr.

79

25-01-2022

To:0335002100027863:MAYUR TEX Txn No.S73759969

Payment

50000

1,99,23,417.55 Dr.

80

25-01-2022

To:0040000400022033:THIRDPARTY Txn No.S73539862

Payment

50000

1,98,73,417.55 Dr.

81

25-01-2022

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

Payment

22600

1,98,23,417.55 Dr.

82

25-01-2022

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

Payment

16349

1,98,00,817.55 Dr.

83

25-01-2022

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

Payment

25000

1,97,84,468.55 Dr.

84

25-01-2022

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

Payment

8463

1,97,59,468.55 Dr.

85

25-01-2022

JAI SHRI KRISHANHANDLOO Txn No.S64604285 Cheque No. 404458

Payment

15000

1,97,51,005.55 Dr.

86

25-01-2022

IW CHQ : 404450 REJ Txn No.S64576849

Payment

236

1,97,36,005.55 Dr.

87

25-01-2022

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

Receipt

30000

1,97,35,769.55 Dr.

88

25-01-2022

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

Receipt

8692

1,97,65,769.55 Dr.

89

25-01-2022

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

Receipt

2730

1,97,74,461.55 Dr.

90

25-01-2022

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

Receipt

200000

1,97,77,191.55 Dr.

91

25-01-2022

SHORTFAL REC- Inw RtrnChrgs:404449 Txn No.S59088095

Payment

236

1,99,77,191.55 Dr.

92

21-01-2022

OW CHQ : 28600 REJ Txn No.S8826093

Payment

177

1,99,76,955.55 Dr.

93

21-01-2022

REJECT:28600:FUNDSINSUFFICIENT Txn No.S8055280

Payment

13860

1,99,76,778.55 Dr.

94

21-01-2022

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

Receipt

13860

1,99,62,918.55 Dr.

95

21-01-2022

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

Receipt

18000

1,99,76,778.55 Dr.

96

21-01-2022

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

Receipt

637

1,99,94,778.55 Dr.

97

21-01-2022

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

Payment

21240

1,99,95,415.55 Dr.

98

21-01-2022

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

Payment

10050

1,99,74,175.55 Dr.

99

20-01-2022

To:0040005500052250:SAIPRIYADARSHINI Txn No.S98751533

Payment

1100

1,99,64,125.55 Dr.

100

20-01-2022

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

Receipt

29664

1,99,63,025.55 Dr.

101

20-01-2022

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

Receipt

3000

1,99,92,689.55 Dr.

102

19-01-2022

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

Payment

39294

1,99,95,689.55 Dr.

103

19-01-2022

To:0040000400022033:THIRDPARTY Txn No.S71861881

Payment

2000

1,99,56,395.55 Dr.

104

19-01-2022

To:0040000400022033:THIRDPARTY Txn No.S71848111

Payment

3067

1,99,54,395.55 Dr.

105

19-01-2022

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

Receipt

7881

1,99,51,328.55 Dr.

106

19-01-2022

To:0040005500052250:SAIPRIYADARSHINI Txn No.S71468006

Payment

10000

1,99,59,209.55 Dr.

107

19-01-2022

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

Receipt

15081

1,99,49,209.55 Dr.

108

19-01-2022

SHORTFAL REC- Inw RtrnChrgs:404448 Txn No.S65886548

Payment

236

1,99,64,290.55 Dr.

109

19-01-2022

SHORTFAL REC- Inw RtrnChrgs:404458 Txn No.S65886483

Payment

236

1,99,64,054.55 Dr.

110

19-01-2022

SHORTFAL REC- Inw RtrnChrgs:297833 Txn No.S65885600

Payment

236

1,99,63,818.55 Dr.

111

18-01-2022

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

Payment

8000

1,99,63,582.55 Dr.

112

18-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S65322115

Receipt

500

1,99,55,582.55 Dr.

113

18-01-2022

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

Payment

16600

1,99,56,082.55 Dr.

114

18-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S65303152

Receipt

9500

1,99,39,482.55 Dr.

115

18-01-2022

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

Receipt

15000

1,99,48,982.55 Dr.

116

18-01-2022

To:0335002100027863:MAYUR TEX Txn No.S60506843

Payment

100000

1,99,63,982.55 Dr.

117

18-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S60472346

Receipt

98000

1,98,63,982.55 Dr.

118

18-01-2022

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

Receipt

36080

1,99,61,982.55 Dr.

119

18-01-2022

IW CHQ : 297833 REJ Txn No.S52754065

Payment

236

1,99,98,062.55 Dr.

120

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115904 Txn No.S49517150

Payment

236

1,99,97,826.55 Dr.

121

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404480 Txn No.S49517138

Payment

236

1,99,97,590.55 Dr.

122

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:297834 Txn No.S49517124

Payment

236

1,99,97,354.55 Dr.

123

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404447 Txn No.S49517098

Payment

236

1,99,97,118.55 Dr.

124

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404479 Txn No.S49517085

Payment

236

1,99,96,882.55 Dr.

125

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404403 Txn No.S49517065

Payment

236

1,99,96,646.55 Dr.

126

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:404446 Txn No.S49517054

Payment

236

1,99,96,410.55 Dr.

127

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:321801 Txn No.S49517037

Payment

236

1,99,96,174.55 Dr.

128

18-01-2022

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

Payment

1144.6

1,99,95,938.55 Dr.

129

18-01-2022

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

Payment

118

1,99,94,793.95 Dr.

130

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115847 Txn No.S49516987

Payment

236

1,99,94,675.95 Dr.

131

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115846 Txn No.S49516971

Payment

236

1,99,94,439.95 Dr.

132

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115843 Txn No.S49516956

Payment

236

1,99,94,203.95 Dr.

133

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115844 Txn No.S49516938

Payment

236

1,99,93,967.95 Dr.

134

18-01-2022

SHORTFAL REC- Inw RtrnChrgs:115845 Txn No.S49516917

Payment

236

1,99,93,731.95 Dr.

135

18-01-2022

D13152402CRTO004000NG00026214 Txn No.S49438594

Payment

29250

1,99,93,495.95 Dr.

136

17-01-2022

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

Payment

1500

1,99,64,245.95 Dr.

137

17-01-2022

To:0335002100027863:MAYUR TEX Txn No.S48942946

Payment

25000

1,99,62,745.95 Dr.

138

17-01-2022

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

Payment

13256

1,99,37,745.95 Dr.

139

17-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S48934634

Receipt

28000

1,99,24,489.95 Dr.

140

17-01-2022

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

Payment

32534

1,99,52,489.95 Dr.

141

17-01-2022

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

Payment

22579

1,99,19,955.95 Dr.

142

17-01-2022

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

Payment

28650

1,98,97,376.95 Dr.

143

17-01-2022

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

Receipt

45500

1,98,68,726.95 Dr.

144

17-01-2022

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

Receipt

38500

1,99,14,226.95 Dr.

145

17-01-2022

To:0040000400037202:SANJEEV KUMAR Txn No.S47719590

Payment

4848

1,99,52,726.95 Dr.

146

17-01-2022

To:0040000400022051:THIRDPARTY Txn No.S47704379

Payment

6000

1,99,47,878.95 Dr.

147

17-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S47688222

Receipt

13000

1,99,41,878.95 Dr.

148

17-01-2022

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

Receipt

8640

1,99,54,878.95 Dr.

149

17-01-2022

To:0040000400022033:THIRDPARTY Txn No.S43356097

Payment

25000

1,99,63,518.95 Dr.

150

17-01-2022

From:0040005500052250:SAIPRIYADARSHINI Txn No.S43317822

Receipt

13000

1,99,38,518.95 Dr.

151

17-01-2022

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

Receipt

21350

1,99,51,518.95 Dr.

152

17-01-2022

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

Receipt

35200

1,99,72,868.95 Dr.

153

17-01-2022

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

Receipt

2533

2,00,08,068.95 Dr.

154

14-01-2022

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

Receipt

53715

2,00,10,601.95 Dr.

155

14-01-2022

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

Receipt

6586

2,00,64,316.95 Dr.

156

13-01-2022

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

Receipt

398

2,00,70,902.95 Dr.

157

12-01-2022

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

Receipt

950

2,00,71,300.95 Dr.

158

12-01-2022

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

Receipt

1960

2,00,72,250.95 Dr.

159

12-01-2022

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

Receipt

2200

2,00,74,210.95 Dr.

160

12-01-2022

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

Receipt

14730

2,00,76,410.95 Dr.

161

11-01-2022

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

Receipt

9750

2,00,91,140.95 Dr.

162

11-01-2022

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

Receipt

5592

2,01,00,890.95 Dr.

163

07-01-2022

REJECT:9313:FUNDSINSUFFICIENT Txn No.S78275908

Payment

34045

2,01,06,482.95 Dr.

164

07-01-2022

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

Receipt

34045

2,00,72,437.95 Dr.

165

06-01-2022

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

Receipt

8639

2,01,06,482.95 Dr.

166

04-01-2022

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

Receipt

2548

2,01,15,121.95 Dr.

167

03-01-2022

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

Receipt

8936

2,01,17,669.95 Dr.

168

03-01-2022

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

Receipt

7392

2,01,26,605.95 Dr.

169

03-01-2022

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

Receipt

3350

2,01,33,997.95 Dr.

170

01-01-2022

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

Receipt

1

2,01,37,347.95 Dr.

171

JohnnyL

That is with the Jan 22 csv file

johnnyL · Feb 21, 2022

As far as the dates are concerned, I could not find a way to correct the dates to the desired format once they were loaded into excel, thus the approach above I employed to 'scrape' the dates from the CSV file prior to loading it into Excel.

Alex Blakenburg · Feb 21, 2022

Regarding fixing the dates.
In my
Post #75
Sub TestCSV_ExtractData()

VBA Code:

' DELETE THESE 2 LINES
    Workbooks.Open Filename:=CSV_FileToOpen
    Set csvWB = ActiveWorkbook

'REPLACE IT WITH THIS LINE - the text file date format matches your Region (Local) date format
    Set csvWB = Workbooks.Open(Filename:=CSV_FileToOpen, local:=True)

Also at the end of Sub TestCSV_ExtractData()
Call the reformatting sub.
Call CSV_ExtractData_ReformatOutput

RAJESH1960 · Feb 22, 2022

johnnyL said:
I could not find a way to correct the dates to the desired format

JohnnyL. But with this code, you got it all correct. columns Date, concatenated description, Voucher Type, etc., It is perfect man.

Convert csv to xlsx

RAJESH1960

Banned for repeated rules violations

Alex Blakenburg

MrExcel MVP

Excel Facts

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

RAJESH1960

Banned for repeated rules violations

Alex Blakenburg

MrExcel MVP

RAJESH1960

Banned for repeated rules violations

johnnyL

Well-known Member

johnnyL

Well-known Member

Alex Blakenburg

MrExcel MVP

RAJESH1960

Banned for repeated rules violations

Similar threads

Forum statistics

Share this page

Convert csv to xlsx

Banned for repeated rules violations

MrExcel MVP

Excel Facts

Banned for repeated rules violations

Banned for repeated rules violations

Banned for repeated rules violations

MrExcel MVP

Banned for repeated rules violations

Well-known Member

Well-known Member

MrExcel MVP

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