convert text file to csv

How is this question different than the one you just closed?
https://www.experts-exchange.com/questions/29042691/convert-text-file-to-csv.html

yes this is a bit different

first one was AR this is AP a bit different report

Found some 7F characters in the file.  Going to replace with space characters and see if it parses correctly.

can you give me screenshot?

No.  You can use your own text editor or hex viewer.

sorry did u mean u want me to do something?

What accounting/finance software are these Account Payable and Account Receivable reports being generated from?

its an old cobool system

If you want to see the 7F characters, you can you your own software to see where they are in the file.  They do no appear as visible characters.

Oh ok sorry i misunderstood.

Thanks from your help in this question

Hi Aikimark,

Would you be able to do something with this file?

I think so.  

Since there can be two sets of data, what should the resulting data look like?
Please post a workbook with the output of one of the accounts where there are two sets of data.

Cool will do in 25 min

Hi Aikimark,

I have attached the screenshot where ever you have this type of data it would fill only these fields (refer to sample sheet please)

you probably have notice that it not under every customer account

this is the data which will have READY TO PAY in column A.


Hope this helps
Capture.PNG

I asked you to post a workbook.  Please do that now.

This is a communication process where you are forced to do a single two-parter by hand.

AS/0000010 has a short second second section.  Try that account or account in your sample data set.

hi Aikimark,

please find attached.
APSAMPLE.xlsx

Doesn't seem like a proper (well-formed) CSV format.

it is actually two sets of data, so every account will have invoices which is the top bit and if there is a payment to be made it shows the bottom bit you can see in the txt file as well.

not all the accounts will have bottom bit but all account will have top bit, hope that helps

There should be two different CSV files

No one one the way i put it in sample data.

The top bit of the file fills the columns and then bottom bit if there will fill the respective columns

I know what you put.  I'm asserting that it is wrong to use a CSV file to contain two different sets of data.  How are you using the CSV data?

i mean if possible i am happy to have 2 csv files if that makes life easy. this data will be pulled in to a different accounts system.

Look at the two new worksheets in the attached workbook.  This approximates what I think you should be shooting for.
APSAMPLE--1-.xlsx

thank you aikimark,

i will check and come back to you.

Hi aIkimark,

Please see attached sheet for comments looks like its all good apart from few column read incorrectly.

thank you for your help.

Regards,

Ammar
APSAMPLE--1.xlsx

No comments about the ReadyToPay worksheet content?

Note: I haven't done any coding.  I just took the content from the first worksheet and added data from the text file you posted.

Hi aikimark,

I think leave the ready to pay and lets just get the main data if please

I've already spent time on both sets of data.  I don't want to waste my time

Oh sorry dont want to waste your time either thats why said leave the other part if its easy

You misunderstood.  Walking away from the second set of data would be a waste of my time.  If I walk away, it will be from this entire problem, not just some part of it.

Hi AikiMark I did understand what you mean thats why I said if that makes life easy they leave the second part but I understand let me upload the other part with comments too.

hi aikimark,

i have put comments in ready to pay tab nothing major, you got it all correct apart from first few columns.

THANK YOU VERY MUCH FOR YOUR HELP.
APSAMPLE--1.xlsx

I don't see any comments

oh there are in column d-f I have checked again and attached

basically D-F should be blank
APSAMPLE--1.xlsx

Here's the routine:

Sub Q_29042875(parmSourceFile)
    Const cAcctFieldnames = "ACCOUNT^SUPPLIERS NAME^TEL NO.^LAST INV^LAST PYMNT^NO OF INVS^TYPE^INV NO^INV DATE^DUE DATE^TOTAL^VAT^DISCOUNT^TAKEN/AV^SUP REF^AMOUNT^O/S^METH^AGE^STATUS^XREF"
    Const cReadyToPayFieldnames = "ACCOUNT^SUPPLIERS NAME^TEL NO.^Site^Branch^Reference^P.O. No^Reg Date^Docket^Order Value"
    Dim strData
    Dim vLineAcctData, vLineReadyToPayData
    Dim lngOutAcctSlot, lngOutReadyToPaySlot
    Dim lngAcctDetailStart, lngReadyToPayStart
    Dim oFS, oTS, oTS_ReadyToPay
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile(parmSourceFile, ForReading, True, TristateFalse)
    strData = oTS.readall
    oTS.Close
    Set oTS = oFS.OpenTextFile(oFS.getparentfoldername(parmSourceFile) & "\" & oFS.getbasename(parmSourceFile) & ".CSV", ForWriting, True, TristateFalse)
    Set oTS_ReadyToPay = oFS.OpenTextFile(oFS.getparentfoldername(parmSourceFile) & "\" & oFS.getbasename(parmSourceFile) & "_ReadyToPay.CSV", ForWriting, True, TristateFalse)
    'clean up data
    '   1. remove errant 7F characters
    strData = Replace(strData, Chr(&H7F), " ")
    
    Dim oRE_Acct, oRE_AcctDetail, oRE_ReadyToPay
    Dim oMatches_AcctData, oMatches_Acct, oMatches_ReadyToPay
    Dim oM_AcctData, oM_Acct, oM_ReadyToPay
    Dim oSM
    Dim lngSM

    Set oRE_Acct = CreateObject("vbscript.regexp")
    oRE_Acct.Global = True
    oRE_Acct.Pattern = "\n ([A-Z]{2}/\d+) +(\S.{1,28}\S) +(\S.{9,}\d)? +(\d+/\d\d/\d\d) +(\d+/\d\d/\d\d) +(\d[^\r]*)\r\n((?:\S|\s)+?)(?=(?:\n [A-Z]{2}/\d+\b)|$)"
    Set oRE_AcctDetail = CreateObject("vbscript.regexp")
    oRE_AcctDetail.Global = True
    oRE_AcctDetail.Pattern = "(?:\n|^) +(\d+) (\d) +(\d+) +(\d+/\d\d/\d\d) +(\d+/\d\d/\d\d) +(\d[^ ]*\.\d\d-?) +(\d[^ ]*\.\d\d-?) +(\d[^ ]*\.\d\d-?) +((?:PAID)|(?:\d[^ ]*\.\d\d-?)) +(\S+) +(\d+) +(\d+) +(\d+)(?: +(\d+))?"
    Set oRE_ReadyToPay = CreateObject("vbscript.regexp")
    oRE_ReadyToPay.Global = True
    oRE_ReadyToPay.Pattern = "\n.{55} +(\d+) +(\d+) +(\d+) +(\d+) +(\d+/\d\d/\d\d) +(\d+) +(\d[^ ]*\.\d\d)(?=\r\n)"

    ReDim vLineAcctData(20)
    ReDim vLineReadyToPayData(9)
    
    oTS.writeline Replace(cAcctFieldnames, "^", ",")
    oTS_ReadyToPay.writeline Replace(cReadyToPayFieldnames, "^", ",")
    
    If oRE_Acct.test(strData) Then
        Set oMatches_AcctData = oRE_Acct.Execute(strData)
        strData = vbNullString
        For Each oM_AcctData In oMatches_AcctData
            lngOutAcctSlot = 0
            lngOutReadyToPaySlot = 0
            With oM_AcctData
                For lngSM = 0 To .submatches.Count - 2
                    vLineAcctData(lngOutAcctSlot) = Trim(.submatches(lngSM))
                    lngOutAcctSlot = lngOutAcctSlot + 1
                Next
                
                lngAcctDetailStart = lngOutAcctSlot
                'delimit with quotes if item text contains a comma
                For lngSM = 0 To lngOutAcctSlot - 1
                    If InStr(vLineAcctData(lngSM), ",") <> 0 Then
                        vLineAcctData(lngSM) = Chr(34) & vLineAcctData(lngSM) & Chr(34)
                    End If
                Next
                
                If oRE_AcctDetail.test(.submatches(6)) Then
                    Set oMatches_Acct = oRE_AcctDetail.Execute(.submatches(6))
                    For Each oM_Acct In oMatches_Acct
                        For lngSM = 0 To oM_Acct.submatches.Count - 1
                            vLineAcctData(lngOutAcctSlot) = Trim(oM_Acct.submatches(lngSM))
                            lngOutAcctSlot = lngOutAcctSlot + 1
                        Next
                        'delimit with quotes if item text contains a comma
                        For lngSM = lngAcctDetailStart To UBound(vLineAcctData)
                            If InStr(vLineAcctData(lngSM), ",") <> 0 Then
                                vLineAcctData(lngSM) = Chr(34) & vLineAcctData(lngSM) & Chr(34)
                            End If
                        Next
                        
                        oTS.writeline Join(vLineAcctData, ",")
                        lngOutAcctSlot = lngAcctDetailStart
                    Next
                
                End If
                
                If oRE_ReadyToPay.test(.submatches(6)) Then
                    Set oMatches_ReadyToPay = oRE_ReadyToPay.Execute(.submatches(6))
                    For lngSM = 0 To 2
                        vLineReadyToPayData(lngOutReadyToPaySlot) = Trim(.submatches(lngSM))
                        lngOutReadyToPaySlot = lngOutReadyToPaySlot + 1
                    Next
                        
                    lngReadyToPayStart = lngOutReadyToPaySlot
                        
                    'delimit with quotes if item text contains a comma
                    For lngSM = 0 To lngOutReadyToPaySlot - 1
                        If InStr(vLineReadyToPayData(lngSM), ",") <> 0 Then
                            vLineReadyToPayData(lngSM) = Chr(34) & vLineReadyToPayData(lngSM) & Chr(34)
                        End If
                    Next
                    
                    For Each oM_ReadyToPay In oMatches_ReadyToPay
                        For lngSM = 0 To oM_ReadyToPay.submatches.Count - 1
                            vLineReadyToPayData(lngOutReadyToPaySlot) = Trim(oM_ReadyToPay.submatches(lngSM))
                            lngOutReadyToPaySlot = lngOutReadyToPaySlot + 1
                        Next
                        
                        'delimit with quotes if item text contains a comma
                        For lngSM = lngReadyToPayStart To UBound(vLineReadyToPayData)
                            If InStr(vLineReadyToPayData(lngSM), ",") <> 0 Then
                                vLineReadyToPayData(lngSM) = Chr(34) & vLineReadyToPayData(lngSM) & Chr(34)
                            End If
                        Next
                        
                        oTS_ReadyToPay.writeline Join(vLineReadyToPayData, ",")
                        lngOutReadyToPaySlot = lngReadyToPayStart
                    Next
                End If
            End With
        Next
    End If
    oTS.Close
    oTS_ReadyToPay.Close
End Sub

Select allOpen in new window

Here's the invocation:

Q_29042875 "C:\Users\Mark\Downloads\AP070717.txt"

Select allOpen in new window

I've attached the resulting files
AP070717.CSV
AP070717_ReadyToPay.CSV

Perfect thanks