Extra Comma in CSV

[Former Member]

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??

[former_member189059]

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.

[Former Member]

Unfortunately, the file is coming from some legacy.

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

Thanks

Pranu

[Former Member]

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.

[nabheetscn]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[former_member183804]

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

[Former Member]

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.