Today I got the request from our sales department to get a list of all Mayors in Germany for a Marketing Campaign. Unfortunately there is no attribute maintained to retrieve this information using the CRM Segment Builder. So I thought where to get this information from and found the Wikipedia (http://www.wikipedia.org/) “[Export pages | http://en.wikipedia.org/wiki/Special:Export]” feature. From my Blog: “[Geocode Business Partner with Google Maps | Geocode Business Partner with Google Maps]” I had already everything in place to use Wikipedia. I had to do just these steps: h3. Preparation h4. Create Database Table for Towns and their Mayors I’ve put all development objects together in the Package ZWIKIPEDIALOAD. Here I’ve created also the DB Table ZCITYMAYORS which will contain City and Mayor. The following picture shows you the table definition: 
h4. Transform Mediawiki to ABAP Like I did in: “[Geocode Business Partner with Google Maps | Geocode Business Partner with Google Maps]” the XML returned is transferred to an ABAP variable using Simple Transformations (Fifth ABAP Online Meet-up: Simple Transformations). To test the Transformation I first downloaded the result of this request to my local drive: h4. Implement ABAP Program to fill Table ZCITYMAYORS with Cities My list of Cities is available in an Excel file. So I had to find a way to import it to my Database table. In the ABAP Forum I’ve found a link to Upload Excel document into internal table (http://www.sapdevelopment.co.uk/file/file_upexcel.htm). With a few modifications this was a perfect solution.
h3. Implement ABAP Program to fill Table ZCITYMAYORS with Mayors Finally we can now fill the Table ZCITYMAYORS also with Mayors. Together with the Transformation this little piece of ABAP does all the work: REPORT zget_mayors_for_cities. DATA: it_citymayors TYPE TABLE OF zcitymayors, wa_citymayors LIKE LINE OF it_citymayors, mayor TYPE full_name, trash TYPE string. PARAMETERS: s_city TYPE s_city LOWER CASE. SELECT * FROM zcitymayors INTO TABLE it_citymayors WHERE city LIKE s_city. * HTTP Client according to * BSP: Create a weather magnet using xml feed from weather.com DATA: client TYPE REF TO if_http_client, url TYPE string, xml TYPE xstring, c_xml TYPE string, city TYPE string. * Converter DATA: l_convin TYPE REF TO cl_abap_conv_in_ce. LOOP AT it_citymayors INTO wa_citymayors. * Use the Progress Indicator to show the user which City is processed CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = sy-index text = wa_citymayors-city. city = wa_citymayors-city. * Spaces have to be replaced by _ in the URL REPLACE FIRST OCCURRENCE OF space IN city WITH '_'. CONCATENATE 'http://de.wikipedia.org/wiki/Spezial:Export/' city INTO url. ****Create the HTTP client
TRY.
CALL METHOD cl_http_client=>create_by_url
EXPORTING
url = url
IMPORTING
client = client
EXCEPTIONS
OTHERS = 1.
client->send( ).
client->receive( ).
xml = client->response->get_data( ).
client->close( ).
CATCH cx_root.
WRITE: / 'HTTP Connection error: ', city.
ENDTRY.
* Wikipedia does not provide a encoding with the returned XML * so we have to do the conversion manually TRY. CALL METHOD cl_abap_conv_in_ce=>create EXPORTING encoding = 'UTF-8' input = xml endian = 'L' RECEIVING conv = l_convin. CALL METHOD l_convin->read IMPORTING data = c_xml. CATCH cx_root. WRITE: / 'Problem during Character conversion: ', city. ENDTRY. ****Transform XML to ABAP Values
TRY.
CALL TRANSFORMATION zwikipedia_mayor_to_abap
SOURCE XML c_xml
RESULT mayor = mayor.
CATCH cx_root.
WRITE: / 'Data loss during transformation: ', city.
ENDTRY.
* Some Mayors already have pecial Pages REPLACE FIRST OCCURRENCE OF '[[' IN mayor WITH ''. REPLACE FIRST OCCURRENCE OF ']]' IN mayor WITH ''. * Some Mayors are members of a Party SPLIT mayor AT '(' INTO mayor trash. wa_citymayors-mayor = mayor. WRITE: / wa_citymayors-city. * Update Database IF NOT wa_citymayors-mayor IS INITIAL. UPDATE zcitymayors FROM wa_citymayors. WRITE: wa_citymayors-mayor. ENDIF. ENDLOOP. The result running this Program with the Parameter 'Tr%' the result in table zcitymayors is:
h4. Transform Mediawiki to ABAP Like I did in: “[Geocode Business Partner with Google Maps | Geocode Business Partner with Google Maps]” the XML returned is transferred to an ABAP variable using Simple Transformations (Fifth ABAP Online Meet-up: Simple Transformations). To test the Transformation I first downloaded the result of this request to my local drive: h4. Implement ABAP Program to fill Table ZCITYMAYORS with Cities My list of Cities is available in an Excel file. So I had to find a way to import it to my Database table. In the ABAP Forum I’ve found a link to Upload Excel document into internal table (http://www.sapdevelopment.co.uk/file/file_upexcel.htm). With a few modifications this was a perfect solution.h3. Implement ABAP Program to fill Table ZCITYMAYORS with Mayors Finally we can now fill the Table ZCITYMAYORS also with Mayors. Together with the Transformation this little piece of ABAP does all the work: REPORT zget_mayors_for_cities. DATA: it_citymayors TYPE TABLE OF zcitymayors, wa_citymayors LIKE LINE OF it_citymayors, mayor TYPE full_name, trash TYPE string. PARAMETERS: s_city TYPE s_city LOWER CASE. SELECT * FROM zcitymayors INTO TABLE it_citymayors WHERE city LIKE s_city. * HTTP Client according to * BSP: Create a weather magnet using xml feed from weather.com DATA: client TYPE REF TO if_http_client, url TYPE string, xml TYPE xstring, c_xml TYPE string, city TYPE string. * Converter DATA: l_convin TYPE REF TO cl_abap_conv_in_ce. LOOP AT it_citymayors INTO wa_citymayors. * Use the Progress Indicator to show the user which City is processed CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' EXPORTING percentage = sy-index text = wa_citymayors-city. city = wa_citymayors-city. * Spaces have to be replaced by _ in the URL REPLACE FIRST OCCURRENCE OF space IN city WITH '_'. CONCATENATE 'http://de.wikipedia.org/wiki/Spezial:Export/' city INTO url. ****Create the HTTP client
TRY.
CALL METHOD cl_http_client=>create_by_url
EXPORTING
url = url
IMPORTING
client = client
EXCEPTIONS
OTHERS = 1.
client->send( ).
client->receive( ).
xml = client->response->get_data( ).
client->close( ).
CATCH cx_root.
WRITE: / 'HTTP Connection error: ', city.
ENDTRY.
* Wikipedia does not provide a encoding with the returned XML * so we have to do the conversion manually TRY. CALL METHOD cl_abap_conv_in_ce=>create EXPORTING encoding = 'UTF-8' input = xml endian = 'L' RECEIVING conv = l_convin. CALL METHOD l_convin->read IMPORTING data = c_xml. CATCH cx_root. WRITE: / 'Problem during Character conversion: ', city. ENDTRY. ****Transform XML to ABAP Values
TRY.
CALL TRANSFORMATION zwikipedia_mayor_to_abap
SOURCE XML c_xml
RESULT mayor = mayor.
CATCH cx_root.
WRITE: / 'Data loss during transformation: ', city.
ENDTRY.
* Some Mayors already have pecial Pages REPLACE FIRST OCCURRENCE OF '[[' IN mayor WITH ''. REPLACE FIRST OCCURRENCE OF ']]' IN mayor WITH ''. * Some Mayors are members of a Party SPLIT mayor AT '(' INTO mayor trash. wa_citymayors-mayor = mayor. WRITE: / wa_citymayors-city. * Update Database IF NOT wa_citymayors-mayor IS INITIAL. UPDATE zcitymayors FROM wa_citymayors. WRITE: wa_citymayors-mayor. ENDIF. ENDLOOP. The result running this Program with the Parameter 'Tr%' the result in table zcitymayors is:
- SAP Managed Tags:
5 Comments
