topic Re: uploading excel data to database table in Application Development and Automation Discussions

uploading excel data to database table

Former Member — Tue, 11 Aug 2009 04:58:28 GMT

Dear Experts,

I have an Excel file which was downloaded from SQL database, now I need to export this data into my 'ztable', for this first I created an internal table of ztable structure and tried to upload into this table through ''TEXT_CONVERT_XLS_TO_SAP".

But I was unable to get data into the internal table(incompatible type), so now I created an internal table of character type fileds now I able to get data to this internal table, but to put this internal table data to actual database table it gives error, here the actual problem is the fields of internal table are getting concated with blank spaces(observed through debug), I unable to condense these blanks also, (I have created an Excel of my own and put data into it and uploaded that data there is no problem working fine but this problem occurs only for the excel downloaded from SQL db)

The problem occurs for quantity and currency fields(I tried through standard FMs also to convert data types ), Could plz suggest something..........

Thank you

Regards

Srinivas

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 05:09:07 GMT

Hi,

Try using pack and shift delete to remove the extra spaces for the quantity and currency fields and then try to upload into the Ztable



loop at itab.
pack itab-menge to itab-menge.
shift itab-menge left deleting leading space.

pack itab-wears to itab-wears.
shift itab-weards left deleting leading space.

modify itab transpoting menge wears.
endloop.

Regards,

Vik

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 05:48:35 GMT

Hai,

Thank you for ur response, eventhough I used the code as you said I'm getting the same problem while executing the pack statement, the error is Unable to interpret "2101 " as a number, here have u observed the blanks are getting added right side of the value and for some fields it gets added left side..........

thank you.................

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 05:50:18 GMT

hi sri!

can you paste your code here..

Sumit

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 05:54:46 GMT

Hi,

Well pack statement can be used only for numbers though. Since you can declared the field as type char you are getting that message. In that case you can just delete the leading and trailing space



shift  itab-menge left   deleting leading space.
shift  itab-menge right deleting trailing space.

Regards,

Vik

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 06:07:26 GMT

Hi,

To upload excel sheet into database table, define a types which is of same as table fields.

and try it with the function module TEXT_CONVERT_XLS_TO_SAP

Here is a sample code:

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
        EXPORTING
         I_FIELD_SEPERATOR          ='X'    
          i_tab_raw_data             = fs_truxs( TYPE of  truxs_t_text_data.)
          i_filename                 = '<FILE-PATH>'
      tables
          i_tab_converted_data       = <Internal Table>
       EXCEPTIONS
         CONVERSION_FAILED          = 1
         OTHERS                     = 2
                .
      IF sy-subrc <> 0.
 MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
      ENDIF.

LOOP AT <internal-table> INTO field string.
 INSERT <DATABASE- TABLE> FROM <field string>.
ENDLOOP.

Hope this solves the issue.

Regards,

Rajani

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 06:07:30 GMT

Hai,

I already used shift also, but there is no use.......,it works for split statement but I have large no.of records in my excel, it is hard to write split statement in loop for every fields of 54 fields...........

Edited by: Sri on Aug 11, 2009 11:37 AM

Re: uploading excel data to database table

Former Member — Tue, 11 Aug 2009 06:13:47 GMT

How did you get blanks if you are using FM "text_convert.."

Instead of makin all the fields of type char in your int tab, make only the quant and curr fields(those that are giving error) of type char in you itab. Then in your code copy them into quant and currency variables manually.

Regards,

Sumit

Edited by: Sumit Nene on Aug 11, 2009 8:19 AM