topic Re: Uploading CSV file into internal table in Application Development and Automation Discussions

Uploading CSV file into internal table

Former Member — Mon, 29 Jun 2009 13:07:47 GMT

Hi,

I want to upload a CSV file into internal table.The flat file is having values as below:

'AAAAA','2003-10-11 07:52:37','167','Argentina',NULL,NULL,NULL,NULL,NULL,'MX1',NULL,NULL,'AAAA BBBB',NULL,NULL,NULL,'1',NULL,NULL,'AR ',NULL,NULL,NULL,'ARGENT','M1V','MX1',NULL,NULL,'F','F','F','F','F',NULL,'1',NULL,'MX','MMI ',NULL

'jklhg','2004-06-25 08:01:57','456','hjllajsdk','MANAGUA ',NULL,NULL,'265-5139','266-5136 al 38','MX1',NULL,NULL,'hjgkid GRÖBER','sdfsdf dfs asdfsdf 380 ad ased,','200 as ads, sfd sfd abajao y 50 m al sdf',NULL,'1',NULL,NULL,'NI ',NULL,NULL,NULL,'sdfdfg','M1V','dds',NULL,NULL,

Here I can not even split at ',' because some of the values are having value like NULL and some have values with comma too,

The delimiter is a quote and the separator is a comma here.

Can anyone help on this?

Thanks.

Edited by: Ginger on Jun 29, 2009 9:08 AM

Re: Uploading CSV file into internal table

Former Member — Mon, 29 Jun 2009 18:11:51 GMT

As long as there can be a comma in a text literal you are right that the spilt command doesn't help. However there is one possibility how to attack this under one assumption:

- A comma outside a text delimiter is always considered a separator

- A comma inside a text delimiter is always considered a comma as part of the text

You have to read you file line by line and then travel along the line string character by character and setting a flag or counter for the text delimiters:

e.g.

"Text","Text1, Text2",NULL,NULL,"Text"

String Index 1: EQ " => lv_delimiter = 'X'

String Index 2: EQ T => text literal (because lv_delimiter = 'X')

String Index 3: EQ e => text literal (because lv_delimiter = 'X')

String Index 4: EQ x => text literal (because lv_delimiter = 'X')

String Index 5: EQ t => text literal (because lv_delimiter = 'X')

String Index 6: EQ " => lv_delimiter = ' ' (because it was 'X' before)

String Index 7: EQ , => This is a separator because lv_delimiter = ' '

String Index 8: EQ " => lv_delimiter = 'X' (because it was ' ' before)

String Index 9: EQ T => text literal (because lv_delimiter = 'X')

String Index 10: EQ e => text literal (because lv_delimiter = 'X')

String Index 11: EQ x => text literal (because lv_delimiter = 'X')

String Index 12: EQ t => text literal (because lv_delimiter = 'X')

String Index 13: EQ 1 => text literal (because lv_delimiter = 'X')

String Index 14: EQ , => text literal (because lv_delimiter = 'X')

String Index 15: EQ T => text literal (because lv_delimiter = 'X')

Whenever you hit a 'real' separator (lv_delimiter = ' ') you pass the value of the string before that up to the previous separator into the next structure field.

This is not an easy way to do it, but if you might have commas in your text literal and NULL values I gues it is probably the only way to go.

Hope that helps,

Michael

Re: Uploading CSV file into internal table

Former Member — Mon, 29 Jun 2009 23:51:36 GMT

Hi Ginger did you try to split the record like this:

split <zcat_ingline> at ',' into wa_zcat_ingredients-sand_size
                                       wa_zcat_ingredients-cat_barcode
                                       wa_zcat_ingredients-article_no
                                       wa_zcat_ingredients-upsell_code
                                       lv_cating_qty_conv
                                       wa_zcat_ingredients-uom
                                       wa_zcat_ingredients-no_move
                                       wa_zcat_ingredients-description.

Edited by: TMM on Jun 29, 2009 7:52 PM

Your spaces should just move to the right field.

Edited by: TMM on Jun 29, 2009 7:54 PM

Re: Uploading CSV file into internal table

Former Member — Tue, 30 Jun 2009 02:52:57 GMT

Hi,


FIELD-SYMBOLS: <field> TYPE ANY.
  DATA : BEGIN OF i_dload OCCURS 0 ,
         dload(1000) ,
       END OF i_dload .

LOOP AT it_tab.
        DO.
          ASSIGN COMPONENT sy-index OF STRUCTURE it_tab TO <field>.
          IF sy-subrc <> 0.
            EXIT.
          ENDIF.
* Look for Commas in the field value.  If it exists, put quotes around
* value so that the file opens correctly with all the columns aligned
* in Excel.
          SEARCH <field> FOR c_cma IN CHARACTER MODE.   u201C(c_cma for ',')
* If search for commas are successful.
          IF sy-subrc = 0.
            CONCATENATE '"' <field> '"' INTO <field> IN CHARACTER MODE.
          ENDIF.
          IF sy-index = 1.
            i_dload-dload = <field>.
            SHIFT: i_dload-dload  LEFT DELETING LEADING space.
          ELSE.
* Put Comma as a separator for values in IT_DLOAD internal table
            SHIFT: i_dload-dload  LEFT DELETING LEADING space.
            CONCATENATE i_dload-dload <field> INTO i_dload-dload
                        SEPARATED BY c_cma IN CHARACTER MODE.     " c_cma for ','
          ENDIF.
        ENDDO.

        it_objbin = i_dload-dload.
        APPEND it_objbin. CLEAR it_objbin.

      ENDLOOP.

Thanks,

Krishna