topic Re: Import .txt file and convert to .csv file in SAS Programming

Import .txt file and convert to .csv file

Babloo — Sun, 13 Nov 2016 07:51:37 GMT

Appreciate if someone guide me with the program to import .txt file which has records as follows.

Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/sasuserl 1419.00 503.79 65% 48937 1% /sasuser

After import, I want to export to .csv as follows.

Filesystem	GB blocks	Free	%Used	Iused	%Iused	Mounted on
/dev/sasuserl	1419.00	503.79	65%	48937%	1%	/sasuser

Thanks for any help you offer me.

Re: Import .txt file and convert to .csv file

Kurt_Bremser — Sun, 13 Nov 2016 08:32:24 GMT

Use list input without the DSD option to read; use list output with DLM=',' to create the csv file.

For the percent values, use the PERCENTw.d format to read and write.

All in all a very simple task.

Re: Import .txt file and convert to .csv file

Patrick — Sun, 13 Nov 2016 09:28:14 GMT

Both .csv and .txt are text files. They just have different delimiters. So in your case you could simply read the source text file into the input buffer, exchange the source delimiter with a comma and then write the result straight back to the output file. There is no need to map the data to SAS variables and input source strings into SAS numeric variables.

/* create source text file with delimiter '|' (tab or whatever in your real data) */
filename have temp;
data _null_;
 file have;
 infile datalines;
 input;
 put _infile_;
 datalines;
Filesystem|GB blocks|Free|%Used|Iused|%Iused|Mounted on
/dev/sasuserl|1419.00|503.79|65%|48937|1%|/sasuser
;
run;

/* read source file "want" into input buffer and replace the source file delimiter (tab?) with comma */
filename want temp;
data _null_;
 file want;
 infile have;
 input;
 _infile_=translate(_infile_,',','|');
 put _infile_;
 /* putlog just here to see in sample code what gets written to file "want" */
 putlog _infile_;
run;

And in case your source text file doesn't have a delimiter and data is just written out positional with blanks then the following code version could deal with that.

/* create source text file */
filename have temp;
data _null_;
  file have;
  infile datalines;
  input;
  put _infile_;
  datalines;
Filesystem    GB blocks Free   %Used Iused %Iused Mounted on
/dev/sasuserl 1419.00   503.79 65%   48937 1%     /sasuser
;
run;

/* read source file "want" into input buffer and replace selected blanks with comma */
filename want temp;
data _null_;
  file want;
  infile have;
  input;
  _infile_=prxchange('s/(?!\sblocks|\son|\s+$)\s+/,/oi',-1,_infile_);
  put _infile_;
  /* putlog just here to see in sample code what gets written to file "want" */
  putlog _infile_;
run;

Re: Import .txt file and convert to .csv file

Babloo — Sun, 13 Nov 2016 09:44:16 GMT

Thank you for quick response. However, I'm unable to view the file (#LN00019) which was created in second data step.See the log below. I'm in a need output .csv file after reading the input .txt file. Also in your first data step, you've hard coded the values which was in input .txt. Could you please tell me how to accomplish this task by reading the .txt file without hardcoding the values in it?

NOTE: The file WANT is:
      Filename=/saswork/v9.4/SAS_work97110001A_ksnsnk02/#LN00019,
      Owner Name=rxxx,Group Name=sas,
      Access Permission=-rw-rw-r--,
      Last Modified=13Nov2016:14:50:55

NOTE: The infile HAVE is:
      Filename=/saswork/v9.4/SAS_work97110001A_ksnsnk02/#LN00018,
      Owner Name=rxxx,Group Name=sas,
      Access Permission=-rw-rw-r--,
      Last Modified=13Nov2016:14:50:55,
      File Size (bytes)=162

Re: Import .txt file and convert to .csv file

Patrick — Sun, 13 Nov 2016 10:13:53 GMT

I'm unable to view the file (#LN00019) which was created in second data step

That's why I've also used "putlog" so you can see the output in the log.

For your real code you want of course to write the file to a permanent location and not to WORK so your code should look like:

filename have '<input path>/<name of source text file>';
filename want '<output path>/<name of target csv file>';
data _null_;
  file want;
  infile have;
  input;
  _infile_=prxchange('s/(?!\sblocks|\son|\s+$)\s+/,/oi',-1,_infile_);
  put _infile_;
run;
filename have clear;
filename want clear;

Re: Import .txt file and convert to .csv file

Patrick — Sun, 13 Nov 2016 10:22:31 GMT

Or even this way:

data _null_;
  file '<output path>/<name of target csv file>';
  infile '<input path>/<name of source text file>';
  input;
  _infile_=prxchange('s/(?!\sblocks|\son|\s+$)\s+/,/oi',-1,_infile_);
  put _infile_;
run;

BTW: If you're the one creating the initial report via Unix command then you could of course also pipe the result directly to a RegEx and create the .csv directly without any SAS involved.

Re: Import .txt file and convert to .csv file

Shmuel — Sun, 13 Nov 2016 10:43:53 GMT

The most simple code to convert text file (blank delimiter) into csv (comma delimiter) is by:

data _null_;

length a_row $100;

infile '... input path and filename.txt' truncover;

input a_row $;

a_row = translate(compbl(a_row), ',' , ' ');

a_row = compress(a_row, '%');

file '... output path and filename.csv';

put a_row ;

run;