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

Copper Contributor

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

4 Replies

@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)

@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

@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.

@Sergei Baklan 

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.