Small and medium business | Business Central, N...

Data Exchange Definition for a Payroll Import file

(0) Share

Report

Posted on by Nick Webb

690

I have a Payroll file that I wish to import into BC. I'm pretty sure I could fairly quickly write a codeunit in an AL extension to achieve the import I want, but I wondered if BC's standard functionality via Data Exchange Definition, Field Mapping and Transformation Rules would be able to handle this format of file?

I've been following the procedure on the link below, which was very helpful but I haven't managed to import the values I want.

https://robertostefanettinavblog.com/2019/09/24/import-payrolls-in-business-central/

Here's a sample import file:-

Acme Products - Dave - Month 6,,,,,,,,,,
Name,Surname,Gross Pay,Tax,Employee NICs,Employer NICs,Student Loan deduction,Global group - Employee Contribution,Global group - Employer Contribution,Global group - Total Contribution,Take-home pay
,,,,,,,,,,
"Month 6 - Ending 30 September, 2021",,,,,,,,,,
EMP,ONE,"1,026.67",0,0,0,0,0,0,0,"1,026.67"
EMP,TWO,"1,500.00",154,0,22.08,0,0,0,0,"1,346.00"
EMP,THREE,"2,500.00",0,0,0,0,0,0,0,"2,225.00"
TOTAL (3 employees),,"5,026.67",154.00,0.00,22.08,0.00,0.00,0.00,0.00,"4,597.67"

From this file, the only line to be imported is the last line (starting with TOTAL), and for each value on that line we need to create a new record in the Gen. Journal Line (81) table.
So the one input line will create 9 records in BC, each with their particular GL and Bal Account No, Description etc.

Can this be done without a custom import?

All responses (4)

Answers (0)

Suggested answer

YUN ZHU 74,115 Super User 2024 Season 2 on at

Like (0)

Report

RE: Data Exchange Definition for a Payroll Import file

Hi, I agree with Kim, if you want to do this without customization, you need to manually edit your import file before importing to BC.

And as you might know, this seems to be easy to do with XMLPort or Excel Buffer.

Hope this also helps.

Thanks.

ZHU
Suggested answer

JAngle 33,159 on at

Like (0)

Report

RE: Data Exchange Definition for a Payroll Import file

Still feasible with data exchange definitions. You would need to use the target table as an intermediate table. You would require a custom codeunit to do the mapping once all the data is present. If the import file format is liable to change this method has some value. If it isn’t then your standalone custom codeunit is probably a nicer choice. DED’s are brilliant but sometimes constraining
Suggested answer

Inge M. Bruvik 32,748 Super User 2024 Season 1 on at

Like (0)

Report

RE: Data Exchange Definition for a Payroll Import file

Kim is totally correct. You will need a customized import function supporting your file format.
Suggested answer

Dallefeld 11,425 User Group Leader on at

Like (1)

Report

RE: Data Exchange Definition for a Payroll Import file

I believe that data exchange definitions are designed to be one line equals one transaction and that you will need a custom import.