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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If it is possible to convert from text or any other format to xlsx with the help of a code, then I can convert a pdf in that format. None of the other sites which convert pdf to xlsx are 100% perfect, especially bank statements. Not even paid sites. There is always room for some cleaning.
 
Upvote 0
Hi,​
​
as yet stated previously the better is to not convert any pdf file but just ask to the creator of the pdf file to create a standard csv file instead …​
​
And again as yet stated, with the pdf conversion way to some text file so the VBA procedure may fail if it is not always the same conversion way used …​
 
Upvote 0
Until you get an alternative format option, give this a try:
(I have left the output sheet to be JohnnyL, since you already have that sheet, change it when you are ready but the code is assuming the sheet already exists.)

VBA Code: 
Sub TestCSV_ExtractData()

    Dim destWB As Workbook, csvWB As Workbook
    Dim destSht As Worksheet, csvSht As Worksheet
    Dim csvRng As Range
    Dim csvArr As Variant
    
    Dim iCnt As Long, jCnt As Long, jCntRvrs As Long, jCntMid As Long
    Dim jLeft As Long, jRight As Long
    Dim strBal As String
    
    Dim outArr() As Variant
    Dim outRow As Long, outCol As Long
    Dim hdgArr As Variant
    
    Application.ScreenUpdating = False
    
    Set destWB = ThisWorkbook
    Set destSht = destWB.Worksheets("JohnnyL")
    
    Dim CSV_FileToOpen  As Variant
'
    CSV_FileToOpen = Application.GetOpenFilename("Text files,*.csv", , "Select file", , False)
    If CSV_FileToOpen = False Then
        Exit Sub
    End If
    Workbooks.Open Filename:=CSV_FileToOpen

    Set csvWB = ActiveWorkbook
    Set csvSht = csvWB.ActiveSheet
    Set csvRng = csvSht.UsedRange
    csvArr = csvRng.Value
    
    hdgArr = Array("Txn No", "Txn Date", "Description", "Branch Name", "Cheque No", "Dr Amount", "Cr Amount", "Balance", "Dr/Cr", "Src Row")
    ReDim outArr(1 To UBound(csvArr, 1), 1 To 10)
    
    outRow = 1
    
    For iCnt = 1 To UBound(csvArr, 1)
        If IsNumeric(Right(csvArr(iCnt, 1), 6)) Then
            outCol = 0
            For jCnt = 1 To UBound(csvArr, 2)
                If csvArr(iCnt, jCnt) <> "" Then
                    outCol = outCol + 1
                    outArr(outRow, outCol) = csvArr(iCnt, jCnt)
                   If outCol = 4 Then
                        jLeft = jCnt + 1
                        Exit For
                    End If
                End If

             Next jCnt
             
            For jCntRvrs = UBound(csvArr, 2) To jLeft Step -1
                If csvArr(iCnt, jCntRvrs) <> "" Then
                    strBal = csvArr(iCnt, jCntRvrs)
                    outArr(outRow, 9) = Mid(strBal, Len(strBal) - 2, 2)
                    outArr(outRow, 8) = Replace(Left(strBal, Len(strBal) - 4), ",", "")
                    outArr(outRow, 7) = csvArr(iCnt, jCntRvrs - 1)
                    outArr(outRow, 6) = csvArr(iCnt, jCntRvrs - 3)
                    jRight = jCntRvrs - 3 - 1
                    Exit For
                End If
            Next jCntRvrs
            
            For jCntMid = jLeft To jRight
                 If csvArr(iCnt, jCntMid) <> "" Then
                    outArr(outRow, 5) = csvArr(iCnt, jCntMid)
                 End If
            Next jCntMid
            
            outArr(outRow, 2) = Replace(outArr(outRow, 2), "-", "/")
            outArr(outRow, 3) = Replace(outArr(outRow, 3), vbLf, " ")
            outArr(outRow, 4) = Replace(outArr(outRow, 4), vbLf, " ")
            outArr(outRow, 10) = iCnt
            outRow = outRow + 1
        End If
    Next iCnt

    With destSht
        .UsedRange.Clear
        .Range("A1").Resize(, UBound(hdgArr) + 1) = hdgArr
        .Range("A1").Resize(, UBound(hdgArr) + 1).Font.Bold = True
        .Range("A1").Offset(1).Resize(outRow - 1, UBound(outArr, 2)).Value = outArr
        ' Convert Date Column to Date
        .Range("A1").Offset(1, 1).Resize(outRow - 1).TextToColumns Destination:=.Range("A1").Offset(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 4), TrailingMinusNumbers:=True
        .Columns("E").NumberFormat = "General"                                                  ' Cheque No field
        .Range("F:H").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .UsedRange.Columns.AutoFit
    End With
    
    Application.DisplayAlerts = False
    csvWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Alex Blakenburg said:
Until you get an alternative format option, give this a try:
(I have left the output sheet to be JohnnyL, since you already have that sheet, change it when you are ready but the code is assuming the sheet already exists.)

VBA Code: 
Sub TestCSV_ExtractData()

    Dim destWB As Workbook, csvWB As Workbook
    Dim destSht As Worksheet, csvSht As Worksheet
    Dim csvRng As Range
    Dim csvArr As Variant
 
    Dim iCnt As Long, jCnt As Long, jCntRvrs As Long, jCntMid As Long
    Dim jLeft As Long, jRight As Long
    Dim strBal As String
 
    Dim outArr() As Variant
    Dim outRow As Long, outCol As Long
    Dim hdgArr As Variant
 
    Application.ScreenUpdating = False
 
    Set destWB = ThisWorkbook
    Set destSht = destWB.Worksheets("JohnnyL")
 
    Dim CSV_FileToOpen  As Variant
'
    CSV_FileToOpen = Application.GetOpenFilename("Text files,*.csv", , "Select file", , False)
    If CSV_FileToOpen = False Then
        Exit Sub
    End If
    Workbooks.Open Filename:=CSV_FileToOpen

    Set csvWB = ActiveWorkbook
    Set csvSht = csvWB.ActiveSheet
    Set csvRng = csvSht.UsedRange
    csvArr = csvRng.Value
 
    hdgArr = Array("Txn No", "Txn Date", "Description", "Branch Name", "Cheque No", "Dr Amount", "Cr Amount", "Balance", "Dr/Cr", "Src Row")
    ReDim outArr(1 To UBound(csvArr, 1), 1 To 10)
 
    outRow = 1
 
    For iCnt = 1 To UBound(csvArr, 1)
        If IsNumeric(Right(csvArr(iCnt, 1), 6)) Then
            outCol = 0
            For jCnt = 1 To UBound(csvArr, 2)
                If csvArr(iCnt, jCnt) <> "" Then
                    outCol = outCol + 1
                    outArr(outRow, outCol) = csvArr(iCnt, jCnt)
                   If outCol = 4 Then
                        jLeft = jCnt + 1
                        Exit For
                    End If
                End If

             Next jCnt
          
            For jCntRvrs = UBound(csvArr, 2) To jLeft Step -1
                If csvArr(iCnt, jCntRvrs) <> "" Then
                    strBal = csvArr(iCnt, jCntRvrs)
                    outArr(outRow, 9) = Mid(strBal, Len(strBal) - 2, 2)
                    outArr(outRow, 8) = Replace(Left(strBal, Len(strBal) - 4), ",", "")
                    outArr(outRow, 7) = csvArr(iCnt, jCntRvrs - 1)
                    outArr(outRow, 6) = csvArr(iCnt, jCntRvrs - 3)
                    jRight = jCntRvrs - 3 - 1
                    Exit For
                End If
            Next jCntRvrs
         
            For jCntMid = jLeft To jRight
                 If csvArr(iCnt, jCntMid) <> "" Then
                    outArr(outRow, 5) = csvArr(iCnt, jCntMid)
                 End If
            Next jCntMid
         
            outArr(outRow, 2) = Replace(outArr(outRow, 2), "-", "/")
            outArr(outRow, 3) = Replace(outArr(outRow, 3), vbLf, " ")
            outArr(outRow, 4) = Replace(outArr(outRow, 4), vbLf, " ")
            outArr(outRow, 10) = iCnt
            outRow = outRow + 1
        End If
    Next iCnt

    With destSht
        .UsedRange.Clear
        .Range("A1").Resize(, UBound(hdgArr) + 1) = hdgArr
        .Range("A1").Resize(, UBound(hdgArr) + 1).Font.Bold = True
        .Range("A1").Offset(1).Resize(outRow - 1, UBound(outArr, 2)).Value = outArr
        ' Convert Date Column to Date
        .Range("A1").Offset(1, 1).Resize(outRow - 1).TextToColumns Destination:=.Range("A1").Offset(1, 1), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 4), TrailingMinusNumbers:=True
        .Columns("E").NumberFormat = "General"                                                  ' Cheque No field
        .Range("F:H").EntireColumn.NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);;_(@_)"   ' Amount fields
        .UsedRange.Columns.AutoFit
    End With
 
    Application.DisplayAlerts = False
    csvWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
 
    Application.ScreenUpdating = True

End Sub
Click to expand...
Thanks Alex. Dates to be corrected in the code. JohnnyL aslo got the dates wrong too but later he corrected it in the code. The dates if November then 1 to 11 dates are displayed in mm-dd-yyyy format and other dates are displayed in dd-mm-yyyy format. The dd-mm-yyyy is the required format in all the cells. There are many steos to be added in the code. Please watch this video link. If you could help me get to the final format I would appreciate it.
Steps to complete.mp4
 
Last edited:
Upvote 0
RAJESH1960 said:
required format in all the cells.
Click to expand...
Sorry forgot to delete the branch name in the video $E$1 from the formula. If you noted I have sorted the src row in the descending order and cut the data and pasted the data to a new sheet. That way I don't have to insert a new sheet and rename the sheet to JohnnyL.
 
Upvote 0
I checked the site. They don't have any pdf to xlsx convertor not csv to xlsx. I assume that it is about converting xlsx file to other formats.
 
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
662
felixstraube
F
N
  • Solved
Multiple statement lines per transaction
Replies
3
Views
429
Nygie
N

Forum statistics

Threads
1,216,104
Messages
6,128,856
Members
449,472
Latest member
ebc9

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