Convert csv to xlsx

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello code experts,
I have this csv file which was converted from pdf. The columns are not in one order to get the data in a columnar view. It will take more than a hour to arrange each line in a particular format. JohnnyL's code has helped me a lot to get the columns in order in less than a second. I am facing one problem in one of the conversions. The code is not able to concatenate the Cheque No. to the Description in 3 different rows and in one of the lines it is not taking the amount. Hence the balances are not matching. I have colored the lines with errors. Need your expertise to correct the code. Thanks.
Conver CSV to XLSX.xlsm
 
Last edited:
Sort by date Sort by votes
I would be interested to see how MS 365 Power Query would go with converting the pdf. Your profile says you only have 2019.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Alex Blakenburg said:
I would be interested to see how MS 365 Power Query would go with converting the pdf. Your profile says you only have 2019.
Click to expand...
I will buy the version 365 very soon for my personal laptop to learn the new features. Only problem is in my office where I go to learn have 2019 in all the systems. So I have to use the 2019 version only till they change.
 
Upvote 0
Alex Blakenburg said:
It sounds like the code, I gave you will work for now until you find a better way of getting the pdf into Excel. ;)
Click to expand...
This is why I have been trying to understand the csv file since the last few days. Why the rows are not under the headings in each page.? I noticed the pattern and thought that if a code was run to convert csv into xlsx, it would be perfect. I am adding some more points to get the required display of columns in the code. Did you watch the video I shared.? I don't think it would be a big deal for you to write and complete the rest of the code as per the video.
 
Upvote 0
RAJESH1960 said:
Did you watch the video I shared.? I don't think it would be a big deal for you to write and complete the rest of the code as per the video.
Click to expand...
I assume you are not the end user. I would be talking to the end user and get to understand how they are using the data.
Concatenating data and deleting the columns is a bad idea. As it is in a database format, you can filter it, sort it, pivot it, once you concatenate it into a single field those options are gone. It is a bit like giving someone a PDF file instead of a XL friendy file. ;)
eg deleting the Dr/Cr field will cause you problems when you get a Cr balance. You need to decide whether you want Dr or Cr to show as positive and apply that before removing the column.

I wouldn't be at all surprised if the user then spends time and effort pulling it apart again. So talk to them

If you want to go ahead anyway. Record a macro doing what you have in the video and post it here and I will make the changes so that it is reusable.
 
Upvote 0
Alex Blakenburg said:
I assume you are not the end user. I would be talking to the end user and get to understand how they are using the data.
Concatenating data and deleting the columns is a bad idea. As it is in a database format, you can filter it, sort it, pivot it, once you concatenate it into a single field those options are gone. It is a bit like giving someone a PDF file instead of a XL friendy file. ;)
eg deleting the Dr/Cr field will cause you problems when you get a Cr balance. You need to decide whether you want Dr or Cr to show as positive and apply that before removing the column.

I wouldn't be at all surprised if the user then spends time and effort pulling it apart again. So talk to them

If you want to go ahead anyway. Record a macro doing what you have in the video and post it here and I will make the changes so that it is reusable.
Click to expand...
There ain't any users except me for now. Once I complete it, I learn accounts and see how they work and practice the same for months and understand the steps involved. If the steps are common every time, then in my free time, with the little knowledge of formulas, codes and with the help of many experts like you in the forum, I create a excel sheet where I can import the data directly to the server in minutes and test it before using it in the office. If it is perfect, then I teach others how to import it directly to the server, with the help of my excel sheet. Right now, they collect the pdf from the client and feed each entry manually which will take hours to do so and if the data is big it may take days. This project is only for bank statements. Got a long way to go.
I will record a macro and share it with you, the remaining steps involved. See you tonight hopefully.?
 
Upvote 0
First of all, the date needs to be corrected in the code. Please note: My macro doesn’t clear the whole data in Raw sheet. When the next data of which xml is to be generated is less than the previous data, the old data is not completely deleted. There are rows with the old data which are not deleted in the raw data. The range in the code needs to delete all the data from A2 to G2 till the last row with data. If any other doubts, please let me know. This is the macro code I have recorded.

Rich (BB code): 
Option Explicit

Sub Macro1()

    Sheets("Raw").Select
    Range("A2:G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    Range("A2").Select
    
    Sheets("JohnnyL").Select
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Range("D2").Select
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "D2:D197"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "E2:E197"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("JohnnyL").Sort
        .SetRange Range("A1:I197")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-7]&R1C1&RC[-9]&R1C4&RC[-6]&R1C5&RC[-5]"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = " Cheque No "
    Range("D1").Select
    ActiveCell.FormulaR1C1 = " Branch Name "
    Range("B1").Select
    ActiveCell.FormulaR1C1 = " Txn Date "
    Range("J2:J6").Select
    Selection.FillDown
    Range("J6").Select
    ActiveCell.FormulaR1C1 = "=RC[-7]&R1C1&RC[-9]&R1C5&RC[-5]"
    Range("J6:J22").Select
    Selection.FillDown
    Range("J22").Select
    ActiveCell.FormulaR1C1 = "=RC[-7]&R1C1&RC[-9]"
    Range("J22").Select
    Selection.AutoFill Destination:=Range("J22:J197")
    Range("J22:J197").Select
    Range("J2").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("A1").Select
    ActiveCell.FormulaR1C1 = " Txn No"
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Range("D2").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Range("H2").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Range("A2").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "F2:F197"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("JohnnyL").Sort
        .SetRange Range("A1:F197")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "F2:F197"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    Application.CutCopyMode = False
    Sheets("JohnnyL").Select
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Columns("G:G").Select
    Selection.Cut
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "E2:E197"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("JohnnyL").Sort
        .SetRange Range("A1:G197")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Payment"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B197")
    Range("B2:B197").Select
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=15
    Range("B109").Select
    ActiveCell.FormulaR1C1 = "Receipt"
    Range("B109").Select
    Selection.AutoFill Destination:=Range("B109:B197")
    Range("B109:B197").Select
    Range("A1").Select
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "C2:C197"), SortOn:=xlSortOnValues, Order:=xlAscending, 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
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1").Select
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JohnnyL").Sort.SortFields.Add2 Key:=Range( _
        "C2:C197"), 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
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:D197").Select
    Selection.Copy
    ActiveSheet.Next.Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("F2").Select
    ActiveSheet.Previous.Select
    Application.CutCopyMode = False
    Range("E2:F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("E2:F197").Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Sheets("JohnnyL").Select
    ActiveWindow.SmallScroll Down:=-162
    Cells.Select
    Selection.Clear
    Range("A1").Select
    Sheets("Raw").Select
    'Generate xml code will be added after this row later
End Sub
 
Upvote 0
Since all the dates of November month, the code is generating dates of different months from row 2 to 55. The month is on the left side and day is in middle. and the rest of the rows it is correct. Please view this
Test 21.02 csv to slsx convertor - Copy.xlsm
A
1DATE
211-01-2021
311-02-2021
411-02-2021
511-04-2021
611-04-2021
711-05-2021
811-06-2021
911-06-2021
1011-08-2021
1111-08-2021
1211-08-2021
1311-08-2021
1411-09-2021
1511-09-2021
1611-09-2021
1711-09-2021
1811-09-2021
1911-10-2021
2011-10-2021
2111-10-2021
2211-11-2021
2311-11-2021
2411-11-2021
2511-11-2021
2611-11-2021
2711-11-2021
2811-11-2021
2911-11-2021
3011-11-2021
3111-11-2021
3211-11-2021
3311-11-2021
3411-11-2021
3511-11-2021
3611-11-2021
3711-11-2021
3811-11-2021
3911-11-2021
4011-11-2021
4111-11-2021
4211-11-2021
4311-11-2021
4411-11-2021
4511-11-2021
4611-11-2021
4711-11-2021
4811-11-2021
4911-11-2021
5011-12-2021
5111-12-2021
5211-12-2021
5311-12-2021
5411-12-2021
5511-12-2021
5614-11-2021
5714-11-2021
5814-11-2021
5914-11-2021
6014-11-2021
6114-11-2021
6214-11-2021
6314-11-2021
6414-11-2021
6514-11-2021
6614-11-2021
6714-11-2021
6814-11-2021
6914-11-2021
7014-11-2021
7114-11-2021
7214-11-2021
7314-11-2021
7414-11-2021
7514-11-2021
7615-11-2021
7715-11-2021
7815-11-2021
7915-11-2021
8015-11-2021
8115-11-2021
8215-11-2021
8315-11-2021
8415-11-2021
8515-11-2021
8615-11-2021
8715-11-2021
8815-11-2021
8915-11-2021
9015-11-2021
9115-11-2021
9215-11-2021
9316-11-2021
9416-11-2021
9516-11-2021
9616-11-2021
9716-11-2021
9816-11-2021
9916-11-2021
10016-11-2021
10116-11-2021
10216-11-2021
10316-11-2021
10416-11-2021
10516-11-2021
10616-11-2021
10716-11-2021
10816-11-2021
10916-11-2021
11016-11-2021
11116-11-2021
11217-11-2021
11317-11-2021
11417-11-2021
11517-11-2021
11617-11-2021
11717-11-2021
11817-11-2021
11917-11-2021
12018-11-2021
12118-11-2021
12218-11-2021
12318-11-2021
12418-11-2021
12518-11-2021
12618-11-2021
12718-11-2021
12819-11-2021
12919-11-2021
13019-11-2021
13119-11-2021
13219-11-2021
13319-11-2021
13419-11-2021
13519-11-2021
13620-11-2021
13720-11-2021
13820-11-2021
13920-11-2021
14020-11-2021
14120-11-2021
14220-11-2021
14320-11-2021
14420-11-2021
14520-11-2021
14620-11-2021
14720-11-2021
14820-11-2021
14920-11-2021
15020-11-2021
15120-11-2021
15221-11-2021
15321-11-2021
15421-11-2021
15522-11-2021
15622-11-2021
15722-11-2021
15822-11-2021
15922-11-2021
16022-11-2021
16123-11-2021
16223-11-2021
16323-11-2021
16423-11-2021
16523-11-2021
16623-11-2021
16723-11-2021
16823-11-2021
16923-11-2021
17023-11-2021
17123-11-2021
17223-11-2021
17323-11-2021
17423-11-2021
17523-11-2021
17623-11-2021
17723-11-2021
17823-11-2021
17923-11-2021
18024-11-2021
18124-11-2021
18225-11-2021
18325-11-2021
18425-11-2021
18525-11-2021
18626-11-2021
18726-11-2021
18826-11-2021
18926-11-2021
19026-11-2021
19128-11-2021
19229-11-2021
19329-11-2021
19430-11-2021
19530-11-2021
19630-11-2021
19730-11-2021
Raw
 
Upvote 0
Actually, in my macro, it should be Sheets("Raw").Select instead of ActiveSheet.Next.Select and also ActiveSheet.Previous.Select in some place. I hope you understand.
 
Upvote 0
You have a lot of stuff going on in your recorded macro that was not in your video.
Run this code as a separate module after you do the csv import and then explain what needs to happen after that.
It won't happen today though.

Note: It assumes that the sheet Raw exists

VBA Code: 
Sub CSV_ExtractData_ReformatOutput()

    Dim destV2Sht As Worksheet
    Dim destSht As Worksheet
    Dim newLRow As Long, i As Long, j As Long
    Dim newDesc As String
    Dim colNo As Long
    
    Dim arrData As Variant
    Dim arrOut() As Variant

    Set destSht = Worksheets("JohnnyL")
    Set destV2Sht = Worksheets("Raw")
    
    destV2Sht.Range("A1").CurrentRegion.Clear
    arrData = destSht.Range("A1").CurrentRegion.Value
    
    For i = 2 To UBound(arrData)
        If arrData(i, 9) = "Cr" Then arrData(i, 8) = arrData(i, 8) * -1
        
        'Concatenate Description
        colNo = 1                                                                       ' Add Txn No
        newDesc = arrData(i, 3) & " " & arrData(1, colNo) & ":" & arrData(i, colNo)     ' Add Txn No
        
        colNo = 4                                                                       ' Add Branch
        If arrData(i, colNo) <> "-" Then
            newDesc = newDesc & " " & arrData(1, colNo) & ":" & arrData(i, colNo)       ' Add Branch
        End If
        
        colNo = 5                                                                       ' Add Cheque
        If arrData(i, colNo) <> "" Then
            newDesc = newDesc & " " & arrData(1, colNo) & ":" & arrData(i, colNo)       ' Add Cheque No
        End If
        
        arrData(i, 3) = newDesc
              
    Next i
    
    ReDim arrOut(1 To UBound(arrData, 1), 1 To 6)
    For j = 1 To UBound(arrData)
        arrOut(j, 1) = arrData(j, 10)   ' Src Row
        arrOut(j, 2) = arrData(j, 2)    ' Txn  Date
        arrOut(j, 3) = arrData(j, 3)    ' Description (Concatenated)
        arrOut(j, 4) = arrData(j, 6)    ' Dr Amt
        arrOut(j, 5) = arrData(j, 7)    ' Cr Amt
        arrOut(j, 6) = arrData(j, 8)    ' Balance
    Next j
    
    destV2Sht.Range("A1").Resize(UBound(arrOut, 1), UBound(arrOut, 2)).Value = arrOut
    
    With destV2Sht
        Range("A1").CurrentRegion.Resize(1).Font.Bold = True
        .Range("D:F").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .UsedRange.Columns.AutoFit
    End With
    
    With destV2Sht
        newLRow = .Cells(Rows.Count, "A").End(xlUp).Row
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Bulk Convert CSV to XLSX Files While Retaining the Leading Zero
Replies
4
Views
2K
joyfu1
J
S
  • Question
Is there any other way to convert xlsb. to xlsx. file
Replies
2
Views
1K
Sinh Le
S
H
  • Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
2K
HeinrichPaul
H
G
  • Question
Converting a ASCII text file with strange delimiters.
2
Replies
14
Views
725
felixstraube
F
N
  • Solved
Multiple statement lines per transaction
Replies
3
Views
442
Nygie
N

Forum statistics

Threads
1,216,526
Messages
6,131,187
Members
449,631
Latest member
mehboobahmad

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top