topic Re: CSV\EXCEL FILE READING in Application Development and Automation Discussions

CSV\EXCEL FILE READING

Former Member — Mon, 04 Feb 2008 12:58:29 GMT

Hi Guys!!

I have designed an upload program.

How to read excel or CSV File from Desktop.

Thanks,

Anee

Re: CSV\EXCEL FILE READING

Former Member — Mon, 04 Feb 2008 13:07:04 GMT

Just upload the file into a suitable structure using cl_gui_upload and then process as an internal table. There aren't any "canned excel upload file" function modules or classes available unfortunately.

Note you'll have to define the delimiters as something in your structure

such as


begin of upload
            col1   whatever
            csv1(1)   whatever it's hex value is

*etc

Note also that if you use EXCEL 2007 it's actually easier since if you use the new xlsx format this is a compressed (zio) open source xml formatted type file which is far more compatable with other systems (including Open Office) than the older proprietary xls format. (Has Microsoft suddenly realized there ARE other systems and file formats out there !).

For more info on EXCEL file formats suggest you read the following WIKI link

http://en.wikipedia.org/wiki/Microsoft_Excel

Cheers

jimbo

Re: CSV\EXCEL FILE READING

Former Member — Mon, 04 Feb 2008 13:10:28 GMT

hi anee,

try out this function module (SE37):

FILE_READ_AND_CONVERT_SAP_DATA

moreover some methods of this class could be helpful:

CL_GUI_FRONTEND_SERVICES

regards

olaf

Re: CSV\EXCEL FILE READING

Former Member — Mon, 04 Feb 2008 13:16:34 GMT

Hi there

have you ACTUALLY tried this yet.

In theory you can convert an XML file (EXCEL 2007 format) but this certainly won't work for a version of EXCEL prior to 2007 - especially since the.xls format is proprietary.

XLSX/SLSM format possibly could work but other formats certainly won't.

The only chance you really have is to convert the speradsheet to a text csv delimited file first and then do the SAP upload.

Cheers

jimbo

Re: CSV\EXCEL FILE READING

Former Member — Mon, 04 Feb 2008 13:48:30 GMT

hi again,

my understanding of anees question was that CSV fit's the needs but

anee don't know how to get the content of the file to the ABAP program.

to solve this problem

FILE_READ_AND_CONVERT_SAP_DATA

should work fine.

regards

olaf

Re: CSV\EXCEL FILE READING

uwe_schieferstein — Mon, 04 Feb 2008 19:35:59 GMT

Hello Anee

A useful function module for reading .csv files is TEXT_CONVERT_CSV_TO_SAP. The function group containing this fm contains several other function modules which are quite useful.

Regards

Uwe

Re: CSV\EXCEL FILE READING

Former Member — Tue, 05 Feb 2008 10:31:06 GMT

Hi ,

Please have a look at the report may Help.

REPORT zibm_u801m_10_ziscust.

DATA :it_ziscust LIKE ziscust OCCURS 0 WITH HEADER LINE ,

itd_excel TYPE TABLE OF alsmex_tabline WITH HEADER LINE.

DATA: BEGIN OF itd_u801m_10 OCCURS 0 ,

zicustnr LIKE ziscust-zicustnr,

zisourcekey LIKE ziscust-zisourcekey,

END OF itd_u801m_10.

START-OF-SELECTION.

PERFORM upload_excel_file.

PERFORM read_excel_to_internal_table.

PERFORM read_data_source.

PERFORM modify_data_source_commit.

END-OF-SELECTION.

&----

*& Form upload_excel_file

&----

text

----

--> p1 text
<-- p2 text

----

FORM upload_excel_file .

REFRESH : itd_excel.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = 'H:\U801M_10.xls'

i_begin_col = '1'

i_begin_row = '2'

i_end_col = '3'

i_end_row = '65536'

TABLES

intern = itd_excel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE e398(00) WITH 'EXcel File' 'U801M_10.xls' 'could not be uploaded'.

ENDIF.

ENDFORM. " upload_excel_file

&----

*& Form read_excel_to_internal_table

&----

text

----

--> p1 text
<-- p2 text

----

FORM read_excel_to_internal_table .

LOOP AT itd_excel.

IF itd_excel-col = '0001'.

itd_u801m_10-zicustnr = itd_excel-value.

ENDIF.

IF itd_excel-col = '0002'.

itd_u801m_10-zisourcekey = itd_excel-value.

ENDIF.

AT END OF row.

APPEND itd_u801m_10.

ENDAT.

CLEAR itd_excel.

ENDLOOP.

ENDFORM. " read_excel_to_internal_table

&----

*& Form read_data_source

&----

text

----

--> p1 text
<-- p2 text

----

FORM read_data_source .

Data selection from the database

SELECT * FROM ziscust

INTO CORRESPONDING FIELDS OF TABLE it_ziscust

FOR ALL ENTRIES IN itd_u801m_10

WHERE zicustnr EQ itd_u801m_10-zicustnr.

Check for Existence of customer

LOOP AT it_ziscust.

READ TABLE itd_u801m_10 WITH KEY zicustnr = it_ziscust-zicustnr.

IF sy-subrc EQ 0.

it_ziscust-zisourcekey = itd_u801m_10-zisourcekey.

ENDIF.

MODIFY it_ziscust TRANSPORTING zisourcekey.

ENDLOOP.

ENDFORM. " read_data_source

&----

*& Form modify_data_source_commit

&----

text

----

--> p1 text
<-- p2 text

----

FORM modify_data_source_commit .

LOOP AT it_ziscust.

MODIFY ziscust FROM it_ziscust.

CLEAR it_ziscust.

ENDLOOP.

IF sy-subrc EQ 0.

COMMIT WORK.

ELSE.

ROLLBACK WORK.

ENDIF.

ENDFORM. " modify_data_source_commit

Regards

Bala.M

Re: CSV\EXCEL FILE READING

Former Member — Thu, 24 Jul 2008 12:11:39 GMT

just a litlle example using the FMs TEXT_CONVERT_CSV_TO_SAP and FILE_READ_AND_CONVERT_SAP_DATA

*&----

*& Report ZTT_CSV_TO_SAP_TEST

*&----

REPORT ztt_csv_to_sap_test .

TYPES: truxs_server TYPE trux_servertyp.

File formats:
ASC = Flat ASCII
CSV = Comma Separated Values
TXT = TXT Format (Notepad)
HTM = Hypertext Markup Language
HTML = Hypertext Markup Language

TYPES: truxs_fileformat TYPE trtm_format.

TYPES truxs_t_text_data(4096) TYPE c OCCURS 0.

Global data definition

DATA:

l_fileform_rep TYPE trtm_format_seldynpro,

l_fileserv_rep TYPE trux_servertyp_seldynpro,

l_servertyp TYPE truxs_server VALUE 'PRS',

l_out_servertyp TYPE truxs_server VALUE 'APP',

l_fileformat TYPE truxs_fileformat VALUE 'CSV',

l_repid TYPE sy-repid,

l_field_seperator TYPE c,

l_filename TYPE string.

DATA: i_tab_fdes LIKE fdes_import OCCURS 1.

DATA: work(4096) TYPE c.

DATA: i_work LIKE TABLE OF work.

DATA: BEGIN OF w_input,

lifnr LIKE lfa1-lifnr,

name1 LIKE lfa1-name1,

bankl LIKE lfbk-bankl,

banka LIKE bnka-banka,

bankn LIKE lfbk-bankn,

iban LIKE tiban-iban,

zahl LIKE bseg-wrbtr.

DATA END OF w_input.

DATA: i_input LIKE w_input OCCURS 1.

SELECTION-SCREEN BEGIN OF BLOCK in1 WITH FRAME TITLE text-in1.

PARAMETERS : p_file LIKE filename-fileextern DEFAULT

*PARAMETERS : p_file TYPE string DEFAULT

'c: empIBAN_Codes.csv',

p_header AS CHECKBOX.

SELECTION-SCREEN END OF BLOCK in1.

START-OF-SELECTION.

*----

die erste Möglichkeit mit FILE_READ_AND_CONVERT_SAP_DATA

*----

l_field_seperator = ';'.

CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'

EXPORTING

i_filename = p_file

i_servertyp = l_servertyp

i_fileformat = l_fileformat

i_field_seperator = l_field_seperator

i_line_header = p_header

TABLES

i_tab_receiver = i_input

EXCEPTIONS

file_not_found = 1

close_failed = 2

authorization_failed = 3

open_failed = 4

conversion_failed = 5

OTHERS = 6.

LOOP AT i_input INTO w_input.

WRITE: / w_input-lifnr,

w_input-name1,

w_input-bankl,

w_input-banka(25),

w_input-bankn,

w_input-iban,

w_input-zahl.

ENDLOOP.

*----

die zweite Möglichkeit mit TEXT_CONVERT_CSV_TO_SAP

*----

REFRESH i_input. CLEAR i_input.

l_filename = p_file.

CALL METHOD cl_gui_frontend_services=>gui_upload

EXPORTING

filename = l_filename

filetype = 'ASC'

has_field_separator = SPACE
header_length = 0
read_by_line = 'X'
dat_mode = SPACE
codepage = SPACE
ignore_cerr = ABAP_TRUE
replacement = '#'
virus_scan_profile =
IMPORTING
filelength =
header =

CHANGING

data_tab = i_work

EXCEPTIONS

file_open_error = 1

file_read_error = 2

no_batch = 3

gui_refuse_filetransfer = 4

invalid_type = 5

no_authority = 6

unknown_error = 7

bad_data_format = 8

header_not_allowed = 9

separator_not_allowed = 10

header_too_long = 11

unknown_dp_error = 12

access_denied = 13

dp_out_of_memory = 14

disk_full = 15

dp_timeout = 16

not_supported_by_gui = 17

error_no_gui = 18

OTHERS = 19

CALL FUNCTION 'TEXT_CONVERT_CSV_TO_SAP'

EXPORTING

i_field_seperator = ';'

I_LINE_HEADER =

i_tab_raw_data = i_work

I_FILENAME = p_file

TABLES

i_tab_converted_data = i_input

EXCEPTIONS

conversion_failed = 1

OTHERS = 2

WRITE: / '----

LOOP AT i_input INTO w_input.

WRITE: / w_input-lifnr,

w_input-name1,

w_input-bankl,

w_input-banka(25),

w_input-bankn,

w_input-iban,

w_input-zahl.

ENDLOOP.