Malformed CSV quoting

0
votes

I pass data from SAS to Python using CSV format. Have a problem with a quoting format SAS uses. Strings like "480 КЖИ" ОАО aren't quoted, but Python csv module thinks they're.

dat = ['18CA4,"480 КЖИ" ОАО', '1142F,"""Росдорлизинг"" Российская дор,лизинг,компания"" ОАО"']
for i in csv.reader(dat):
    print(i)
>>['18CA4', '480 КЖИ ОАО']
>>['1142F', '"Росдорлизинг" Российская дор,лизинг,компания" ОАО']

The 2nd string is fine, but I need 480 КЖИ ОАО string to be "480 КЖИ" ОАО. Don't find such an option in csv module. Maybe it's possible to force proc export to quote all " chars?

UPD: Here's a similar problem Python CSV : field containing quotation mark at the beginning UPD2: @Quentin have asked for details. Here they're: I have SAS8.2 connected to 9.1 server. I download custom format data from server side with proc format cntlout=..; proc download... So i get a dictionary-like dataset <key>, <value>. Then i pass this dataset in CSV format using proc export via DDE interface to Python. But proc export quotes only strings which include delimiter (comma) as i understand. So i think, i need SAS to quote quotation marks too or Python to unquote only those strings which include commas.

UPDATE: switching from proc export via DDE to direct reading of dataset with a modified SAS7BDAT Python module hugely improved performance. And i got rid of the problem above.

1
pythoncsvimportsasexport
If you want advice from the SAS side, suggest you add more to the question, describing the values you have in SAS dataset, and the values you would like to write to the CSV. - Quentin
How did you ask SAS to create the CSV file? SAS would normally place quotes around a string with embedded quotes. So your problem value would appear in the CSV file as """480 КЖИ"" ОАО". - Tom
@Quentin i've added details - Winand
@Tom maybe i'm wrong somewhere, but proc export quotes string for me only if it includes field separator (comma). DDE really passes strings like ...,18CA4,"480 КЖИ" ОАО and csv module gets confused.)) - Winand
Export does the same thing as a data step since it just generates a data step for you. Are you talking about using EXPORT to write to Excel or to CSV? If you write to a CSV file then the extra quotes are there. Also don't look at the CSV file using Excel since it will transform the data. Look at it with a text editor. - Tom

1 Answers

0
votes

SAS will add extra quotes if the value has quotes in it already.

data _null_;
  file log dsd ;
  string='"480 КЖИ" ОАО';
  put string;
run;

Generates this result:

"""480 КЖИ"" ОАО"

Perhaps the quotes are being removed at some other point in the flow from SAS to Python? Try saving the CSV file to a disk and having Python read from the disk file.