Load flat file into a database table

[Former Member]

I have a database table ztab_sal that has the following fields

field-datatype-length-decimals

mandt-clnt-3-0

field1-numc-3-0

field2-numc-3-0

field3-numc-3-0

field4-quan-17-0

field5-dec-17-2

field6-dec-17-2

field7-dec-17-2

I am using UPLOAD_FILES function module to load a spreadsheet into ztab_sal. I have tried various things but it fails on me miserably. Following is the code I use..

parameters: name(128),

type(3).

data: fitab like RLGRAP occurs 0 with header line.

data: itab like ZTAB_SAL occurs 0 with header line.

fitab-FILENAME = name.

append fitab.

call function 'UPLOAD_FILES'

exporting I_TRUNCLEN = ' '

I_FILETYPE = type

I_XPC = 'X'

tables FILE_ALL = itab

TAB_FILE = fitab.

I take user input for filename and filetype. Say "load" is the name of the file then I have tried c:\load.xls and c:\load.csv filenames with every possible combination of filetype (ASC to DAT). Everytime the error is "Error Reading file". I tried to debug, it goes into GUI_UPLOAD function module well but somewhere it's missing something. Can anyone help me with this? Thanks for your inputs.

Sam

[Former Member]

I don't know if that FM can upload Excel. I've always saved the file in text format and uploaded with filetyp 'ASC' (and used UPLOAD or GUI_UPLOAD).

Rob

[Former Member]

Rob, as a follow-up question to your post, when you create the text file from your excel spreadsheet, what is the field separator you use? As I have created my text file out of .csv, it is a comma. I have also tried using space as the de-limiter with no luck. I am not sure whether these de-limiters are causing the problem!! The statement "file_all = data_all-all." looks very innocent. file_all is the internal table with the same structure as my dbtable with a header line. Whereas data_all has only one field "all" in which entire file contents are dumped in different rows. As a novice abaper, I would like to ask how does this one field gets converted to multiple fields of my internal table. Obviously it should use some separator or some fixed length logic right?!? Please share your comments here.

[former_member355937]

Hi Sam,

You can use the FM 'WS_UPLOAD'.

Pls. Check out the following code:

***<<<

TABLES: SFLIGHT.

DATA: BEGIN OF W_SFLT OCCURS 0,

MANDT LIKE SFLIGHT-MANDT,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID,

FLDATE LIKE SFLIGHT-FLDATE,

PRICE LIKE SFLIGHT-PRICE,

CURRENCY LIKE SFLIGHT-CURRENCY,

PLANETYPE LIKE SFLIGHT-PLANETYPE,

SEATSMAX LIKE SFLIGHT-SEATSMAX,

SEATSOCC LIKE SFLIGHT-SEATSOCC,

PAYMENTSUM LIKE SFLIGHT-PAYMENTSUM,

END OF W_SFLT.

SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TEXT-001.

SELECTION-SCREEN END OF BLOCK B2.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'WS_FILENAME_GET'

EXPORTING

MASK = '., All files (.).'

MODE = '0'

TITLE = 'Get Text File'

IMPORTING

FILENAME = P_FILE

EXCEPTIONS

INV_WINSYS = 1

NO_BATCH = 2

SELECTION_CANCEL = 3

SELECTION_ERROR = 4

OTHERS = 5.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

START-OF-SELECTION.

PERFORM UPLD_FILE.

FORM UPLD_FILE.

CALL FUNCTION 'WS_UPLOAD'

EXPORTING

FILENAME = P_FILE

FILETYPE = 'DAT'

TABLES

DATA_TAB = W_SFLT

EXCEPTIONS

CONVERSION_ERROR = 1

FILE_OPEN_ERROR = 2

FILE_READ_ERROR = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

OTHERS = 10.

.

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 W_SFLT.

WRITE:/ W_SFLT-MANDT,

W_SFLT-CARRID,

W_SFLT-CONNID,

W_SFLT-FLDATE,

W_SFLT-PRICE,

W_SFLT-CURRENCY,

W_SFLT-PLANETYPE,

W_SFLT-SEATSMAX,

W_SFLT-SEATSOCC,

W_SFLT-PAYMENTSUM.

ENDLOOP.

ENDFORM.

Happy coding!!!

Jeffrey

[Former Member]

first try to read your file into an internal table...

u can use GUI_UPLOAD or UPLOAD for this....WS_UPLOAD is now obsolete.....

once u do this ....the data in the internal table can be directly inserted into the database table using the insert command....

something like insert dbtab from table itab.

gud luck

regards,

PJ

[Former Member]

Hi Sameer,

This link will show you how to upload data directly from Excel file format:

http://www.sap-img.com/abap/upload-direct-excel.htm

Hope this helps you.

Regards,

Anjali

[rainer_hbenthal]

Do not use WS_UPLOAD oder GUI_UPLOAD any more. They are not unicode-aware.

Use the appropriate method in class cl_gui_frontend_services

[Former Member]

Thanks all for your inputs. For Some reason the problem still persists and I would like to know if anyone has any comments on the following process.

As Rob suggested I have used a text file instead of a .xls file. To get a text file out of my .xls file, first I have converted it to a .csv file and then opened this .csv in a notepad. Now I saved this notepad as a .txt file. Now I enter the 'c:\load.txt' and 'asc' as the two input parameters. This time the code DUMPS on me, which is some kind of achievement since it used to throw "Error Reading file" before!!! When I tried to debug, it's dumping at the following code in UPLOAD_FILES FM..

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING ...

TABLES ...

EXCEPTIONS ...

IF sy-subrc <> 0.

MESSAGE e323 WITH tab_file-filename RAISING error_file.

ENDIF.

CHECK sy-subrc = 0.

LOOP AT data_all.

>>>>>>>>file_all = data_all-all.

APPEND file_all.

DELETE data_all.

ENDLOOP.

In the debugger, I have seen that the table data_all is having all the data from the file load.txt in a single column "all". sy-subrc at this point is 0 and the dump reads "UC_OBJECTS_NOT_CONVERTIBLE". In the error analysis, it states "The statement 'MOVE src TO dst' requires the operands 'dst' and 'src' to be convertible. Since this statement occurs in a unicode program, the special convertibility rules of unicode programs apply. In this case the following rules have been broken."

I couldn't find a relevant OSS note for this issue. I am on BW 3.5 (Unicode compatible) and I don't know whether this FM doesn't work with unicode compatible systems?!! Since the src and dst the error analysis talking about are the FM's in-built operands, it doesn't make sense!!!

As Rainer suggested, I have checked cl_gui_frontend_services, which has GUI_UPLOAD as one of the methods. So GUI_UPLOAD should work in my system, shouldn't it? Someone please share your thoughts here. Thanks a bunch.

Sam

[Former Member]

Well - a couple of things.

First - Save the file direcly from Excel as a txt file, not a csv file. When you upload it into a table, GUI_UPLOAD will expect records of equal length.

You don't want it to look like:

8/30/2003,20:03,21:51,,,4:53,6:41,7:02

8/31/2003,20:01,21:48,22:07,,4:55,6:42,7:07

9/1/2003,20:00,21:46,22:34,,4:57,6:43,7:11

You want it to look like:

8/30/2003 20:03 21:51 4:53 6:41 7:02

8/31/2003 20:01 21:48 22:07 4:55 6:42 7:07

9/01/2003 20:00 21:46 22:34 4:57 6:43 7:11

You don't need the extra spaces and 4:53 should be over 4:55 - the columns need to line up correctly.

Second - The logical structure of the text file you are uploading must be the same as the structure of the table you are uploading it into. When you save it as a text file, you will probably get extra spaces.

Rob

[Former Member]

Rob, I have tried with the exact format as you suggested but it still dumps with "UC_OBJECTS_NOT_CONVERTIBLE".

FUNCTION UPLOAD_FILES.

DATA: BEGIN OF data_all OCCURS 0,

all(1000) TYPE c,

END OF data_all.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING filename = myfname

filetype = 'ASC'

codepage = <as calculated in FM>

TABLES data_tab = data_all

EXCEPTIONS ...

IF sy-subrc <> 0.

MESSAGE e323 WITH tab_file-filename RAISING error_file.

ENDIF.

CHECK sy-subrc = 0.

LOOP AT data_all.

>>>>>>>>file_all = data_all-all.

APPEND file_all.

DELETE data_all.

ENDLOOP.

One interesting observation is that the internal table 'data_all' is populated with all the columns of my flatfile in its only column "all" (ofcourse in different rows). So my flatfile load format is fine since the FM is reading all the values in. But it dumps when the FM tries to load the internal table I provide using data_all! First I thought how can the single column in data_all be split into multiple columns/fields of my internal table. But this is how the FM is defined/coded by SAP!!!!! So it should be able to achieve the result. I don't know why it's dumping when the code looks simple and straightforward. The dump error analysis reads like "The statement 'MOVE src TO dst' requires the operands 'dst' and 'src' to be convertible. Since this statement occurs in a unicode program, the special convertibility rules of unicode programs apply. In this case the following rules have been broken."

I couldn't find a relevant OSS note for this issue. I am on BW 3.5, which is Unicode compatible. I don't know how to get this resolved. Any help is greatly appreciated. Thanks.

Sam

PS - There were three different options (text tab delimited, text unicode, text MS-DOS) while saving directly from an Excel file. I have tried everyone of them and all of them throw the same dump. I am not an abaper, but I require to load a database table using a flatfile in my BW system (BW 3.5). So I am unable to debug like an abaper.

Message was edited by: Sameer Nath

[Former Member]

Is it correct that the FM correctly uploads the file into table DATA_ALL and dumps in the loop assigning DATA_ALL to FILE_ALL?

DATA_ALL has a length of 1000. IS FILE_ALL a structure with a different length? If so, I think you will get a conversion error.

Rob

OK - I think I see what's going on. I don't think you should be calling UPLOAD_FILES. You should call GUI_UPLOAD directly, passing a table with the correct structure.

Message was edited by: Rob Burbank

[former_member181959]

Hi sameer,

Do one thing. Convert your .xsl file to a flat file. I think there is an option provided for that. Then use UPLOAD OR GUI_UPLOAD OR WS_UPLOAD fm to upload. Use DAT as format type.

With regards…

Prasad babu.