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: 

Question about reading csv file into internal table

former_member220801
Participant
0 Kudos

Some one (thanks those nice guys!) in this forum have suggested me to use FM KCD_CSV_FILE_TO_INTERN_CONVERT to read csv file into internal table. However, it can be used to read a local file only.

I would like to ask how can I read a CSV file into internal table from files in application server?

I can't simply use SPLIT as there may be comma in the content. e.g.

"abc","aaa,ab",10,"bbc"

My expected output:

abc

aaa,ab

10

bbb

Thanks again for your help.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Gundam,

Try this...


OPEN DATASET dsn FOR input IN TEXT MODE ENCODING DEFAULT.
DO.
  READ DATASET dsn INTO record.
  SPLIT record AT '","' INTO field1 record.
  SPLIT record AT '",'  INTO field2 record.
  SPLIT record AT ',"'  INTO field3 field4.
  CONCATENATE '"' field1 INTO field1 IN CHARACTER MODE.
  CONCATENATE '"' field4 INTO field4 IN CHARACTER MODE.
  WRITE : / field1, field2, field3, field4.
ENDDO.

6 REPLIES 6

Former Member
0 Kudos

Hi Gundam,

Try this...


OPEN DATASET dsn FOR input IN TEXT MODE ENCODING DEFAULT.
DO.
  READ DATASET dsn INTO record.
  SPLIT record AT '","' INTO field1 record.
  SPLIT record AT '",'  INTO field2 record.
  SPLIT record AT ',"'  INTO field3 field4.
  CONCATENATE '"' field1 INTO field1 IN CHARACTER MODE.
  CONCATENATE '"' field4 INTO field4 IN CHARACTER MODE.
  WRITE : / field1, field2, field3, field4.
ENDDO.

0 Kudos

Hi Wenceslaus G,

Thanks for your reply first.

Your method seems only solve one particular case only

my line may be

"abc","aaa,ab",10,"bbc"

OR

"a,bc","aaaab",10,"bbc"

OR

"abc","aaa,ab",10,"b,bc"

OR

"abc","aaaab",10,"bbc"

etc....

My point is that "," may be the field content in any fields and the actual no. of fields may be up 50 or more...

0 Kudos

Hi Gundam,

I think that my code works with that too..

Please send a sample record for which it may fail. I have splitted the string at different places with "," OR ," ...

I think it may work with your records.

But the double quotes may not be right. YOu can remove or replace them with for all the fields like:

REPLACE ALL OCURRENCES OF '"' IN field1 WITH SAPCE.

CONDENSE field1.

CONCATENATE '"' field1 '"' INTO field1.

Unless your table content follow a particular pattern you might not be able to split it programmatically.

Regards,

Wenceslaus.

0 Kudos

I think Wenceslaus' solution is correct; if you don't know how many fields you will have, you can use the "SPLIT f AT g INTO TABLE itab" variant of SPLIT. Do F1 on split for more information.

Rob

0 Kudos

It will be failed if

"abc",10,"aaa,ab","bbc"

May be my question is not clear enough (I am sorry about that)

My line format is

1) each field is separated by comma

2) for character value, it is double quoted, e.g "aa"

3) for numeric value, it is not quoted, e.g. 10

4) each field may be either character value, or numeric value, varied line by line, e.g.

1st line: "abc",10,"aaa,ab","bbc"

2nd line: "ab","aaa",21,"bbc"

Thanks!

0 Kudos

Hi Gundam,

Try this code. I have made a custom parser to read the details in the record and split them accordingly. I have also tested them with your provided test cases and it work fine.


OPEN DATASET dsn FOR input IN TEXT MODE ENCODING DEFAULT.
DO.
READ DATASET dsn INTO record.
  PERFORM parser USING record.
ENDDO.

*DATA str(32) VALUE '"abc",10,"aaa,ab","bbc"'.
*DATA str(32) VALUE '"abc","aaa,ab",10,"bbc"'.
*DATA str(32) VALUE '"a,bc","aaaab",10,"bbc"'.
*DATA str(32) VALUE '"abc","aaa,ab",10,"b,bc"'.
*DATA str(32) VALUE '"abc","aaaab",10,"bbc"'.

FORM parser USING str.
DATA field(12).
DATA field1(12).
DATA field2(12).
DATA field3(12).
DATA field4(12).
DATA cnt TYPE i.
DATA len TYPE i.
DATA temp TYPE i.
DATA start TYPE i.
DATA quote TYPE i.
DATA rec_cnt TYPE i.


len = strlen( str ).
cnt = 0.
temp = 0.
rec_cnt = 0.
DO.
*  Start at the beginning
  IF start EQ 0.
    "string just ENDED start new one.
    start = 1.
    quote = 0.
    CLEAR field.
  ENDIF.
  IF str+cnt(1) EQ '"'.  "Check for qoutes
    "CHECK IF quotes is already set
    IF quote = 1.
      "Already quotes set
      "Start new field
      start = 0.
      quote = 0.
      CONCATENATE field '"' INTO field.
      IF field IS NOT INITIAL.
        rec_cnt = rec_cnt + 1.
        CONDENSE field.
        IF rec_cnt EQ 1.
          field1 = field.
        ELSEIF rec_cnt EQ 2.
          field2 = field.
        ELSEIF rec_cnt EQ 3.
          field3 = field.
        ELSEIF rec_cnt EQ 4.
          field4 = field.
        ENDIF.
      ENDIF.
*      WRITE field.
    ELSE.
      "This is the start of quotes
      quote = 1.
    ENDIF.
  ENDIF.

  IF str+cnt(1) EQ ','. "Check end of field
    IF quote EQ 0. "This is not inside quote end of field
      start = 0.
      quote = 0.
      CONDENSE field.
*      WRITE field.
      IF field IS NOT INITIAL.
        rec_cnt = rec_cnt + 1.
        IF rec_cnt EQ 1.
          field1 = field.
        ELSEIF rec_cnt EQ 2.
          field2 = field.
        ELSEIF rec_cnt EQ 3.
          field3 = field.
        ELSEIF rec_cnt EQ 4.
          field4 = field.
        ENDIF.
      ENDIF.
    ENDIF.
  ENDIF.
  CONCATENATE field str+cnt(1) INTO field.
  cnt = cnt + 1.
  IF cnt GE len.
    EXIT.
  ENDIF.
ENDDO.

WRITE: field1, field2, field3, field4.
ENDFORM.

Regards,

Wenceslaus.