1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
*(Q) How can I read comma delimited fields when quoted strings contain commas?

* (A) Posted by rlevesque@videotron.ca to SPSSX-L on 2001/11/03.

* (Note: use FILE HANDLE if records lenght are too large).

* Read the data as a single string.
DATA LIST FIXED /str1 1-80 (A).
BEGIN DATA
1,"6159905","XYZ Agency, City Middle School",0,,,0,1
2,"4444","ABC Company,, My City,",1,2,3,4,5
END DATA.
LIST.

* replace the commas delimiting fields by ~ (or any other char not already present in the data).
* commas inside quotes are left unchanged.
COMPUTE #inside=-1.
LOOP cnt=1 TO 80.
IF SUBSTR(str1,cnt,1)="," & #inside=-1 SUBSTR(str1,cnt,1)="~".
IF SUBSTR(str1, cnt,1)='"' #inside=#inside*-1.
END LOOP.

*Write the new text file where fields are separated by ~.
WRITE OUTFILE='c:\\temp\\data.txt' /str1.
EXECUTE.

* Read back the new text file.
NEW FILE.
DATA LIST FILE='c:\\temp\\data.txt' LIST('~') 
	/ id(f8.2) nb(A10) addr(A50) var1(f8.2) var2(f8.2) var3(f8.2) var4(f8.2) var5(f8.2). 
LIST.