How to read the excel data from ftp through abap P...

krishna_k19 · ‎05-29-2013

Dear All,

How to read the excel data which is present in FTP server through ABAP program. I followed few methods but its not happening pls help me.

Here i'm putting the data what i followed.

1) using FM "FTP_SERVER_TO_R3" passing data and file name i'm passing as aba.xls , then it is showing in alpha-numeric characters (Ï#à¡±#á################>###þÿ##################,#########) if the table format is text and if change the table format also coming same.

2) If suppose i'm downloading the data from SAP byusing the below structure

Ex :

BEGIN OF ST_CUS,

LEDGERKEY(50) TYPE C, "LedgerKey

WRK_DELIM1 TYPE X ,

NAME1(80) TYPE C, "AccountName

WRK_DELIM2 TYPE X ,

KUNNR(50) TYPE C, "AccountReference

WRK_DELIM3 TYPE X ,

end of xxx.

and same data i'm uploading that time it is taking. but if i'm giving external excel file then why its not taking.

which file format it will support. i'm not finding the documentation of the FM & definition of method also.

Regards,

Krishna

marc_augustin2 · ‎05-29-2013

Hi Krishna,

you can use the function module "ALSM_EXCEL_TO_INTERNAL_TABLE" to directly read Excel data into SAP. As this module needs a filename, I don't think you'll be able to get the data via FTP here.

Also, the FM uses OLE objects to upload and convert the data into an internal table and therefore can only be executed online, background processing here is not possible.

You could save the file as tab seperated text file or csv file and load this into your SAP system. That would be my suggestion here.

Cheers,

Marc

krishna_k19 · ‎05-29-2013

Hi Marc,

Thanks for quick reply..

but the FM "ALSM_EXCEL_TO_INTERNAL_TABLE" is for the file which is present in local system , in my case the file is present in FTP so i need ftp way only. and i need which are supports background only like function module no need of manual interaction. incase of OLE manual interaction is required i think so.

pls let me know if is any other way.

Regards,

Krishna

varun_maharshi · ‎05-29-2013

I haven't heard about installing the OLE on server before. http://help.sap.com/saphelp_47x200/helpdata/en/59/ae3d32488f11d189490000e829fbbd/frameset.htm

http://help.sap.com/saphelp_47x200/helpdata/en/59/ae3d32488f11d189490000e829fbbd/frameset.htm. If you have any documentation please let us know.

Also, there is a project in SAP code exchange to read excel files to ABAP. http://scn.sap.com/community/abap/blog/2010/09/06/xlsx2abap--read-and-edit-your-excel-files-from-aba... It might be worth a try.

marc_augustin2 · ‎05-29-2013

Hi Varin,

thanks for the project. I only knew about the other way round, abap2xlsx, which is working really good. Used it for background processing.

As for native solutions, I don't know of any possibility to read Excel in standard SAP in background.

Regards,

Marc

krishna_k19 · ‎05-29-2013

Hi Varun,

I appreciate your reply .

I think OLE is pick the data from Application server , but in my case i need to take the data from FTP .

It is supporting few formats(i'm reading the data if the file is flat file which is present in FTP Server not Application Server).

but coming to excel uploading that time the characters which is present in excel file are appearing in different format.

so i want to confirm whether reading the data from FTP excel reading will support or not.

Regards,

Krishna

raymond_giuseppi · ‎05-29-2013

FTP only transfer files, it will not convert any proprietary format as Excel (even if now it is some xml in zipped folder but that don't solve the problem)

Only Excel (on a GUI workstation, "presentation server", via OLE) and tools like ABAPXLSX (work in background with recent versions of Excel, xlsx not old xls) could interpret the data.

Regards,
Raymond

krishna_k19 · ‎05-29-2013

Hi Raymond,

Thanks for your valuable suggestions..

but i'm reading the data which is present in ftp by using the FM "FTP_SERVER_TO_R3" i'm able to read the notepad data(the table structure is Text) , so i'm asking is there any structure to read the excel file .

Here i'm giving sample code pls check. with this code i'm downloading & uploading into sap..

TYPES: BEGIN OF ST_CUSTOMER,

LEDGERKEY TYPE CHAR50,

NAME1 TYPE KNA1-NAME1,

KUNNR TYPE KNA1-KUNNR,

NAME2 TYPE KNA1-NAME2,

STRAS TYPE KNA1-STRAS,

ORT01 TYPE KNA1-ORT01,

REGIO TYPE KNA1-REGIO,

PSTLZ TYPE KNA1-PSTLZ,

LAND1 TYPE KNA1-LAND1,

WAERS TYPE KNVV-WAERS,

TELF1 TYPE KNA1-TELF1,

END OF ST_CUSTOMER.

TYPES : BEGIN OF ST_CUS,

LEDGERKEY(50) TYPE C, "LedgerKey

WRK_DELIM1 TYPE X ,

NAME1(80) TYPE C, "AccountName

WRK_DELIM2 TYPE X ,

KUNNR(50) TYPE C, "AccountReference

WRK_DELIM3 TYPE X ,

NAME2(80) TYPE C, "Address1

WRK_DELIM4 TYPE X ,

STRAS(80) TYPE C, " Address2

WRK_DELIM5 TYPE X ,

ORT01(50) TYPE C, " Town

WRK_DELIM6 TYPE X ,

REGIO(50) TYPE C, " County / State

WRK_DELIM7 TYPE X ,

PSTLZ(10) TYPE C, "Post Code / ZIP

WRK_DELIM8 TYPE X ,

LAND1(2) TYPE C, "Country

WRK_DELIM9 TYPE X ,

WAERS(3) TYPE C, "AccountCurrencyCode

WRK_DELIM10 TYPE X ,

TELF1(20) TYPE C, "ContactTelephone

END OF ST_CUS .

DATA: WRK_FILE TYPE CHAR200 VALUE 'it-team/ecomm/book1234'.

DATA: IT_CUSTOMER TYPE STANDARD TABLE OF ST_CUSTOMER,

WA_CUSTOMER LIKE LINE OF IT_CUSTOMER.

DATA: IT_DAT TYPE STANDARD TABLE OF ST_CUS,

WA_DAT LIKE LINE OF IT_DAT.

SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS:CUST_NO FOR KNA1-KUNNR,"customer no

COM_CODE FOR KNB1-BUKRS,"company code

COUNTRY FOR KNA1-LAND1, "country

CREAT_ON FOR KNA1-ERDAT DEFAULT SY-DATUM. "created or changed on

SELECTION-SCREEN END OF BLOCK B2.

DATA LV_BLOB_LENGTH TYPE I VALUE '16384'.

START-OF-SELECTION.

IF CREAT_ON-HIGH IS INITIAL.

CREAT_ON-HIGH = CREAT_ON-LOW.

ENDIF.

* CONCATENATE sy-mandt '_1_' creat_on-low '_' creat_on-high '_Cust12.csv' INTO

* wrk_file.

PERFORM CUSTOMER_DATA_SELECT. "Customer Data File

PERFORM BUILD_TEMPLATE_DATA.

PERFORM FTP_FILE_CUSTOMER.

*&---------------------------------------------------------------------*

*& Form CUSTOMER_DATA_SELECT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* --> p1 text

* <-- p2 text

*----------------------------------------------------------------------*

FORM CUSTOMER_DATA_SELECT .

SELECT A~NAME1 A~NAME2 A~KUNNR A~STRAS A~ORT01 A~REGIO A~PSTLZ A~LAND1 A~TELF1

"Details of the Customer based on KNA1.

FROM KNA1 AS A

INNER JOIN KNB1 AS B ON A~KUNNR = B~KUNNR

INTO CORRESPONDING FIELDS OF TABLE IT_CUSTOMER

WHERE A~KUNNR IN CUST_NO

AND ( ( A~ERDAT IN CREAT_ON OR A~UPDAT IN CREAT_ON ) OR

( B~ERDAT IN CREAT_ON OR B~UPDAT IN CREAT_ON ) )

AND A~LAND1 IN COUNTRY

AND B~BUKRS IN COM_CODE.

IF SY-SUBRC = 0.

LOOP AT IT_CUSTOMER INTO WA_CUSTOMER.

SELECT SINGLE WAERS FROM KNVV INTO WA_CUSTOMER-WAERS

WHERE KUNNR = WA_CUSTOMER-KUNNR..

WA_CUSTOMER-LEDGERKEY = '12345'.

MODIFY IT_CUSTOMER FROM WA_CUSTOMER.

ENDLOOP.

ENDIF.

ENDFORM. " CUSTOMER_DATA_SELECT

*&---------------------------------------------------------------------*

*& Form BUILD_TEMPLATE_DATA

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* --> p1 text

* <-- p2 text

*----------------------------------------------------------------------*

FORM BUILD_TEMPLATE_DATA .

LOOP AT IT_CUSTOMER INTO WA_CUSTOMER.

MOVE-CORRESPONDING WA_CUSTOMER TO WA_DAT.

WA_DAT-WRK_DELIM1 = '09'. "for adding tab after each field

WA_DAT-WRK_DELIM2 = '09'.

WA_DAT-WRK_DELIM3 = '09'.

WA_DAT-WRK_DELIM4 = '09'.

WA_DAT-WRK_DELIM5 = '09'.

WA_DAT-WRK_DELIM6 = '09'.

WA_DAT-WRK_DELIM7 = '09'.

WA_DAT-WRK_DELIM8 = '09'.

WA_DAT-WRK_DELIM9 = '09'.

WA_DAT-WRK_DELIM10 = '09'.

APPEND WA_DAT TO IT_DAT.

ENDLOOP.

ENDFORM. " BUILD_TEMPLATE_DATA

*&---------------------------------------------------------------------*

*& Form FTP_FILE_CUSTOMER

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* --> p1 text

* <-- p2 text

*----------------------------------------------------------------------*

FORM FTP_FILE_CUSTOMER .

DATA: L_USER(30) TYPE C VALUE "fdjh', "user name of ftp server

L_PWD(30) TYPE C VALUE 'abc', "password of ftp server

L_HOST(64) TYPE C VALUE '1xx.aa.34.2s', "ip address of FTP server

L_DEST LIKE RFCDES-RFCDEST VALUE 'SAPFTPA'."Background RFC destination

DATA: W_HDL TYPE I,

C_KEY TYPE I VALUE 26101957,

L_SLEN TYPE I.

*HTTP_SCRAMBLE: used to scramble the password provided in a format recognized by SAP.

SET EXTENDED CHECK OFF.

L_SLEN = STRLEN( L_PWD ).

CALL FUNCTION 'HTTP_SCRAMBLE'

EXPORTING

SOURCE = L_PWD

SOURCELEN = L_SLEN

KEY = C_KEY

IMPORTING

DESTINATION = L_PWD.

* To Connect to the Server using FTP

CALL FUNCTION 'FTP_CONNECT'

EXPORTING

USER = L_USER

PASSWORD = L_PWD

HOST = L_HOST

RFC_DESTINATION = L_DEST

IMPORTING

HANDLE = W_HDL

EXCEPTIONS

OTHERS = 1. .

*FTP_R3_TO_SERVER:used to transfer the internal table data as a file to other system in the character mode.

CALL FUNCTION 'FTP_R3_TO_SERVER'

EXPORTING

HANDLE = W_HDL

FNAME = WRK_FILE "file path of destination system

* blob_length = lv_blob_length

CHARACTER_MODE = 'X'

TABLES

* blob = it_dat "lt_outtab

TEXT = IT_DAT

EXCEPTIONS

TCPIP_ERROR = 1

COMMAND_ERROR = 2

DATA_ERROR = 3

OTHERS = 4.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4

RAISING INVALID_OUTPUT_FILE.

ENDIF.

*FTP_DISCONNECT: This is used to disconnect the connection between SAP and other system.

* To disconnect the FTP

CALL FUNCTION 'FTP_DISCONNECT'

EXPORTING

HANDLE = W_HDL.

*RFC_CONNECTION_CLOSE:This is used to disconnect the RFC connection between SAP and other system.

CALL FUNCTION 'RFC_CONNECTION_CLOSE'

EXPORTING

DESTINATION = L_DEST

EXCEPTIONS

OTHERS = 1.

ENDFORM. " FTP_FILE_CUSTOMER

byusing this code we can download & uploading .

Can you please provide some code which is using OLE ..

pls correct me if i wrong..

pls give some suggestions..

Regards,

Krishna

marc_augustin2 · ‎05-29-2013

Hi Krishna,

there is no structure or standard way to read Excel files within SAP. The only possible solutions, in my opinion, are to have the file converted into a text file on FTP side or to use xlsx and then use the XLSX2ABAP classes provided in the link by Varun.

Regards,

Marc

How to read the excel data from ftp through abap Program