topic Re: Extra Comma in CSV in Application Development and Automation Discussions

Extra Comma in CSV

Former Member — Thu, 21 Oct 2010 02:48:21 GMT

Hello,

I am reading the data from CSV File.

One of the field is description and the description has a comma within.

My internal table is not populated properly coz of this.

I am splitting the raw data at ','.

eg: description = Herald Group Activity,Quarter 1

This is entire description but the comma before Quarter is a part of description and not the delimeter.

Any work around??

Re: Extra Comma in CSV

former_member189059 — Thu, 21 Oct 2010 02:58:09 GMT

Hello,

The workaround would be to not use a simple csv format.

You can either try using some rare symbols such as | or ~

Alternatively, you can use two commas ie: ,, as the separator.

Re: Extra Comma in CSV

Former Member — Thu, 21 Oct 2010 03:00:51 GMT

Unfortunately, the file is coming from some legacy.

If the delimeter is to be changed, it will be a long process.

Thanks

Pranu

Re: Extra Comma in CSV

Former Member — Thu, 21 Oct 2010 03:13:31 GMT

Hi Pranu,

If you are using a Description in the file then it is very difficult to handle in the .CSV file.

If your data contains , then the best workaround is to use a TAB delimited file.

Regards,

Immanuel.

Re: Extra Comma in CSV

nabheetscn — Thu, 21 Oct 2010 03:26:27 GMT

Text fields always have such a problem. It will be good to use a tab delimited file or use the diffenet separator as suggested above.

Nabheet

Re: Extra Comma in CSV

Former Member — Thu, 21 Oct 2010 03:33:27 GMT

Hello Pranu,

I assume you are reading the .CSV into an internal table, looping at it and splitting each record at comma. You may also want to consider reading fields by their length in this scenario. For example.


wa_processed-field1 = wa_raw(10).
wa_processed-field2 = wa_raw+10(5).
wa_processed-field3 = wa_raw+15(3).

Probably not the cleanest method. But in this scenario, we dont have a lot of options available.

Regards,

Jinesh.

Re: Extra Comma in CSV

Former Member — Thu, 21 Oct 2010 03:38:07 GMT

In ideal case if all the fields are present in the file, it could be used.

But my file is having 204 fields and not always all fields are filled.

I guess changing the delimeter will be the right option but the only problem is it will have to go through long process and approvals

Pranu

Re: Extra Comma in CSV

Former Member — Thu, 21 Oct 2010 03:46:02 GMT

Hi Pranu,

You tell the Legacy file creation system to put ' " ' at the begining and at the end of the value which contains comma. Then consider the ' " ' at the time of spliting. You will get lots of code in the net for this kind of spliting. In MS Excel also do the same thing when it is creating the .csv file.

Regards,

Amitava

Re: Extra Comma in CSV

former_member183804 — Fri, 26 Nov 2010 18:38:56 GMT

Hello Pranu,

the , as content shall be escaped by quotation marks. I have contributed a [sample|http://wiki.sdn.sap.com/wiki/display/Snippets/ConvertInternalTabletoCSVSpreadsheetformat%286.40and+higher%29] in the code gallery recently. Possibly you can use the sample as it is or derive your custom solution from it.

Regards

Klaus

Re: Extra Comma in CSV

Former Member — Tue, 30 Nov 2010 19:15:52 GMT

Below FM resolved the issue.



 CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = v_filename
      i_begin_col             = 1
      i_begin_row             = 2
      i_end_col               = 256
      i_end_row               = 9999
    TABLES
      intern                  = ta_kcde_cells
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.
  IF sy-subrc <> 0.
    MESSAGE e079(z1) WITH 'Error opening file'.
  ENDIF.

  CHECK NOT ta_kcde_cells[] IS INITIAL.
  SORT ta_kcde_cells BY row col.
*** Collect data from the excel file
  LOOP AT ta_kcde_cells.
    MOVE : ta_kcde_cells-col TO da_index.

    IF da_index < 205.
      ASSIGN COMPONENT da_index OF STRUCTURE wa_file TO <fs>.
      MOVE : ta_kcde_cells-value TO <fs>.
      IF da_index = 1 OR da_index = 8.
        TRANSLATE <fs> TO UPPER CASE.
      ENDIF.
    ENDIF.

    AT END OF row.
      IF wa_file IS INITIAL.
        EXIT.
      ENDIF.
      APPEND wa_file to i_file.
      CLEAR wa_file.
    ENDAT.
  ENDLOOP.