.csv File Export Adding an Extra Blank Line

[paulrockliffe]

Enterprise Guide 5.1, I'm using (and only able to use) the Export as Step in Project Wizard.  It's started adding an extra blank line at the end of the .csv file.

The result is that when I import the data into Excel I get a "(blank)" option in my slicers, which is untidy, but all my page headings and explanations of what's visible is linked to a filtered pivot table that takes a value if there's only one value in the column - the "(blank)" shows as a second option here and breaks my formulas.

I've tested the issue and I can open the .csv files in Notepad and delete the extra line, once I've done this for 7 .csv files and updated the data in Excel the issue goes away, so it looks like the issue is the export, rather than the import. 

This has just started today, having been fine in the past.  The manual fix isn't practical as I'm exporting 7 files and running the process 20 times to get different data cuts, so I need a fix!

Thanks

[heffo]

Stupid that it's not really possible to do it in an easy fashion, but I created a macro program to fix that. Minus one last trailing space on the last row. Hm... 

Anyway:

*Create the macro program;
%macro exportWithoutExtraLine(lib_name,ds_name,export_file);
	*Get the variables in the order of the data set.;
	proc sql noprint ;
		select name into : all_vars separated by ","
		from dictionary.columns
		where upcase(libname) = upcase("&lib_name") and upcase(memname) = upcase("&ds_name") 
		order by varnum;
	quit;

	*Export the file to CSV.;
	data _null_;
	     file "&export_file" recfm=n;
	     length row $1000;
	     set &lib_name..&ds_name;
	     row = catx( ",", &all_vars);
	     if _n_ > 1 then put "0D0A"x +(-1); *Deal with trailing spaces... ;
	     put row +(-1); *Deal with trailing spaces... ;
	run;
%mend exportWithoutExtraLine;

*Call the macro program with your parameters, there is a comma between the libname and the data set name! ;
%exportWithoutExtraLine(sashelp,baseball,G:\temp\file.csv);

[Tom]

Your description of the problem is a little unclear to me.

Are you saying that blank lines are appearing randomly in your exports from Excel to CSV files?

I really doubt that Excel is doing that.  It is more likely that there are humans making changes to the files that are inserting blank lines.

Are you asking how to delete observations from SAS datasets where all variables are missing?

If you know how many variables are in the dataset then it is easy.  So if for example your dataset has 12 variables.

data want;
  set have ;
  if nmiss(of _all_) = 12 then delete;
run;

[ballardw]

---

@paulrockliffe wrote:

Enterprise Guide 5.1, I'm using (and only able to use) the Export as Step in Project Wizard.  It's started adding an extra blank line at the end of the .csv file.

The result is that when I import the data into Excel I get a "(blank)" option in my slicers, which is untidy, but all my page headings and explanations of what's visible is linked to a filtered pivot table that takes a value if there's only one value in the column - the "(blank)" shows as a second option here and breaks my formulas.

I've tested the issue and I can open the .csv files in Notepad and delete the extra line, once I've done this for 7 .csv files and updated the data in Excel the issue goes away, so it looks like the issue is the export, rather than the import. 

This has just started today, having been fine in the past.  The manual fix isn't practical as I'm exporting 7 files and running the process 20 times to get different data cuts, so I need a fix!

Thanks

---

So how do you "import the data into Excel"?

If you have an Excel "template" of some sort then you may well have phantom rows if the CSV does not exactly match the number of rows used when the Excel template was generated.

Excel seems to have some sort of memory and will sometimes treat cells as if they are populated if something has been deleted.

[paulrockliffe]

Thanks for the help, that Macro looks neat!

I closed the EG file and reran it and the problem vanished, so although I've no idea what the problem was, it seems to be sorted now.

I was importing into Excel using a Data Connection that loaded the data into the Data Model, which is nothing like any of the other methods and should be far more immune to artifacts like this, as I said in my first post the issue appears to have been with EG as I could test the .csv files and see the blank row was there. 

Excel was doing as I'd have expected.  It just doesn't have much control over the import, it would be helpful if there were a few more options for tidying up the files as they come in, because it's not immediately obvious how to edit the data once it's into the Data Model, at least on my version of Excel.

The most frustrating thing about this sort of thing is that I have PowerBI Pro, which is by far the best solution for what I'm doing and can correct dodgy input data.  But I have to deliver in Excel.

Oh well, it's sorted now.  Thanks again!