Skip to main content

Notifications

Announcements

No record found.

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

Data Exchange Definition for a Payroll Import file

Posted on by 670

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?

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,647 Super User 2024 Season 2 on at
    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 Profile Picture
    JAngle 33,159 on at
    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 Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    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 Profile Picture
    Dallefeld 11,425 User Group Leader on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans