topic Re: Read data excel into internal table in Application Development and Automation Discussions

Read data excel into internal table

Former Member — Wed, 18 Nov 2015 07:34:46 GMT

Hi guys, i have an problem when i read data from excel data using abap editor

here my code.

REPORT ZTEST_UPLOAD NO STANDARD PAGE HEADING.

TYPES: BEGIN OF t_gb,

rec(1000) TYPE c,

END OF t_gb.

TYPES: BEGIN OF t_gb1,

field1(100) TYPE c,

field2(100) TYPE c,

field3(100) TYPE c,

END OF t_gb1.

DATA: BEGIN OF t_gb_excel,

field1(20) TYPE c,

field2(20) TYPE c,

field3(20) TYPE c,

END OF t_gb_excel,

t_tab_excel LIKE TABLE OF t_gb_excel.

DATA: g_repid like sy-repid.

DATA: it_gb TYPE TABLE OF t_gb WITH HEADER LINE,

it_gb1 TYPE TABLE OF t_gb1 WITH HEADER LINE.

DATA: file_str TYPE string.

initialization.

g_repid = sy-repid.

PARAMETERS: p_file TYPE localfile.

AT SELECTION-SCREEN on VALUE-REQUEST FOR p_file.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = g_repid

IMPORTING

FILE_NAME = p_file.

START-OF-SELECTION.

file_str = p_file.

call function 'GUI_UPLOAD'

exporting

filename = file_str

tables

data_tab = it_gb

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

others = 17.

delete it_gb INDEX 1.

LOOP AT it_gb.

CLEAR:it_gb1.

SPLIT it_gb-rec at cl_abap_char_utilities=>horizontal_tab

into it_gb1-field1

it_gb1-field2

it_gb1-field3.

append it_gb1.

ENDLOOP.

DATA: a TYPE string,

b TYPE string,

c TYPE string.

LOOP AT it_gb1.

* REPLACE ALL OCCURRENCES OF ',' in it_gb1 WITH ' '.

t_gb_excel-field1 = it_gb1-field1.

t_gb_excel-field2 = it_gb1-field2.

t_gb_excel-field3 = it_gb1-field3.

append t_gb_excel to t_tab_excel.

ENDLOOP.

LOOP AT t_tab_excel INTO t_gb_excel.

WRITE:/ t_gb_excel-field1, t_gb_excel-field2, t_gb_excel-field3.

ENDLOOP.

But if i just write t_gb_excel-field1 the output will be there

My data in excel like this

No Acc Rcc

1 Afif As

2 Nuzia Sekop

3 Al Asadi Wajik

Can anyone help me?

Re: Read data excel into internal table

former_member190321 — Wed, 18 Nov 2015 07:57:06 GMT

Hi,

Solution : save your excel file as a Text (Tab Delimited) file instead of an csv.

Let me know if this solves your problem

Re: Read data excel into internal table

Former Member — Wed, 18 Nov 2015 08:02:30 GMT

Hi,

it looks like your split doesn't work. Maybe the fields are separated by ',', so try

SPLIT it_gb-rec at ','

into it_gb1-field1

it_gb1-field2

it_gb1-field3.

Regards,

Klaus

Re: Read data excel into internal table

SwadhinGhatuary — Wed, 18 Nov 2015 08:24:08 GMT

Hi,

why dont you use 'ALSM_EXCEL_TO_INTERNAL_TABLE'

sample program http://www.sapdev.co.uk/file/file_upexcelalt2.htm

or as suggested earlier save that excel to tab delimited which will help full to get your desirable output.

Re: Read data excel into internal table

Former Member — Wed, 18 Nov 2015 08:50:27 GMT

Hi tebza, my extensions file already csv

Re: Read data excel into internal table

Former Member — Wed, 18 Nov 2015 08:52:32 GMT

Hi klaus thank you very much

Re: Read data excel into internal table

Former Member — Wed, 18 Nov 2015 10:18:07 GMT

Hi ANAA,

Could you please check it_gb-rec whether it is having comma or tab seperator, because cl_abap_char_utilities=>horizontal_tab represents a tab delimeter. Accordingly you can use the split statement.

Regards,

Surendra Gupta.

Re: Read data excel into internal table

former_member190321 — Wed, 18 Nov 2015 12:27:43 GMT

I know that it is a csv, thats why you are not getting the correct results. i meant to say save the Excel file which you are uploading as a Text (Tab Delimited) instead of a csv file. that would have also solved your problem as this file type was not going to require splitting

Re: Read data excel into internal table

Former Member — Wed, 25 Nov 2015 07:56:20 GMT

Hi ANAA,

Could you please check and close thread with proper comments if issue resolved.