Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

CSV file to Ztable

former_member611341
Participant
0 Kudos

Can u guys tell me steps/give sample code to upload a ZTABLE from a CSV file.

Iam not using all fields in the CSV file( if file have 5 fields,I am using 3 out of it) and also not all fields in the ZTABLE ( uploading 3 fields out of 7 fields)and some necessary validations.

Thnaks..

1 ACCEPTED SOLUTION

former_member611341
Participant
0 Kudos

Iam having 10412 records in my excel sheet...( but not sure when the user uses it he/she may have more or less rows)..

when i ran it for 9999 rows (<b>deleting extra rows in my</b> <b>excel sheet</b> ) ...every thing was fine..

when i ran it for 10412 rows...things got messed up due to truncation..as the values in the prvious rows are over written by truncated row numbers.

e.g : 10357 row is truncated 0357 ..coz of that it over writtes the previus row value of 0357.

Thansk for quick response..

13 REPLIES 13

Former Member
0 Kudos

HI,

Try this. We use this FM and it works quite well. 

** internal table to receive stat key figures data from EXCEL 
data: begin of intab01 occurs 0, 
kostl(10) type n, " cost center number 
lstar(10), " stat key figure code 
per01(15), " period 01 value 
per02(15), " period 02 value 
per03(15), " period 03 value 
per04(15), " period 04 value 
per05(15), " period 05 value 
per06(15), " period 06 value 
per07(15), " period 07 value 
per08(15), " period 08 value 
per09(15), " period 09 value 
per10(15), " period 10 value 
per11(15), " period 11 value 
per12(15), " period 12 value 
end of intab01. 

* Added Input Work Area 
data: begin of in_wa, 
kostl(10), " cost center number 
lstar(10), " stat key figure code 
per01(15), " period 01 value 
per02(15), " period 02 value 
per03(15), " period 03 value 
per04(15), " period 04 value 
per05(15), " period 05 value 
per06(15), " period 06 value 
per07(15), " period 07 value 
per08(15), " period 08 value 
per09(15), " period 09 value 
per10(15), " period 10 value 
per11(15), " period 11 value 
per12(15), " period 12 value 
end of in_wa. 

data: w_index type i. 

* ---------------------------------------------------------------------* 
* FIELD SYMBOLS 
* ---------------------------------------------------------------------* 
field-symbols : <fs>. 
* 
*----------------------------------------------------------------------* 
* S E L E C T I O N S C R E E N F I E L D S 
*----------------------------------------------------------------------* 

* 
parameters: p_input like rlgrap-filename obligatory. 
parameters: p_skp type n default 0. 

** transfer data from EXCEL input file to internal table INTAB01 
perform upload_excel. 

*----------------------------------------------------------------------* 
form upload_excel. 

* load plan data from EXCEL into table INTAB01 via function UPLOAD * 
*----------------------------------------------------------------------* 
data : i_intern type alsmex_tabline occurs 0 with header line."BCT004 
data : w_start_col type i value '1', 
w_start_row type i value '1', 
w_end_col type i value '18', "must = # of excel columns 
w_end_row type i value '65536'. 

* 
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE' "BCT004 
exporting 
filename = p_input 
i_begin_col = w_start_col 
i_begin_row = w_start_row 
i_end_col = w_end_col 
i_end_row = w_end_row 
tables 
intern = i_intern. 
* 
if i_intern[] is initial. " File empty SLG001 
format color col_negative. 
skip 1. 
write: / 'There is a problem with'. 
format color col_normal. 
write: / p_input. 
format color col_negative. 
write: / 'Check the file and rerun'. 
skip 2. 
write: /15 '>>>>>>>> E N D O F P R O G R A M <<<<<<<<'. 
stop. 
endif. 
* Begin SLG001 
sort i_intern by row col. 
loop at i_intern. 
if i_intern-row <= p_skp. 
continue. 
endif. 
move : i_intern-col to w_index. 
if w_index <= 18. "Col 18-last data col 
assign component w_index of structure in_wa to <fs>. 
move : i_intern-value to <fs>. 
endif. 
at end of row. 
move-corresponding in_wa to intab01. 

shift intab01-per01 right deleting trailing ' '. 
shift intab01-per02 right deleting trailing ' '. 
shift intab01-per03 right deleting trailing ' '. 
shift intab01-per04 right deleting trailing ' '. 
shift intab01-per05 right deleting trailing ' '. 
shift intab01-per06 right deleting trailing ' '. 
shift intab01-per07 right deleting trailing ' '. 
shift intab01-per08 right deleting trailing ' '. 
shift intab01-per09 right deleting trailing ' '. 
shift intab01-per10 right deleting trailing ' '. 
shift intab01-per11 right deleting trailing ' '. 
shift intab01-per12 right deleting trailing ' '. 

append intab01 . 
clear: in_wa, intab01 . 
endat. 
endloop. 
* 

endform. " upload_excel

<b>See the below links for more example programs</b>

http://sap.ittoolbox.com/code/archives.asp?d=3365&a=s&i=10

http://sap.ittoolbox.com/code/archives.asp?d=3126&a=s&i=10

http://sap.ittoolbox.com/code/archives.asp?d=3127&a=s&i=10

http://sap.ittoolbox.com/code/archives.asp?d=2128&a=s&i=10

http://sap.ittoolbox.com/code/archives.asp?d=1721&a=s&i=10

Thanks

Sudheer

Former Member
0 Kudos

hii,

Use gui_download to download from internal table to flat file.

Use fm ALSM_EXCEL_TO_INTERNAL_TABLE to upload data frm excel.

Use function module GUI_UPLOAD for .csv

CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
   FILENAME = 'C:test.csv'
   FILETYPE = 'ASC'
   TABLES
   DATA_TAB = itab
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.

chk this link

Regards

Naresh

former_member611341
Participant
0 Kudos

Hello guys

Iam trying to upload an EXCEL file using ALSM_EXCEL_TO_INTERNAL_TABLE....Iam getting the following error( DUMP)...

<b>Incorrect FORM call.

The FORM call is incorrect for parameter number 2.

The current internal table passed to the formal table parameter has

a type that is not compatible with the type of the formal parameter.

</b>

i declared my internal table fields mostly as CHAR fields as my excel file is coming from thrid party and its format is not good( eg: in one field (column) it has account number and cost centre combined with '-' in between them and in one column it has amount field in which braces indicates negative quantity )....

any ideas...

Thanks

0 Kudos

When using that Function module, you must called it with the table parameter that it is expecting. Please see the following program. This program builds a dynamic internal table and fills the internal table from the excel file on the frontend.



report zrich_0002.

type-pools: slis.

field-symbols: <dyn_table> type standard table,
               <dyn_wa>,
               <dyn_field>.

data: it_fldcat type lvc_t_fcat,
      wa_it_fldcat type lvc_s_fcat.

type-pools : abap.

data: new_table type ref to data,
      new_line  type ref to data.

<b>data: xcel type table of alsmex_tabline with header line.</b>

selection-screen begin of block b1 with frame title text .
parameters: p_file type  rlgrap-filename default 'c:Test.csv'.
parameters: p_flds type i.
selection-screen end of block b1.

start-of-selection.

* Add X number of fields to the dynamic itab cataelog
  do p_flds times.
    clear wa_it_fldcat.
    wa_it_fldcat-fieldname = sy-index.
    wa_it_fldcat-datatype = 'C'.
    wa_it_fldcat-inttype = 'C'.
    wa_it_fldcat-intlen = 10.
    append wa_it_fldcat to it_fldcat .
  enddo.
  .
* Create dynamic internal table and assign to FS
  call method cl_alv_table_create=>create_dynamic_table
               exporting
                  it_fieldcatalog = it_fldcat
               importing
                  ep_table        = new_table.

  assign new_table->* to <dyn_table>.

* Create dynamic work area and assign to FS
  create data new_line like line of <dyn_table>.
  assign new_line->* to <dyn_wa>.

* Upload the excel
  call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       exporting
            filename                = p_file
            i_begin_col             = '1'
            i_begin_row             = '1'
            i_end_col               = '200'
            i_end_row               = '5000'
<b>       tables
            intern                  = xcel</b>
       exceptions
            inconsistent_parameters = 1
            upload_ole              = 2
            others                  = 3.

* Reformt to dynamic internal table
  loop at xcel.
    assign component xcel-col of structure <dyn_wa> to <dyn_field>.
    if sy-subrc = 0.
     <dyn_field> = xcel-value.
    endif.

    at end of row.
      append <dyn_wa> to <dyn_table>.
      clear <dyn_wa>.
    endat.
  endloop.

* Write out data from table.
  loop at <dyn_table> into <dyn_wa>.
    do.
      assign component  sy-index  of structure <dyn_wa> to <dyn_field>.
      if sy-subrc <> 0.
        exit.
      endif.
      if sy-index = 1.
        write:/ <dyn_field>.
      else.
        write: <dyn_field>.
      endif.
    enddo.
  endloop.

Regards,

Rich Heilman

former_member611341
Participant
0 Kudos

Thanks Rich and Sudheer..code helped to debug and know about FM...

Now I have one more issue... i can upload only upto 9999 rows...but my excel file will have more no.of rows....not sure how many rows it will have..it varies from time to time ...

so whats the solution for it.

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

If you look at the structure of ALSMEX_TABLINE via SE11. it has the first field as row which is only 4 character long. This is why you think that the limit is 9999, but really all of the records are there in the internal table. Its just once the row counter goes passed 9999, the next digit is trucated, or stripped off. But if you write out the internal table, you will see that all records are there.

Regards,

Rich Heilman

0 Kudos

Hii Rich...

Thanks for your quick response ...I really appreciate ...

yes I noticed row is 4 CHAR long..

As u said,it is truncating the first digits,if it is more than 4 chars...coz of that the previous values stored in that row are getting messed

<b>e.g.: if row 10357 it is truncating row as 0357 ...</b>

If I am doing it for 9999 rows ...every thing is fine..

If it crosses 9999...things getting messes up...

Any ideas..or do u think Iam doing wrong some where..

0 Kudos

No, you are not doing anything wrong. This is just how the FM is. Again all rows of your excel sheet shoule be there, just that the row numbers will be screwed up. As long as you read it in sequenceal order there should be no problem. Are you saying that you only have 9999 records in the internal table?

Regards,

Rich Heilman

former_member611341
Participant
0 Kudos

Rich one more thing...

u declared P_FLDS parameter in selection screen ...I didn't understand clearly what's the importance of it..?

sorry if iam bothering u much...

regards..

former_member611341
Participant
0 Kudos

Iam having 10412 records in my excel sheet...( but not sure when the user uses it he/she may have more or less rows)..

when i ran it for 9999 rows (<b>deleting extra rows in my</b> <b>excel sheet</b> ) ...every thing was fine..

when i ran it for 10412 rows...things got messed up due to truncation..as the values in the prvious rows are over written by truncated row numbers.

e.g : 10357 row is truncated 0357 ..coz of that it over writtes the previus row value of 0357.

Thansk for quick response..

0 Kudos

Post your code here, or send to my email address on my business card.

Regards

Rich HEilman

0 Kudos

Thanks for sending the code, remember the internal table is coming from the FM as sorting in the correct way, so do not sort it because it will screw up the row number when you have more than 9999.





*  sort i_intern by row col.  " Comment this out.
  loop at i_intern.
  if i_intern-row <= p_skp.
  continue.
  endif.
  move : i_intern-col to w_index.
  if w_index <= 17. "Col 17-last data col
  assign component w_index of structure t_wa to <fs>.
  move : i_intern-value to <fs>.
endif.
at end of row.
move-corresponding t_wa to t_del.
 append t_del .
 clear: t_wa,
    t_del .
 endat.
 endloop.

Regards,

Rich Heilman

former_member611341
Participant
0 Kudos

Thnaks very much Rich...I tried many things but didnt tried that one...:)

Thanks for all your time..

Regards..