Forum Discussion

KCkum's avatar
KCkum
Copper Contributor
Nov 27, 2020

Xero output file in CSV. How to convert to txt file for upload in bank system for payment

Hi I need some help to convert a  file in CSV (Xero output) into a txt file for upload in bank system for payment. I am trying to build a process when I can take the CSV file, take the data and paste it into a excel template. This template then coverts the data according to the upload format. Then save this template file as a txt file, ready for upload into bank. Any suggestions / ideas will be welcomed.

Attached is sample files only ('20201131.CSV' is Xero output).  Since I cannot attach a txt file (bank upload), I have copied the examples as below :

10 OCBCSGSGXXX501100100001 On Behalf Of abcd GIROABCF00000000000107082018
OCBCSGSGXXX501111222001 TINTINSTAR1 00000000000000020Paymentdetails1 IVPTM ETest@gmail.com
INV Test invoice
INV Invoice Number: 12345 Item Description : Test Item
DBSSSGSGXXX029888999002 TINTINSTAR2 00000000000000025Paymentdetails1 M ETest@gmail.com
INV Test invoice
INV Invoice Number: 12345 Item Description : Test Item 1
INV Invoice Number: 11122 Item Description : Test Item 2
Paynow Test Bene 1 00000000000000030Paymentdetails1 M NRIC S1111111A
Paynow Test Bene 2 00000000000000035Paymentdetails2 M MSISDN +6591111111
Paynow Test Bene 3 00000000000000040Paymentdetails3 M MSISDN +6592222222

excel

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 27, 2020

    KCkum 

    I'd do that with Power Query

    - query csv from some folder

    - query additional information which is required to form the text (I guess email, etc)

    - transform all together into one column table with required texts

    - return it to Excel sheet and save it as Unicode txt, manually or by macro similar to NikolinoDE  suggested, i guess simple bat file will work as well.

    • KCkum's avatar
      KCkum
      Copper Contributor
      Nov 30, 2020

      SergeiBaklan 

      Thanks so much for your response which sparked me off in the right direction. It was helpful !

      Thanks also to all the others who provided input / tips. They will surely come in handy as I build my solution.

  • Ramiz_Assaf's avatar
    Ramiz_Assaf
    Iron Contributor
    Nov 27, 2020

    KCkum 

     

    I tried reading it multiple times.. I cannot understand what is the inpus.. what is the output?

    please spend more time clarifying this for us.

    thanks

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 27, 2020

    KCkum 

    Fished fresh from the internet here, as there is no time.
    Maybe this will help you to get on with your project.
     

     

     

    Option Explicit

Sub Convert_CSV_to_XLS ()
Dim i As Long, delayed As String
Dim file Form As String
'With a backslash at the end
dir = "C: \ Folder1 \"
'File format
fileForm = "csv"
On Error GoTo error
ChDrive Left (galv, 2)
ChDir galv
With Application.FileSearch
    .NewSearch
    .LookIn = delayed
    .SearchSubFolders = False
    .FileType = msoFileTypeAllFiles
    .Execute
     For i = 1 To .FoundFiles.Count
          Application.StatusBar = "File" & i & "from" & .FoundFiles.Count & "is being processed"
          If Right (.FoundFiles (i), 3) = fileForm Then
               Application.ScreenUpdating = False
               Debug.Print .FoundFiles (i)
               Workbooks.Open .FoundFiles (i), local: = True
               ActiveWorkbook.SaveAs Left (.FoundFiles (i), Len (.FoundFiles (i)) - 3) & "xls"
               ActiveWorkbook.Close False
               Application.ScreenUpdating = True
          End If
     Next i
End With
Error Exit:
Application.ScreenUpdating = True
Application.StatusBar = False
Exit Sub

error:
MsgBox Err.Number & ";" & Err.Description
Resume ErrorExit
End Sub [code] [/ code]

     

     

     

    Thank you for your understanding and patience

     

    Nikolino

    I know I don't know anything (Socrates)

Resources