Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Uploading Excel File that contains Commas, Tabs, and Carriage Returns

Former Member
0 Kudos
1,645

Do any of you know a function module to use to upload excel/text files into SAP without using tab-delimited or comma-delimited.

We have some formatting data that has shudder commas, tabs/carriage-returns which screws up the data when importing every single time. Sigh...

I have tried (and failed) with these function modules:

GUI_UPLOAD

Failed due to commas/tabs already present in data

TEXT_CONVERT_XLS_TO_SAP

Failed just by giving error 'cannot process file'

DATA: FILENAME TYPE  STRING VALUE 'C:\HEAT01.XLS'.

TYPE-POOLS: truxs.

DATA: i_text_data       TYPE truxs_t_text_data,
      v_filename_string LIKE  RLGRAP-FILENAME VALUE 'C:\HEAT01.XLS'.

START-OF-SELECTION.

CALL FUNCTION 'GUI_UPLOAD'
  EXPORTING
    FILENAME                      = FILENAME
    FILETYPE                      = 'DAT'
    HAS_FIELD_SEPARATOR           = 'X'
*   HEADER_LENGTH                 = 0
*   READ_BY_LINE                  = 'X'
*   DAT_MODE                      = ''
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
*   CHECK_BOM                     = ' '
*   VIRUS_SCAN_PROFILE            =
*   NO_AUTH_CHECK                 = ' '
* IMPORTING
*   FILELENGTH                    =
*   HEADER                        =
  TABLES
    DATA_TAB                      = i_text_data
 EXCEPTIONS
   FILE_OPEN_ERROR               = 1
   FILE_READ_ERROR               = 2
   NO_BATCH                      = 3
   GUI_REFUSE_FILETRANSFER       = 4
   INVALID_TYPE                  = 5
   NO_AUTHORITY                  = 6
   UNKNOWN_ERROR                 = 7
   BAD_DATA_FORMAT               = 8
   HEADER_NOT_ALLOWED            = 9
   SEPARATOR_NOT_ALLOWED         = 10
   HEADER_TOO_LONG               = 11
   UNKNOWN_DP_ERROR              = 12
   ACCESS_DENIED                 = 13
   DP_OUT_OF_MEMORY              = 14
   DISK_FULL                     = 15
   DP_TIMEOUT                    = 16
   OTHERS                        = 17
          .
    IF sy-subrc NE 0.
      MESSAGE S000(ZC) WITH 'Error uploading file'.
    endif.

CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
  EXPORTING
    I_FIELD_SEPERATOR          = 'X'
*   I_LINE_HEADER              =
    I_TAB_RAW_DATA             = i_text_data
    I_FILENAME                 = v_filename_string
  TABLES
    I_TAB_CONVERTED_DATA       = ZCHAR[]
* 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.

TEXT_CONVERT_CSV_TO_SAP

Failed due to commas in one of my formatting fields (and possible carriage returns)?

This caused data to go in the wrong fields

ALSM_EXCEL_TO_INTERNAL_TABLE

Failed with a upload_ole error despite checking the filename and input columns multiple times and making sure file was closed.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  exporting
    filename                = v_filename_string
    i_begin_col             = 1
    i_begin_row             = 1
    i_end_col               = 71
    i_end_row               = 50000
  tables
    intern                  = ZCHAR
  exceptions
    inconsistent_parameters = 1
    upload_ole              = 2
    others                  = 3.

if sy-subrc NE 0.
*  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
endif.

Thanks for all your help,

Brian Waugh

Programmer/Analyst

1 ACCEPTED SOLUTION

Amarpreet
Active Participant
0 Kudos
253

how about reading in the raw data into an internal table without using has_field_separator .

and then manually SPLITing them into the fields u want .

will that help ?

4 REPLIES 4

Amarpreet
Active Participant
0 Kudos
254

how about reading in the raw data into an internal table without using has_field_separator .

and then manually SPLITing them into the fields u want .

will that help ?

former_member222709
Contributor
0 Kudos
253

Hi,

There is a workaround that I can suggest. You can download the data as a Text File using 'GUI_DOWNLOAD' and then open it in Excel where the delimiter can be specified.

Regards,

Pranav.

Former Member
0 Kudos
253

Thanks for your replys.

To anyone who looks for this via google I thought i'd share what solution I found:

In excel I was only aware of three kinds of delimiters: commas, tabs, and spaces. However, I fixed my problem above by going to regional and language setting on the control panel of my windows xp machine and changing list separator to '$' or any other special character I want to delimit my files as. This automatically applies settings to excel to change the delimiter. So after this I simply resaved my excel file as a .CSV and it contains $ signs whereby I can now delimit my data properly upon import into SAP.

Former Member
0 Kudos
253

I forgot to mention one other thing that was the actual problem solver.

I had to take out the carriage returns/line feeds in excel BEFORE I imported it.

I found the CLEAN(A1) function. I used =CLEAN(A1) in a new column adjacent to the "unclean" column and then pasted the "values only" into a separate column. This removed the extra non-printing characters. I then deleted the unclean and formula columns. Many thanks.

Hope this information helps someone.

Edited by: bwaugh06 on Aug 31, 2011 5:16 PM