topic Re: Reading CSV file in Application Development and Automation Discussions

Reading CSV file

SG141 — Tue, 17 Jun 2008 19:06:26 GMT

I am reading CSV located on PC or unix server and loading into internal table


    LOOP AT itab1 INTO WA_itab1.
      SPLIT WA_itab1 AT ',' INTO WA_INPUT-f1
.....                                           WA_INPUT-f2  
                                                WA_INPUT-f3
                                                WA_INPUT-f4.
append wa_input to it_input.
endloop.

the above code works fine when f1,f2,f3,f4 are seperated by comma....however when the data in the field is f3 is separated by extra comma then field f4 is filled by wrong data ...how can i avoid it this situation .......

Re: Reading CSV file

Former Member — Tue, 17 Jun 2008 19:23:09 GMT

YOu cannot do anything in such situation.

Having a comma as data in a csv file will cause this issue for ever.

Regards,

ravi

Re: Reading CSV file

SG141 — Tue, 17 Jun 2008 19:48:12 GMT

Currently when a single field is split into two by a comma then it is in double " data1, data2" can we identify the way and delete ',' ????

Re: Reading CSV file

former_member156446 — Tue, 17 Jun 2008 19:55:11 GMT

Hi if data have a comma... then better go for some other format of file like a pipe '|' or tab delimited file.. that is the only option..

Re: Reading CSV file

Former Member — Tue, 17 Jun 2008 20:17:30 GMT

I made this code


types: begin of this_type,
a(15),
b(15),
c(15),
d(15),
end of this_type.

data: itab type table of string with header line.
data: itab2 type table of this_type with header line.
data: stringa(1024).
data: lenght type p.
field-symbols <fs> type any.

CALL FUNCTION 'GUI_UPLOAD'
  EXPORTING
    filename                      = 'C:\Book1.csv'
  tables
    data_tab                      = itab.

loop at itab.
do.
assign component sy-index of structure itab2 to <fs>.
if sy-subrc ne 0.
exit.
endif.
split itab at ',' into <fs> itab.
lenght = strlen( <fs> ).
lenght = lenght - 1.
if <fs>(1) = '"' and lenght ne 0.
if <fs>+lenght ne '"'.
split itab at '"' into stringa itab.
concatenate <fs> ',' stringa '"' into <fs>.
split itab at ',' into stringa itab.
endif.
endif.

enddo.
append itab2.
clear itab2.
endloop.

with this csv


1,"hola,amigos",2,3
"hello,dude",2,"1,2,3,4",3

It goes to itab2.

However I must tell you, it will only work if you don't have double quotes (") in any field.

I would say that you need to use another delimiter or tell the user to not use quotes

Re: Reading CSV file

former_member189059 — Wed, 18 Jun 2008 03:15:08 GMT

Hello Karthik,

Another option you can try is to create the csv file with a double comma

ie: each field separated by ,,

opening it in excel will have a blank row in between, but when reading it, you can modify your code as follows


    LOOP AT itab1 INTO WA_itab1.
      SPLIT WA_itab1 AT ',,' INTO WA_INPUT-f1
.....                                           WA_INPUT-f2  
                                                WA_INPUT-f3
                                                WA_INPUT-f4.
append wa_input to it_input.
endloop.

hence if a single comma appears in the data, it will not get split into the next cell

Re: Reading CSV file

Former Member — Fri, 27 Jun 2008 14:10:03 GMT

Dear Karthik,

Have you got the solution for this problem you have raised . If so could you please let me know the solution sothat i can implement. Iam facing exactly the same situation ........pleae do let meknow in the .csv

file when you are trying to upload how you have handled

in the scenario where in a particular field where you have data as below

A B c D

101 raja cleark,supervisor 500

102 raju cleark 200

here when iam uploading the data has to go as

cleark,supervisor only .......but since when we are using split at .........this data is getting splitted at two cleark and supervisor .......which is wrong .......please help m

little urgent .

regards

madhuri

Re: Reading CSV file

Former Member — Fri, 27 Jun 2008 14:41:20 GMT

when creating CSV files make sure that your data doesnt contain any , in them....it gives wrong data while reading as internally it is stored as "data1, data2".

But we can overcome this by using this

when creating itself... u concatenate the data into field string separated by comma

concatenate data1 ',' data2 into string

instead use

concatenate data1 ' "," ' data2 into string

this should solve ur problem

regards

padma

Re: Reading CSV file

SG141 — Fri, 27 Jun 2008 19:10:24 GMT

Hi,

When my data contains ' ,' in it's content, it will be encapsulated by " ",

eg it will be like test1," data1,date2",test3 i wrote below logic and it works fine.

str1 = test1," data1,date2",test3

split str1 at ',", into str1 str2.

(now str1 has test1 and str2 has data1,data2",test3.)

split str2 at '",' into str2 str3.

(now str2 has data1, data2 and str3 has test3.)

replace , with ' ' into str2. ( to get ridff comma)

concatenate str1 str2 str3 into str1 separated by ','.

i used the above logic to get ridsff unwanted commas.

Edited by: Karthik on Jun 27, 2008 10:50 PM