Import .txt file and convert to .csv file

Reply
Super Contributor
Posts: 422

Import .txt file and convert to .csv file

[ Edited ]

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.

Esteemed Advisor
Posts: 6,661

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,836

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

[ Edited ]

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;

Super Contributor
Posts: 422

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

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

 

Respected Advisor
Posts: 3,836

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

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;

 

Respected Advisor
Posts: 3,836

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

[ Edited ]

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.

Super User
Posts: 1,235

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

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;

Ask a Question
Discussion stats
  • 6 replies
  • 357 views
  • 3 likes
  • 4 in conversation