How to Import AR Invoices from Excel using SDK

[rajesh_khater]

Hi,

I am using B1 version 9.1

I want to import multiple AR Invoices from a single Excel file, using SDK or Integration API. I do not want to use the DTW templates, as they contain a lot of fields; and moreover, for a single invoice, we have to fill data in a lot of Excel files.

I am looking for some helpful documents and examples in SDK or Integration API using which I can import the AR Invoice and create the associated JE too.

The admin department collects details of all the reimbursement expenses at month end, and prepares an Excel file, which needs to be imported into SAP.

Example:

Invoice No.    Posting Date    Due Date    Document Date    BP Code    Invoice Type    Invoice Amt    GL Code    GL Name    Tax Code    Tax Amt    GL Amt (LC)

1    15/10/2014    15/10/2014    15/10/2014    C000001    Service    1500    625100    Travel Reimbursement    NoTax    0    1000

1    15/10/2014    15/10/2014    15/10/2014    C000001    Service    1500    625200    Food Expenses Reimbursement    NoTax    0    500

2    16/10/2014    16/10/2014    16/10/2014    C000002    Service    2000    625100    Travel Reimbursement    NoTax    0    1300

2    16/10/2014    16/10/2014    16/10/2014    C000002    Service    2000    625200    Food Expenses Reimbursement    NoTax    0    400

2    16/10/2014    16/10/2014    16/10/2014    C000002    Service    2000    625300    Daily Allowance    NoTax    0    300

Notice that Invoice No. 1 is spread across 2 rows, and Invoice No. 2 is spread across next 3 rows. The Document level fields are repeated in each row.

This single Excel file contains all required data for 2 invoices, including document level and line item level data.

I do not want to use multiple Excel files for the different database tables involved in an AR Invoice transaction, as that will be very inconvenient for the end user.

A step by step code example will be helpful.

Thanks.

[Former Member]

Hi,

If you are using SDK it depends on how you will program it. If I were to program it I will use the 1st sheet as header and 2nd and 3rd sheet and so on as my data row and name the sheet as the primary key base on the data in 1st sheet. I will then have my own template.

Jim

[rajesh_khater]

Please point me to some helpful documents and examples in SDK or Integration API using which I can import the AR Invoice and create the associated JE too.

I will prefer it to be a single Excel sheet in a single Excel file, as the customer will find it very inconvenient to fill multiple sheets.

[Former Member]

Hi Rajesh,

JE will be generated by DI, so you don't have to take care about it.

As stated by Jim, the format of the Excel document will depend on the available data, the library you are using to read the document, the language you are using... No dedicated format exists and nothing behing format for DTW is provided by SAP.

Having said this, DTW documents can be simplified: you are not mandatory to keep all columns (what i'm doing with consultants here: I just remove all colums for which Customer is not providing data) and DTW can be automatized which can avoid you to write a full program to do the same...

Regards,

Eric

[rajesh_khater]

Hi Eric,

I have already given the format of the Excel document in my question.

I will use C# to read the Excel file. I know how to do this part.

What I do not know is how can I use the API to push the data to SAP.

I am looking for:

1. Documents on using the DI API or any other API using which I can push data to SAP

2. Example Code in C# with hardcoded values, which demonstrates how to create AR Invoice (Service Type, not Item Type) in SAP.

Also if you can provide links on DTW automation, that will be helpful. But I do not want to deviate from the Excel format which I have already stated in my question. In particular, I do not want multiple Excel file or multiple Excel sheets.

Hope I am clear.

Thanks.

[Former Member]

http://scn.sap.com/community/business-one-sdk/blog/2014/09/04/various-sdk-testsHi Rajesh,

I'm not locked to one solution, I'm more open-minded than this

Look at http://scn.sap.com/community/business-one-sdk/blog/2014/09/04/various-sdk-tests which is a blog post I am maintaining with answer on questions posed here for which I am chosing to write tests samples: you'll find Inside how to post an invoice.

Basically, to automate DTW, you have to do the full process once, and before executing it to save the xml configuration file, then to call DTW with this file as a parameter. As far as I remind, this is fully explained in the HLP.

Regards,

Eric

[rajesh_khater]

With which Visual Studio version will the examples open?

[Former Member]

I am using VS2013, but you can open the .cs file with any version.

Regards,

Eric

[rajesh_khater]

I have Visual Studio 2005 and 2008.

Ya, the .cs file will open. But is there any way of converting the project and solution file to open in 2005 or 2008, so that I can run it straight away?

Otherwise if I create a new project in 2005 or 2008, which references should I add to make it run?

Sorry, but I am new to SAP SDK.

[Former Member]

This has nothing to do with SDK, this time this is Microsoft related.

To make the answer short, I would just create an empty project (console application) and just reference the .CS file and the 2 COM objects (UI and DI).

Should be enought.

Catch me me on Skype (LapougeEric) if you have any difficulties, we will not pollute the thread with this.

Regards,

Eric

[Former Member]

Hi Rejesh,

When you install the SDK there are vb.net and c# sample in SAP SDK installation folder.

Regards,

Jim