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

%3CLINGO-SUB%20id%3D%22lingo-sub-1935548%22%20slang%3D%22en-US%22%3EXero%20output%20file%20in%20CSV.%20How%20to%20convert%20to%20txt%20file%20for%20upload%20in%20bank%20system%20for%20payment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935548%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20need%20some%20help%20to%20convert%20a%20%26nbsp%3Bfile%20in%20CSV%20(Xero%20output)%20into%20a%20txt%20file%20for%20upload%20in%20bank%20system%20for%20payment.%20I%20am%20trying%20to%20build%20a%20process%20when%20I%20can%20take%20the%20CSV%20file%2C%20take%20the%20data%20and%20paste%20it%20into%20a%20excel%20template.%20This%20template%20then%20coverts%20the%20data%20according%20to%20the%20upload%20format.%20Then%20save%20this%20template%20file%20as%20a%20txt%20file%2C%20ready%20for%20upload%20into%20bank.%20Any%20suggestions%20%2F%20ideas%20will%20be%20welcomed.%3C%2FP%3E%3CP%3EAttached%20is%20sample%20files%20only%20('20201131.CSV'%20is%20Xero%20output).%26nbsp%3B%20Since%20I%20cannot%20attach%20a%20txt%20file%20(bank%20upload)%2C%20I%20have%20copied%20the%20examples%20as%20below%20%3A%3C%2FP%3E%3CP%3E10%20OCBCSGSGXXX501100100001%20On%20Behalf%20Of%20abcd%20GIROABCF00000000000107082018%3CBR%20%2F%3EOCBCSGSGXXX501111222001%20TINTINSTAR1%2000000000000000020Paymentdetails1%20IVPTM%20ETest%40gmail.com%3CBR%20%2F%3EINV%20Test%20invoice%3CBR%20%2F%3EINV%20Invoice%20Number%3A%2012345%20Item%20Description%20%3A%20Test%20Item%3CBR%20%2F%3EDBSSSGSGXXX029888999002%20TINTINSTAR2%2000000000000000025Paymentdetails1%20M%20ETest%40gmail.com%3CBR%20%2F%3EINV%20Test%20invoice%3CBR%20%2F%3EINV%20Invoice%20Number%3A%2012345%20Item%20Description%20%3A%20Test%20Item%201%3CBR%20%2F%3EINV%20Invoice%20Number%3A%2011122%20Item%20Description%20%3A%20Test%20Item%202%3CBR%20%2F%3EPaynow%20Test%20Bene%201%2000000000000000030Paymentdetails1%20M%20NRIC%20S1111111A%3CBR%20%2F%3EPaynow%20Test%20Bene%202%2000000000000000035Paymentdetails2%20M%20MSISDN%20%2B6591111111%3CBR%20%2F%3EPaynow%20Test%20Bene%203%2000000000000000040Paymentdetails3%20M%20MSISDN%20%2B6592222222%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1935548%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1935934%22%20slang%3D%22de-DE%22%3ESubject%3A%20Xero%20output%20file%20in%20CSV.%20How%20to%20convert%20to%20txt%20file%20for%20upload%20in%20bank%20system%20for%20payment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935934%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883343%22%20target%3D%22_blank%22%3E%40KCkum%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EFished%20fresh%20from%20the%20internet%20here%2C%20as%20there%20is%20no%20time.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EMaybe%20this%20will%20help%20you%20to%20get%20on%20with%20your%20project.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EOption%20Explicit%0A%0ASub%20Convert_CSV_to_XLS%20()%0ADim%20i%20As%20Long%2C%20delayed%20As%20String%0ADim%20file%20Form%20As%20String%0A'With%20a%20backslash%20at%20the%20end%0Adir%20%3D%20%22C%3A%20%5C%20Folder1%20%5C%22%0A'File%20format%0AfileForm%20%3D%20%22csv%22%0AOn%20Error%20GoTo%20error%0AChDrive%20Left%20(galv%2C%202)%0AChDir%20galv%0AWith%20Application.FileSearch%0A%20%20%20%20.NewSearch%0A%20%20%20%20.LookIn%20%3D%20delayed%0A%20%20%20%20.SearchSubFolders%20%3D%20False%0A%20%20%20%20.FileType%20%3D%20msoFileTypeAllFiles%0A%20%20%20%20.Execute%0A%20%20%20%20%20For%20i%20%3D%201%20To%20.FoundFiles.Count%0A%20%20%20%20%20%20%20%20%20%20Application.StatusBar%20%3D%20%22File%22%20%26amp%3B%20i%20%26amp%3B%20%22from%22%20%26amp%3B%20.FoundFiles.Count%20%26amp%3B%20%22is%20being%20processed%22%0A%20%20%20%20%20%20%20%20%20%20If%20Right%20(.FoundFiles%20(i)%2C%203)%20%3D%20fileForm%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Debug.Print%20.FoundFiles%20(i)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Workbooks.Open%20.FoundFiles%20(i)%2C%20local%3A%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ActiveWorkbook.SaveAs%20Left%20(.FoundFiles%20(i)%2C%20Len%20(.FoundFiles%20(i))%20-%203)%20%26amp%3B%20%22xls%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ActiveWorkbook.Close%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20Next%20i%0AEnd%20With%0AError%20Exit%3A%0AApplication.ScreenUpdating%20%3D%20True%0AApplication.StatusBar%20%3D%20False%0AExit%20Sub%0A%0Aerror%3A%0AMsgBox%20Err.Number%20%26amp%3B%20%22%3B%22%20%26amp%3B%20Err.Description%0AResume%20ErrorExit%0AEnd%20Sub%20%5Bcode%5D%20%5B%2F%20code%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1935936%22%20slang%3D%22en-US%22%3ERe%3A%20Xero%20output%20file%20in%20CSV.%20How%20to%20convert%20to%20txt%20file%20for%20upload%20in%20bank%20system%20for%20payment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1935936%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883343%22%20target%3D%22_blank%22%3E%40KCkum%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20reading%20it%20multiple%20times..%20I%20cannot%20understand%20what%20is%20the%20inpus..%20what%20is%20the%20output%3F%3C%2FP%3E%3CP%3Eplease%20spend%20more%20time%20clarifying%20this%20for%20us.%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 @Nikolino  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.