Macro or utility to manipulate a CSV file
- or -
Post a project like this2248
$$
- Posted:
- Proposals: 2
- Remote
- #1877105
- Awarded
Description
Experience Level: Intermediate
Description of excel work: Create an excel macro or simple utility application to input a csv file, manipulate the columns and output a new csv file.
The csv file is from an accounting system and is a batch payment file. The output file is to load into an online banking system to make batch payments. Each line represents a transaction. The 2 formats don't match so we need to have this application to convert the file. The application or macro should be quick and easy for a non-technical user to use. Typically the file will have less than 100 lines so processing speed is not an issue.
Input file format:
The input file has the following format: (Example below)
00.01,01234578901123,Test Company,,TestReference
Column 1 - This is the amount of the payment with 2 decimal places. This field length can vary.
Column 2 - This is the sort code and account number of the receiving bank account. In the UK the first 6 digits will be the sort code and the last 8 digits the account number. It will always be 14 digits. This will need to be split into different fields for the sort code and account number in the output file.
Column 3 - This is the company name of the receiving account
Column 4 - This field is blank and unused
Column 5 - This is the payment reference field.
Output file format
(example below)
012345,Test Company,78901123,00.01,TestReference,99
Column1 - Sort code - Taken from the first 6 digits of Column 2 of the input file.
Column2 - This is the company name of the receiving account
Column 3 of the input file. Column 3 - Account number - This is the last 8 digits of of Column 2 of the input file.
Column 4 - Amount, taken from column 1 of the input file.
Column 5 - Reference taken from Column 5 of the input file.
Column 6 - This is the BACS code and is always the digits "99"
Extra notes:
The csv file is from an accounting system and is a batch payment file. The output file is to load into an online banking system to make batch payments. Each line represents a transaction. The 2 formats don't match so we need to have this application to convert the file. The application or macro should be quick and easy for a non-technical user to use. Typically the file will have less than 100 lines so processing speed is not an issue.
Input file format:
The input file has the following format: (Example below)
00.01,01234578901123,Test Company,,TestReference
Column 1 - This is the amount of the payment with 2 decimal places. This field length can vary.
Column 2 - This is the sort code and account number of the receiving bank account. In the UK the first 6 digits will be the sort code and the last 8 digits the account number. It will always be 14 digits. This will need to be split into different fields for the sort code and account number in the output file.
Column 3 - This is the company name of the receiving account
Column 4 - This field is blank and unused
Column 5 - This is the payment reference field.
Output file format
(example below)
012345,Test Company,78901123,00.01,TestReference,99
Column1 - Sort code - Taken from the first 6 digits of Column 2 of the input file.
Column2 - This is the company name of the receiving account
Column 3 of the input file. Column 3 - Account number - This is the last 8 digits of of Column 2 of the input file.
Column 4 - Amount, taken from column 1 of the input file.
Column 5 - Reference taken from Column 5 of the input file.
Column 6 - This is the BACS code and is always the digits "99"
Extra notes:
Edmund S.
100% (9)Projects Completed
9
Freelancers worked with
8
Projects awarded
50%
Last project
17 Jul 2022
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies