Automating Exporting Invoice to CSV

[richard_thurlow]

Hi,

I have a requirement to automate the process of exporting invoices for certain customers to a CSV.

I was hoping that someone can point me in the right direction as doing this doesn't seem straight forward in the SAP B1 program - nor can I find anything by Googling.

Any help would be much appreciated.

Regards

Rick

[luisdarui]

Hi Rick,

Do you need to export it from the database directly to a CSV or do you want to export data from a SAP Business One program?

In case you need to export it from the SAP BusinessOne I suggest you to use the following forum to post your question:

In case of development on Business One:

In case of general information on Business One:

From a database perspective there are many ways to export information to a CSV file, but I believe that in your case, you're not doing it from the database directly, instead you want to do it from the application. Is this correct?

Regards,

Luis Darui

[richard_thurlow]

Hi Luis,

I was thinking that this would have being done outside of SAP - and directly from the DB.

I have already setup and utilizing SQLCMD to query and export from the OITM tables to a CSV on an periodic automated basis - which I couldn't figure out how to do directly from the SAP B1 application. Thus the reason for my trying to work out how to do this with scripts and using the windows scheduled tasks functions.

Any help would be great - I guess I could create another SQLCMD script, but I would like to learn how to export out a csv which has a header line.

I was thinking maybe the header information could be:

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[DocTotal]- T0.[DiscSum]- T0.[VatSum]-T0.[U_FreightEntry]  as 'ex GST', T0.[DiscSum], T0.[VatSum], T0.[U_FreightEntry], T0.[DocTotal], T0.[PaidToDate], T0.[DocCur] FROM OINV T0 WHERE T0.[CANCELED] = 'n'

Then the lines could be:

SELECT T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[CANCELED] = 'n'

Any help would be appreciated.

Thanks

Rick

[luisdarui]

Hi Rick,

I believe that the challenge is more business related than SQL because it requires knowledge on how to find the Invoice data inside the table and to fit it into the format that you'll export as CSV.

How is your select criteria? Are you going to export 1 document or N documents? Are you considering documents that are cancelled, invalidated, etc?

Are you exporting invoices from one customer from a date/time range? Or all documents?

What's the purpose of the CSV file? This exported document will be imported by another system?

All the questions are more related to the business rules and the purpose of the file.

For a CSV file I don't believe that you can have different data/columns for the same data.

e.g.: the header have different columns than the products/items in the invoices. Is that you want and is it expected? How will the system that will import it read the data from a non-standard CSV file?

As per my understanding, you're putting business rules into DB responsibility. Maybe the best approach would have it working from inside the SAP B1.

Regards,

Luis Darui

[Sriram2009]

Hi Rick

Just check this SCN thread 

Br

SS