11-20-2007 6:40 PM
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
11-20-2007 6:59 PM
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
11-20-2007 6:59 PM
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
11-20-2007 7:12 PM
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.
11-20-2007 7:17 PM
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
11-20-2007 7:21 PM
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
11-20-2007 7:32 PM
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
11-20-2007 9:59 PM
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.