Import CSV with html input

vanness145 — Thu, 23 Aug 2018 09:27:26 GMT

Hi guys, I am trying to import a CSV which some of the columns contain html type. Currently, I am using this feature from PowerBI to handle this but I want to try to do it using SAS University Edition. Any expert can advice what similar function as shown below that I can use in SAS?

Re: Import CSV with html input

ChrisNZ — Fri, 24 Aug 2018 03:31:35 GMT

Such a function does not exist. It would be nice to have actually.

SAS has no way that I know to treat multiple records as one, except by using RECFM to treat the whole file as a stream (i.e. ignore all EoL markers).

It doesn't mean it can't be done though, but it's clumsy:

data _null_;
  file "%sysfunc(pathname(work))\t.txt";
  put 'aaaa,"bb' / 'bb","cc,cc" ,"dd' / 'dd", eeee';
  put '1,2,3,,5';
run;     

data T1;  %* Standard behaviour;
  infile "%sysfunc(pathname(work))\t.txt" dsd missover ;
  input (A B C D E) ($);
run;

data T2;  %* Attempt to include EOL as delimiter;
  infile "%sysfunc(pathname(work))\t.txt" dsd missover dlm='2c0A0D'x ;
  input (A B C D E) ($);
run;

data T3; %* Read values one by one and complete unclosed quotes using the next record;
  array ARR[*] $ A B C D E ;
  infile "%sysfunc(pathname(work))\t.txt" dsd missover dlm='2c0A0D'x ;
  do I=1 to 5;
    input ARR[I] $ @ ;
    if first(ARR[I]) = '"' & first(reverse(trim(ARR[I]))) ^= '"' then do;
      input ;
      input TMP $ @;
      ARR[I]=dequote(cats(ARR[I],TMP));
    end;
  end;
  drop I TMP;
run;

A	B	C	D	E
aaaa	"bb
bb"	cc,cc	"dd
dd"	ee
1	2	3		5

A	B	C	D	E
aaaa	"bb
bb"	cc,cc	"dd
dd"	ee
1	2	3		5

A	B	C	D	E
aaaa	bbbb	cc,cc	dddd	ee
1	2	3		5

Re: Import CSV with html input

ChrisNZ — Fri, 24 Aug 2018 03:37:23 GMT

Having said that, HTML files should be treated as a stream. EOL means nothing for these files.

Tags are the main delimiter, then more parsing.

How does CSV importation logic make sense for HTML files?

topic Re: Import CSV with html input in Microsoft Integration with SAS

Import CSV with html input

Re: Import CSV with html input

Re: Import CSV with html input