Create .dat file from excel data for payment upload

[sailingmike49]

I am trying to get an excel sheet of payment data for a club into the database and need to use a macro to create the .dat output for the database upload.
The .dat output has a header like this below:

REQUIRED OUTPUT TO .dat file via macro
Header
01HSPAY001PAY		if Debit amount - show total	If credit amount show total	sum of total receipts(cedit minus debit)

Then the body would have rows below like this - there could be up to a hundred rows

02HSPAY0012024987651		PAYCARD	120.00	DB02/03/2023
This is where all rows below header have the "02" preceding the HSPAY001 batch name concatenated with Receipt_No		Payment credit and payment method are concatenated	Amounts would display here "-" for a credit	CONCATENATED Posted by and uploaded date

 

[sailingmike49]

better view of the fields in the excel sheet