topic Re: simple excel logic in Application Development and Automation Discussions

simple excel logic

Former Member — Tue, 20 Nov 2007 18:40:41 GMT

hi friends,

i am given a big excel sheet of FI transactional data, which has around 60 000 records,

the company codes have changed , previously which were 3000 are now 3050,

now there are a thousand records with comp code 3000, how do i change all these compcodes to 3050.

thank you

Re: simple excel logic

Former Member — Tue, 20 Nov 2007 18:59:52 GMT

You want to change company 3000 to 3050 within excel then

Copy the entire column into another excel then do like ctrl+F ,Select Replace -> now enter value and new value -> replace all.

now copy the data -> goto original excel file -> paste it entire column.

Thanks

Seshu

Re: simple excel logic

Former Member — Tue, 20 Nov 2007 19:12:36 GMT

hi seshu,

thank you very much for responding,

actually i too thought of doing this but there are 60,000 records,

doing this manually, would be too tedious,

what i expected some one would help me with excel functions like

if(condion_true, true_value,false_value)

if(bukrs=3000, 3050, No_change)

some thing like this.

can you please look into this.

thank you.

Re: simple excel logic

naimesh_patel — Tue, 20 Nov 2007 19:17:06 GMT

But again it will take some time and you need to apply that formula to each row.

So, better to replace it.

Open your excel file..

Select the Entire columns(say column A) by clicking on the top .

Press Cntrl+F.. in the pop up select replace tab

Enter 3000 into Find what

Enter 3050 into replace with

Press button REPLACE ALL

It will take some time, but it will change all bukrs from 3000 to 3050

Regards,

Naimesh Patel

Re: simple excel logic

balbino_soaresferreirafil — Tue, 20 Nov 2007 19:21:23 GMT

Hi sanjana,

Do you need tochange that's records only in excel sheet? Or do you need to change in the database too?

If you need, I have one sample code to change this in excel.

*&----

*& Report ZEXEMPLOEXCEL *

*& *

*&----

*& EXPORT DATA TO EXCEL USING "CLIPBOARD", EXPORTING DATA FOR *

*& CLIPBOARD AND "PAST" THE INTERNAL TABLE *

*&----

REPORT ZEXEMPLOEXCEL.

************************************************************************

**FIELD-SYMBOL AND OLE FOR EXPORTING

************************************************************************

INCLUDE ole2incl.

field-symbols: .

data: w_cell1 type ole2_object,

w_cell2 type ole2_object.

data: h_excel type ole2_object, " Excel object

h_mapl type ole2_object, " list of workbooks

h_map type ole2_object, " workbook

h_zl type ole2_object, " cell

worksheet type ole2_object,

h_cell type ole2_object,

range type ole2_object,

h_sheet1 type ole2_object, "FIRST SHEET TO BE CREATED

h_sheet2 type ole2_object, "SECOND SHEET TO BE CREATED

h_sheet3 type ole2_object, "THIRD SHEET TO BE CREATED

gs_font type ole2_object.

************************************************************************

TABLES AND TYPES

************************************************************************

TABLES: SCUSTOM, SBOOK, SFLIGHT.

TYPES: data1(1500),

ty_data type table of data1.

************************************************************************

**INTERNAL TABLES, CONSTANTS AND VARIABLES

************************************************************************

DATA: ti_scustom LIKE STANDARD TABLE OF scustom WITH HEADER LINE,

ti_sbook LIKE STANDARD TABLE OF sbook WITH HEADER LINE,

ti_sflight LIKE STANDARD TABLE OF sflight WITH HEADER LINE,

wa_scustom type ty_data with header line,

wa_sbook type ty_data with header line,

wa_sflight type ty_data with header line,

begin of hex,

tab type x,

end of hex.

constants cns_09(2) type n value 09.

DATA: deli(1) type c,

l_amt(18) type c,

nome_planilha(20) type c.

*--FIELDS TO CONCATENATE

*--

DATA: FORCURAM(15), "field FORCURAM of table SBOOK

LOCCURAM(15), "field LOCCURAM of table SBOOK

PRICE(20), "field PRICE of table SFLIGHT

SEATSMAX(10), "field SEATSMAX dof table SFLIGHT

SEATSOCC(10), "field SEATSOCC of table SFLIGHT

PAYMENTSUM(20). "Cfield PAYMENTSUM of table SFLIGHT

************************************************************************

SET IN FIELD DELI THE SYMBOL '#'

************************************************************************

assign deli to = hex-tab.

*--CALL FORMS

PERFORM preenche_tabelas_internas.

PERFORM abre_excel.

*--EXPORTE DATAS FROM WORKAREA WA_SCUSTOM

PERFORM exporta_dados using 'SCUSTOM' h_sheet1 wa_scustom[].

*--EXPORTA DATAS FROM WORKAREA WA_SBOOK

PERFORM exporta_dados using 'SBOOK' h_sheet2 wa_sbook[].

*--EXPORTA DATAS FROM WORKAREA WA_SFLIGHT

PERFORM exporta_dados using 'SFLIGHT' h_sheet3 wa_sflight[].

*--CLOSE EXCEL CONNECTION

free object h_zl.

free object h_mapl.

free object h_map.

free object h_excel.

*----

FORM preenche_tabelas_internas

*----

POPULATING INTERNAL TABLES AND WORKAREAS

*----

form preenche_tabelas_internas.

************************************************************************

POPULATING INTERNAL TABLES AND WORKAREAS "WA_"

************************************************************************

select * from scustom into table ti_scustom.

select * from sbook into table ti_sbook.

select * from sflight into table ti_sflight.

concatenate 'CARRID' 'CONNID' 'FLDATE' 'CUSTOMID'

'CLASS' 'FORCURAM' 'FORCURKEY' 'LOCCURAM'

'LOCCURKEY' 'ORDER_DATE' 'AGENCYNUM'

into wa_sbook

separated by deli.

append wa_sbook.

clear wa_sbook.

concatenate 'ID' 'NAME' 'STREET' 'POSTBOX'

'POSTCODE' 'CITY' 'COUNTRY' 'TELEPHONE'

into wa_scustom

separated by deli.

append wa_scustom.

clear wa_scustom.

concatenate 'CARRID' 'CONNID' 'FLDATE' 'PRICE' 'CURRENCY'

'PLANETYPE' 'SEATSMAX' 'SEATSOCC' 'PAYMENTSUM'

into wa_sflight

separated by deli.

append wa_sflight.

clear wa_sflight.

*-- WA_SCUSTOM DATA'S

loop at ti_scustom.

concatenate ti_scustom-ID ti_scustom-NAME ti_scustom-STREET

ti_scustom-POSTBOX ti_scustom-POSTCODE ti_scustom-CITY

ti_scustom-COUNTRY ti_scustom-TELEPHONE

into wa_scustom

separated by deli.

append wa_scustom.

clear wa_scustom.

endloop.

*--WA_SBOOK DATA'S

loop at ti_sbook.

FORCURAM = ti_sbook-FORCURAM.

LOCCURAM = ti_sbook-LOCCURAM.

concatenate ti_sbook-CARRID ti_sbook-CONNID ti_sbook-FLDATE

ti_sbook-CUSTOMID ti_sbook-CLASS FORCURAM

ti_sbook-FORCURKEY LOCCURAM ti_sbook-LOCCURKEY

ti_sbook-ORDER_DATE ti_sbook-AGENCYNUM

into wa_sbook

separated by deli.

append wa_sbook.

clear wa_sbook.

endloop.

*--WA_SFIGHT DATA'S

loop at ti_sflight.

PRICE = ti_sflight-PRICE.

SEATSMAX = ti_sflight-SEATSMAX.

SEATSOCC = ti_sflight-SEATSOCC.

PAYMENTSUM = ti_sflight-PAYMENTSUM.

concatenate ti_sflight-CARRID ti_sflight-CONNID

ti_sflight-FLDATE PRICE ti_sflight-CURRENCY

ti_sflight-PLANETYPE SEATSMAX SEATSOCC PAYMENTSUM

into wa_sflight

separated by deli.

append wa_sflight.

clear wa_sflight.

endloop.

endform.

*----

FORM abre_excel

*----

OPEN EXCEL

*----

form abre_excel.

************************************************************************

START AND PREPARE EXCEL

************************************************************************

if h_excel-header = space or h_excel-handle = -1.

create object h_excel 'EXCEL.APPLICATION'.

endif.

call method of h_excel 'Workbooks' = h_mapl.

set property of h_excel 'Visible' = 1.

call method of h_mapl 'Add' = h_map.

endform. "abre_excel

*----

FORM exporta_dados

*----

EXPORTA OS DADOS PARA A PLANILHA ESPECIFICADA NO PERFORM "USING"

*----

form exporta_dados using nome sheet table.

*--CRIA UMA PLANILHA COM O NOME SBOOK

nome_planilha = nome.

get property of h_excel 'Sheets' = sheet .

call method of sheet 'Add' = h_map.

set property of h_map 'Name' = nome_planilha .

get property of h_excel 'ACTIVESHEET' = worksheet.

*--FORMATA A PRIMEIRA LINHA COMO NEGRITO (CABEÇALHO)

*--Linhas e Colunas onde a formatação Inicia

call method of h_excel 'Cells' = w_cell1

EXPORTING

#1 = 1 "Linha

#2 = 1. "Coluna

*--Linhas e Colunas onde a formatação Termina

call method of h_excel 'Cells' = w_cell2

EXPORTING

#1 = 1 "Linha

#2 = 50. "Coluna

call method of h_excel 'Range' = h_cell

EXPORTING

#1 = w_cell1

#2 = w_cell2.

get property of h_cell 'Font' = gs_font .

set property of gs_font 'Bold' = 1 .

*--COPY WA_SBOOK FOR CLIPBOARD

data l_rc type i.

call method cl_gui_frontend_services=>clipboard_export

IMPORTING

data = table

CHANGING

rc = l_rc

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

not_supported_by_gui = 3

others = 4.

*--PREPARE SHEET TO RECEIVE DATA'S

call method of h_excel 'Cells' = w_cell1

EXPORTING

#1 = 1

#2 = 1.

call method of h_excel 'Cells' = w_cell2

EXPORTING

#1 = 1

#2 = 1.

call method of h_excel 'Range' = range

EXPORTING

#1 = w_cell1

#2 = w_cell2.

*--PAST DATA'S IN SBOOK SHEET

call method of range 'Select'.

call method of worksheet 'Paste'.

endform. "exporta_dados_sbook

just sorry for portuguese code. I don't had time to translate.

Regards Balbino

Re: simple excel logic

Former Member — Tue, 20 Nov 2007 19:32:03 GMT

hi sanjana,

do u want to change it at excel?

so, only do it:

=IF(<Cell>=3000;3050;<Cell>)

like this:

=IF(B2=3000;3050;B2)

Regards

Allan Cristian

Re: simple excel logic

Former Member — Tue, 20 Nov 2007 21:59:19 GMT

hi naimesh,

your suggestion seems to work,

thanks for your effort.

as you said, the other methos needs to be applied for each row, yours seems to be a better way,

thanks for your effort.