REg Upload of data through Flat file ..... .XLS or .TXT or .CSV

[prasanth_kasturi]

Hi,

I am trying to upload flat file data using the FM GUI_UPLOAD for .TXT file and

FM TEXT_CONVERT_XLS_TO_SAP for .Xls and .csv files

My internal table is as follows

TYPES : BEGIN OF ty_f64,
                 field(255) type c,
              END OF ty_f64.

data : it_f64 type table of ty_f64.

Example Flat file data is as follows

H	20112008	DR	0001	20112008	INR
L	01	61	1000		
L	50	41000	1000		
H	20112008	DR	0001	20112008	INR
L	01	61	1000		
L	50	41000	500		
L	50	41000	400		
L	50	41000	100		

My problem is that only the first field i.e H or L is getting updated into my internal table but I want whole record to be the content in one row

i.e in RUN TIME I see the internal table data as

H

L

L

H

L

L

L

regards

Prasanth

[Former Member]

hi,

While uploading data using GUI_upload use file type as 'ASC'....in case of the XLS use filetype as 'DAT'.

[prasanth_kasturi]

Hi,

In GUI_UPLOAD its ASC

and for Xls file there is no such field

check here

  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename                      = filename
      has_field_separator           = 'X'
      filetype                      = 'ASC'
    TABLES
      data_tab                      = it_f64
  .
  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR          = ''
*     I_LINE_HEADER              = ''
      i_tab_raw_data             = raw_data
      i_filename                 = p_file
    TABLES
      i_tab_converted_data       = it_f64
*   EXCEPTIONS
*     CONVERSION_FAILED          = 1
*     OTHERS                     = 2
            .

Guys need your help

regards

Prasanth

[Former Member]

Try to pass HAS_FIELD_SEPARATOR = SPACE. (Tab Delimited file)

Thanks,

Jayant

[prasanth_kasturi]

Hi,

when I remove X from the field seperator I am gettting the values as

1 H#20112008#DR#0001#20112008#INR

2 L#01#61#1000##

3 L#50#41000#1000##

4 H#20112008#DR#0001#20112008#INR

5 L#01#61#1000##

6 L#50#41000#500##

7 L#50#41000#400##

8 L#50#41000#100##

But I could get the data seperated into based on H and L into 2 internal tables

I tried using split at # and also offset as you can see in my code

check my code

  LOOP AT it_f64 INTO wa_f64.

    IF wa_f64+0(1) = 'H'.

      wa_f64+0(1)  =  wa_header-indicator.
      wa_f64+1(10) =  wa_header-bldat.
      wa_f64+11(2) =  wa_header-blart.
      wa_f64+13(4) =  wa_header-bukrs.
      wa_f64+17(10) = wa_header-budat.
      wa_f64+27(5) = wa_header-waers.

*      SPLIT wa_f64 AT v_delimiter INTO :wa_header-indicator
*                                       wa_header-bldat
*                                       wa_header-blart
*                                       wa_header-bukrs
*                                       wa_header-budat
*                                       wa_header-waers.

      APPEND wa_header TO it_header.
      CLEAR wa_header.

    ELSEIF wa_f64+0(1) = 'L'.

      SPLIT wa_f64 AT v_delimiter INTO : wa_item-indicator
                                        wa_item-newbs
                                        wa_item-newko
                                        wa_item-wrbtr.
      APPEND wa_item TO it_item.
      CLEAR wa_item.

    ENDIF.

[Former Member]

Yes, you can either get the data in you internal table right by using the split statement or the offset.

Has worked for you. (You are getting the data in your internal table now).

Cheers,

Jayant.

[Former Member]

Hi,

Use ALSM_EXCEL_TO_INTERNAL_TABLE.

It will upload data from both XLS and CSV file.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_filenm

i_begin_col = '1'

i_begin_row = '2' "Do not require headings

i_end_col = '14'

i_end_row = lineno

TABLES

intern = itab

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE e010(zz) WITH text-001. "Problem uploading Excel Spreadsheet

ENDIF.

• Sort table by rows and colums

*SORT itab BY row col.

• Get first row retrieved

READ TABLE itab INDEX 1.

• Set first row retrieved to current row

gd_currentrow = itab-row.

LOOP AT itab.

• Reset values for next row

IF itab-row NE gd_currentrow.

APPEND wa_record TO it_record.

CLEAR wa_record.

gd_currentrow = itab-row.

ENDIF.

CASE itab-col.

WHEN '0001'. "First name

wa_record-ecode = itab-value.

WHEN '0002'. "Surname

wa_record-edate = itab-value.

WHEN '0005'. "Age

wa_record-eshift = itab-value.

WHEN '0006'.

wa_record-earrtim = itab-value.

WHEN '0011'.

wa_record-edeptim = itab-value.

WHEN '0013'.

wa_record-ewrkhrs = itab-value.

ENDCASE.

• append wa_record to wa_it.

ENDLOOP.

APPEND wa_record TO it_record.

CLEAR wa_record.

LOOP AT it_record INTO wa_record.

wa_it-ecode = wa_record-ecode.

wa_it-edate = wa_record-edate.

wa_it-eshift = wa_record-eshift.

wa_it-earrtim = wa_record-earrtim.

wa_it-edeptim = wa_record-edeptim.

wa_it-ewrkhrs = wa_record-ewrkhrs.

APPEND wa_it TO it.

CLEAR wa_it.

ENDLOOP.

Hope magic Works!

Regards,

Purvesh Patel.

[prasanth_kasturi]

purvesh patel

Hi I know abt that Fm but the problem is that It will become tedious for me

as It requires a seperate internal table.

with that I have to get the recotds in one internal table and then again seperate into header and line items into 2 seperate internal table

so I planned to this Fm

Jayanth.....

Hi I am getting the data But I could n't use it

and I couldnt upload Xcel file also

Please try why the Split statemnt is not working

I need to use split because the internal Table for ex 2nd record the value is 1000##

the value may be 100000 or anything so I must make my offset generic

if use the following way

wa_item-wrbtr = wa_f64+8(4).

Hash(#) is getting into the internal table

regards

Prasanth

Edited by: Prasanth Kasturi on Nov 20, 2008 2:01 PM

[Former Member]

check the below code

REPORT ZSRK_073 .

CLASS CL_ABAP_CHAR_UTILITIES DEFINITION LOAD.

TYPES : BEGIN OF TY_F64,

FIELD(255) TYPE C,

END OF TY_F64.

DATA : IT_F64 TYPE TABLE OF TY_F64,

WA_F64 TYPE TY_F64.

TYPES : BEGIN OF TY_HEADER,

INDICATOR,

BLDAT TYPE BLDAT,

BLART TYPE BLART,

BUKRS TYPE BUKRS,

BUDAT TYPE BUDAT,

WAERS TYPE WAERS,

END OF TY_HEADER.

TYPES : BEGIN OF TY_ITEM,

INDICATOR,

NEWBS TYPE NEWBS,

NEWKO TYPE NEWKO,

WRBTR(16), " TYPE WRBTR,

END OF TY_ITEM.

DATA : IT_HEADER TYPE TABLE OF TY_HEADER ,

IT_ITEM TYPE TABLE OF TY_ITEM,

WA_HEADER TYPE TY_HEADER,

WA_ITEM TYPE TY_ITEM.

CONSTANTS : C_HTAB TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'C:\TTT1.txt'

FILETYPE = 'ASC'

TABLES

DATA_TAB = IT_F64.

LOOP AT IT_F64 INTO WA_F64.

IF WA_F64+0(1) = 'H'.

SPLIT WA_F64 AT C_HTAB INTO WA_HEADER-INDICATOR

WA_HEADER-BLDAT

WA_HEADER-BLART

WA_HEADER-BUKRS

WA_HEADER-BUDAT

WA_HEADER-WAERS.

APPEND WA_HEADER TO IT_HEADER.

CLEAR WA_HEADER.

ELSEIF WA_F64+0(1) = 'L'.

SPLIT WA_F64 AT C_HTAB INTO : WA_ITEM-INDICATOR

WA_ITEM-NEWBS

WA_ITEM-NEWKO

WA_ITEM-WRBTR.

APPEND WA_ITEM TO IT_ITEM.

CLEAR WA_ITEM.

ENDIF.

ENDLOOP.

[prasanth_kasturi]

Hi,

sreekanth thanks for the message

My split statemnt worked.

But I couldnt get through with the .XLS and .CSV

anyone help how to get through with the EXCEL file

regards

Prasanth

Edited by: Prasanth Kasturi on Nov 21, 2008 5:51 AM

[Former Member]

Check the below code

REPORT ZSRK_073 .

CLASS CL_ABAP_CHAR_UTILITIES DEFINITION LOAD.

TYPES : BEGIN OF TY_F64,

FIELD(255) TYPE C,

END OF TY_F64.

DATA : IT_F64 TYPE TABLE OF TY_F64,

WA_F64 TYPE TY_F64.

TYPES : BEGIN OF TY_HEADER,

RECNO TYPE I,

INDICATOR,

BLDAT TYPE BLDAT,

BLART TYPE BLART,

BUKRS TYPE BUKRS,

BUDAT TYPE BUDAT,

WAERS TYPE WAERS,

END OF TY_HEADER.

TYPES : BEGIN OF TY_ITEM,

RECNO TYPE I,

INDICATOR,

NEWBS TYPE NEWBS,

NEWKO TYPE NEWKO,

WRBTR(16), " TYPE WRBTR,

END OF TY_ITEM.

TYPES : BEGIN OF TY_TAB,

F1,

F2(10),

F3(17),

F4(17),

F5(10),

F6(5),

END OF TY_TAB.

DATA : IT_HEADER TYPE TABLE OF TY_HEADER ,

IT_ITEM TYPE TABLE OF TY_ITEM,

WA_HEADER TYPE TY_HEADER,

WA_ITEM TYPE TY_ITEM,

IT_TAB TYPE TABLE OF TY_TAB,

WA_TAB TYPE TY_TAB.

DATA : L_REC TYPE I,

FLAG.

CONSTANTS : C_HTAB TYPE C VALUE CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

DATA : FNAME TYPE STRING VALUE 'C:\TTT1.xls',

L_FILE LIKE RLGRAP-FILENAME.

IF FNAME CS '.TXT'.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = FNAME

FILETYPE = 'ASC'

TABLES

DATA_TAB = IT_F64.

PERFORM SPLIT_TEXT_TABLE TABLES IT_F64.

ELSEIF FNAME CS '.XLS'.

L_FILE = FNAME.

PERFORM UPLOAD_XLSFILE TABLES IT_TAB

USING L_FILE.

PERFORM SPLIT_XLS_TABLE TABLES IT_TAB.

ELSEIF FNAME CS '.CSV' .

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = FNAME

FILETYPE = 'ASC'

TABLES

DATA_TAB = IT_F64.

PERFORM SPLIT_CSV_TABLE TABLES IT_F64.

ENDIF.

&----

---

*& Form UPLOAD_XLSFILE

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

FORM UPLOAD_XLSFILE TABLES P_TABLE

USING P_FILE.

DATA : L_INTERN TYPE KCDE_CELLS OCCURS 0 WITH HEADER LINE.

DATA : L_INDEX TYPE I.

DATA : L_START_COL TYPE I VALUE '1',

L_START_ROW TYPE I VALUE '1',

L_END_COL TYPE I VALUE '256',

L_END_ROW TYPE I VALUE '65536'.

FIELD-SYMBOLS : <FS>.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

FILENAME = P_FILE

I_BEGIN_COL = L_START_COL

I_BEGIN_ROW = L_START_ROW

I_END_COL = L_END_COL

I_END_ROW = L_END_ROW

TABLES

INTERN = L_INTERN

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE : / 'File Error'.

EXIT.

ENDIF.

IF L_INTERN[] IS INITIAL.

FORMAT COLOR COL_BACKGROUND INTENSIFIED.

WRITE : / 'No Data Uploaded'.

EXIT.

ELSE.

SORT L_INTERN BY ROW COL.

LOOP AT L_INTERN.

MOVE L_INTERN-COL TO L_INDEX.

ASSIGN COMPONENT L_INDEX OF STRUCTURE P_TABLE TO <FS>.

MOVE L_INTERN-VALUE TO <FS>.

AT END OF ROW.

APPEND P_TABLE.

CLEAR P_TABLE.

ENDAT.

ENDLOOP.

ENDIF.

ENDFORM. " UPLOAD_XLSFILE

&----

---

*& Form SPLIT_TEXT_TABLE

&----

---

• text

----

---

• -->P_IT_F64 text

----

---

FORM SPLIT_TEXT_TABLE TABLES IT_F64 .

LOOP AT IT_F64 INTO WA_F64.

IF WA_F64+0(1) = 'H'.

SPLIT WA_F64 AT C_HTAB INTO WA_HEADER-INDICATOR

WA_HEADER-BLDAT

WA_HEADER-BLART

WA_HEADER-BUKRS

WA_HEADER-BUDAT

WA_HEADER-WAERS.

CLEAR FLAG.

IF FLAG EQ SPACE.

L_REC = L_REC + 1.

WA_HEADER-RECNO = L_REC.

FLAG = 'X'.

ENDIF.

APPEND WA_HEADER TO IT_HEADER.

CLEAR WA_HEADER.

ELSEIF WA_F64+0(1) = 'L'.

SPLIT WA_F64 AT C_HTAB INTO : WA_ITEM-INDICATOR

WA_ITEM-NEWBS

WA_ITEM-NEWKO

WA_ITEM-WRBTR.

IF FLAG EQ 'X'.

WA_ITEM-RECNO = L_REC.

ENDIF.

APPEND WA_ITEM TO IT_ITEM.

CLEAR WA_ITEM.

ENDIF.

ENDLOOP.

ENDFORM. " SPLIT_TEXT_TABLE

&----

---

*& Form SPLIT_xls_TABLE

&----

---

• text

----

---

• -->P_IT_TAB text

----

---

FORM SPLIT_XLS_TABLE TABLES IT_TAB .

LOOP AT IT_TAB INTO WA_TAB.

IF WA_TAB-F1 = 'H'.

WA_HEADER-INDICATOR = WA_TAB-F1.

WA_HEADER-BLDAT = WA_TAB-F2.

WA_HEADER-BLART = WA_TAB-F3.

WA_HEADER-BUKRS = WA_TAB-F4.

WA_HEADER-BUDAT = WA_TAB-F5.

WA_HEADER-WAERS = WA_TAB-F6.

CLEAR FLAG.

IF FLAG EQ SPACE.

L_REC = L_REC + 1.

WA_HEADER-RECNO = L_REC.

FLAG = 'X'.

ENDIF.

APPEND WA_HEADER TO IT_HEADER.

CLEAR WA_HEADER.

ELSEIF WA_TAB-F1 = 'L'.

WA_ITEM-INDICATOR = WA_TAB-F1.

WA_ITEM-NEWBS = WA_TAB-F2.

WA_ITEM-NEWKO = WA_TAB-F3.

WA_ITEM-WRBTR = WA_TAB-F4.

IF FLAG EQ 'X'.

WA_ITEM-RECNO = L_REC.

ENDIF.

APPEND WA_ITEM TO IT_ITEM.

CLEAR WA_ITEM.

ENDIF.

CLEAR WA_TAB.

ENDLOOP.

ENDFORM. " SPLIT_xls_TABLE

&----

---

*& Form SPLIT_CSV_TABLE

&----

---

• text

----

---

• -->P_IT_F64 text

----

---

FORM SPLIT_CSV_TABLE TABLES IT_F64 .

LOOP AT IT_F64 INTO WA_F64.

IF WA_F64+0(1) = 'H'.

SPLIT WA_F64 AT '|' INTO WA_HEADER-INDICATOR

WA_HEADER-BLDAT

WA_HEADER-BLART

WA_HEADER-BUKRS

WA_HEADER-BUDAT

WA_HEADER-WAERS.

CLEAR FLAG.

IF FLAG EQ SPACE.

L_REC = L_REC + 1.

WA_HEADER-RECNO = L_REC.

FLAG = 'X'.

ENDIF.

APPEND WA_HEADER TO IT_HEADER.

CLEAR WA_HEADER.

ELSEIF WA_F64+0(1) = 'L'.

SPLIT WA_F64 AT '|' INTO : WA_ITEM-INDICATOR

WA_ITEM-NEWBS

WA_ITEM-NEWKO

WA_ITEM-WRBTR.

IF FLAG EQ 'X'.

WA_ITEM-RECNO = L_REC.

ENDIF.

APPEND WA_ITEM TO IT_ITEM.

CLEAR WA_ITEM.

ENDIF.

ENDLOOP.

ENDFORM. " SPLIT_CSV_TABLE

[I355602]

Hi Prasanth Kasturi,

Use EXCEL file to enter your data and then save this file as a TEXT DELIMITED file type.

This will result in a text file with fields separated by one tab and records in different lines.

If you use a line for headings of the fields, then remember to delete the first line from the internal table (in case you go for a update in your database table from internal table).

To upload a flat file (*.txt file) into your internal table, you can probably use this code,

Its working.

REPORT Z_VENDOR.

TYPES : BEGIN OF VENDOR,

LIFNR LIKE RF02K-LIFNR,

BUKRS LIKE RF02K-BUKRS,

EKORG LIKE RF02K-EKORG,

KTOKK LIKE RF02K-KTOKK,

ANRED LIKE LFA1-ANRED,

NAME1 LIKE LFA1-NAME1,

SORTL LIKE LFA1-SORTL,

LAND1 LIKE LFA1-LAND1,

SPRAS LIKE LFA1-SPRAS,

WAERS LIKE LFM1-WAERS,

END OF VENDOR.

DATA : VENDOR_TAB TYPE STANDARD TABLE OF VENDOR INITIAL SIZE 10 WITH HEADER LINE.

START-OF-SELECTION.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = 'c:\vendors.txt'

FILETYPE = 'DAT'

TABLES

DATA_TAB = VENDOR_TAB.

IF SY-SUBRC <> 0.

ENDIF.

Using this code, your flat file data will be uploaded to your internal table.

Now you can manipulate this internal table as per your requirement.

Hope this solves your problem.

Thanks & Regards.

Tarun Gambhir.