Convert csv to xlsx

[RAJESH1960]

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]

The five most important columns needed require to import my data are all available and ready and in newest to oldest order. I can record a macro and sort the data from oldest to newest and that is it. Tonight I will check all the 5 csv files and let you know if this is final. Till then, have a nice day. ☺

 

[RAJESH1960]

If you add this code to your code, then we can close this query. Please note, do not sort as per data without the line column else the dates rows will be sorted but not in the original order as in the csv file. I have also formatted the amount columns.

Sheets("JohnnyL").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Line"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A2:A3").Select
    Selection.AutoFill Destination:=Range("A2:A197")
    Range("A2:A197").Select
    Columns("E:F").Select
    Selection.NumberFormat = "0.00"
    Range("C3").Select
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "A2:A197"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("JohnnyL").Sort
        .SetRange Range("A1:G197")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select

 

[RAJESH1960]

This is the tough part me to write the code. The ranges selected till the last row.

Range("A2:A197")

 

[Alex Blakenburg]

Johnny's code doesn't have the RowNo in it but if you just sort on the date it should keep the sub-sort in the original order.

    With destSht.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange destSht.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

My code does have the original row reference so you can use it as a secondary sort but in theory since it is the only sort you shouldn't need it.

    With destV2Sht.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add2 Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange destV2Sht.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Alex Blakenburg]

Oops I missed putting the sheet reference in front of the Key:=Range
So for Johnny's code s/be

    With destSht.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=destSht.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange destSht.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

For my code:

    With destV2Sht.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=destV2Sht.Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add2 Key:=destV2Sht.Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange destV2Sht.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[RAJESH1960]

if you just sort on the date

Alex. I added your sort code to JohnnyL's code. It worked but the order of the rows has changed as I said in my post #112. AS there are multiple rows having the same date, the order of the xlsx will be different from the csv order.
You need to insert a Line column in the sheet at the end of the code, to get the exact order. In the end of the code you can delete the Line column. That way it will be perfect.

 

[RAJESH1960]

I have in my result sheet entered in column I, this formula to get the amounts in number format.
=IF(ISNUMBER(FIND("Cr.",$H2)),SUBSTITUTE($H2,"Cr.",""),IF(ISNUMBER(FIND("Dr.",$H2)),SUBSTITUTE($H2,"Dr.",""),$H2))
So, once the sort is corrected, it is done.

 

[Alex Blakenburg]

do not sort as per data without the line column else the dates rows will be sorted but not in the original order as in the csv file

Ahh it depends on what you consider to be the "original order as in the csv file".
I think what you mean is that since we are tipping the date upside down you want all the lines in the date upside down as well.
Technically you don't then even need to sort on date, just give each row a line number and sort descending.

I will leave it up to @johnnyL to modify his code to cater for that.

PS: if while you are waiting for Johnny, you want to let me know what is not working in what I gave you I wouldn't mind just finishing off what I started and seeing what I missed.
PPS: In your number conversion formula, you might want to decide whether you want the credit or debit to be negative and add that logic into your formula. In my code I have converted Cr to negative.

I will log out shortly, be back tomorrow.

 

[RAJESH1960]

what I missed.

Your new code is exceptional but not complete. Even the amount with Dr. and Cr. is separated. In your code, Sorting also not needed as the row numbers can be reversed in the code.
As in the very first mentioned, I need to concatenate Description, first, with Trn. No., Branch Name and cheque No. and the voucher type column with Receipt and Payment all included in the Description column.
As all these things are already completed by JohnnyL, but if you still want to finish this, you are most welcome. Good Luck.

 

[Alex Blakenburg]

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.