topic Re: Importing an xlsx file, preserving all characters as text as they appear in Excel in SAS Programming

Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Fri, 17 May 2024 19:00:00 GMT

Hi folks,

I am attempting to read an xlsx file into SAS without SAS doing anything to modify the the variable types or the characters as they appear when viewing the xlsx file in Excel. In other words, if something looks like a date, regardless of how it's stored in the xlsx file (e.g., "11/25/2023"), it'll show up in sas as a character variable as that same value (e.g., "11/25/2023"). In other words, I'd like to replicate what the Import Data wizard in SAS does when it just defines all fields as character, as in the following attributes for one particular column, for example:

type=String

Source Informat=$CHAR29

Len.=29

Output Format=$CHAR29.

Output Informat=$CHAR29.

The code automatically generated from the Import Data wizard when I do this contains the DATALINES4 statement, which I can't use because I can't just fill all of these in, as I'm trying to load the data in without having to look at the contents. I'd be fine, though, if I can define the variable types/lengths/formats/etc., as I know what the variables are and what they'll be named.

How do I do this without having to fill in the DATALINES4 content?

I need to be able to specify the sheet within the Excel workbook in question as well.

Thanks!

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

ballardw — Fri, 17 May 2024 19:53:29 GMT

Proc Import file="<path>\your excel file.xlsx" out=yourdatasetname

dbms=Excel replace;

mixed=yes;

run;

Maybe.

The documentation states (emphasis added):

MIXED=YES | NO

assigns a SAS character type for the column and converts all numeric data values to character data values when mixed data types are found.

YES specifies that the connection is set to import mode and updates are not allowed. The XLSX and XLS formats assume MIXED=YES.

Note: Due to how the Microsoft ACE driver and the Microsoft Jet Excel driver work, using MIXED=YES could result in improper text variable lengths.

NO assigns numeric or character type for the column, depending on the majority of the type data that is found.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Kurt_Bremser — Fri, 17 May 2024 19:58:04 GMT

The import wizard uses two stages: first, it converts the Excel spreadsheet to text on the client side, and then makes this text part of the code.

If you want full control of the import, you must save the data to a text (csv) file, upload it if you use SAS on a server, and then read the file with a DATA step.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sat, 18 May 2024 03:17:09 GMT

Hi ballardw--thanks for the response. Unfortunately, this didn't end up working for me, as it still brought in some fields as numbers (namely, the date fields I described in my original post, which of course were read in as number of days since whatever date, as well as other fields). That said, I found a really roundabout solution that I'll post below...

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sat, 18 May 2024 03:29:24 GMT

Hi Kurt,

Thanks for your response. Unfortunately, saving each file as a .csv, as much as I'd like to, isn't doable in this situation--the client will be receiving these .xlsx (and some are .xlsm) files (expected to be well over 100) at some point in the near future and won't have time to save all of them as such. I'm not aware of a way to save all the files as a .csv en masse. I tried something in SAS to that effect, but all it ended up doing was saving them with a .csv file extension but where the underlying data were still .xlsx, and this move somehow corrupted them such that they weren't openable even from the file explorer, much less in SAS. I could import them into SAS en masse with the intention of exporting as a .csv, but then I run into the problem that brought me here in the first place.

That said, I found a really roundabout solution, which I'll post below...

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sat, 18 May 2024 03:37:19 GMT

Ok, so here's the really roundabout solution involving DDE (yup...) I came up with, appropriating some things from a few different resources, including some old SAS Communities threads (I apologize for not citing/tagging the folks who have unbeknownst to them contributed to this ridiculous block of code):

%macro a; %do i=1 %to &n;
	options noxwait noxsync;
	%SYSEXEC "H:\misc\equip\data\core_&i..xlsx";

	%let ws=ReOp6-Equip Depr;
		%let r1=1;
		%let c1=1;
	  	%let r2=10000;
	  	%let c2=24;

	  	FileName xl
	    	DDE
	      	"Excel|&ws!r&r1.c&c1.:r&r2.c&c2."
	       	LRecL=5000
	       	NoTab;

	data _null_;  
		length fid rc start stop time 8;  
		fid=fopen('xl');  
		if (fid le 0) then do;    
			start=datetime();    
			stop=start+20;    
			do while (fid le 0);      
				fid=fopen('xl');      
				time=datetime();      
				if (time ge stop) then fid=1;      
				end;    
			end;  
		rc=fclose(fid); 
	run;

	data core_&i.b;
		Infile XL
		DSD
		Pad
		DLM = "09"x;

	    length Col1-Col%eval(&c2.-&c1.-1) $300;
	    format Col1-Col%eval(&c2.-&c1.-1) $300.;
	    input Col1-Col%eval(&c2.-&c1.-1 );
	run ;

	filename cmds dde 'excel|system';
	data _null_;
	  file cmds;
	  put '[close("H:\misc\equip\data\core_&i..xlsx")]';
	  put '[quit]';
	run;
%end; %mend a; %a

/*THIS THEN REPEATS FOR THE .xlsm WORKBOOKS...*/

It sure ain't very elegant or efficient, but it's the only thing I've tried so far that has actually worked for my purposes, so I'm not messing with it for now.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Ksharp — Sat, 18 May 2024 05:51:28 GMT

proc import datafile='c:\temp\temp.xlsx' out=class dbms=excel replace;
sheet='xxxx';
dbdsopts="dbsastype=(age='char(20)' weight='char(20)' height='char(20)')";
run;

You can specify this kind of variable to be character type as well with EXCEL engine.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sat, 18 May 2024 06:43:40 GMT

Hi Ksharp,

Thanks for this--I had tried this solution already, and it's not giving me what I want. For example, even though it's bringing the date column in as a character variable, it's still just in the form of days since such and such date but as a string instead of a numerical variable.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Tom — Sat, 18 May 2024 18:05:54 GMT

@robeldritch wrote:

Hi Ksharp,

Thanks for this--I had tried this solution already, and it's not giving me what I want. For example, even though it's bringing the date column in as a character variable, it's still just in the form of days since such and such date but as a string instead of a numerical variable.

This is the behavior that is causing the issue for you. When SAS decides that a date (or datetime they are really the same in Excel since time of day is just stored as a fraction of day) value needs to be converted into a character variable it does not apply the Excel formatting to it. Instead it just converts the underlying number (the value that is actually stored in the cell) into a character string. The same thing happens with other EXCEL display formats. For example currency displays.

Take this sheet:

If I use PROC IMPORT to read that then because the last row has character strings in every column all three variables will be character.

Your example of using DDE is one way to have SAS control EXCEL to do what you want. But DDE is a deprecated technology and can by hijacked by random other processes on your PC making it not a reliable choice.

But for DDE to work you have to be running EXCEL locally anyway so you could instead use Visual Basic or Powershell commands instead to convert the sheets in the XLSX workbooks into CSV (or other text) files that could then be read by SAS as all character variables very easily.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Tom — Sat, 18 May 2024 18:25:17 GMT

If you know what display format you want applied to the values then just add a step to do that.

For example if your generated dataset is named FROM_EXCEL and the variable created from the column in the spreadsheet with the DATE values is named DATE you might want to run something like this:

data want;
  set from_excel;
  if not missing(input(date,??32.)) then date=put(input(date,32.)+'30dec1899'd,mmddyy10.);
run;

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sat, 18 May 2024 18:52:01 GMT

Hi Tom,

I wish I knew what date format I wanted! The issue is that I don't, and the sheets I'm reading in are all manner of formats, including erroneous ones (e.g., 23/17/24). The client just wants everything preserved exactly as it's displayed on the Excel sheet (I realize the underlying data may look different). I'd be curious to see how this might be done with the other tools you mentioned above (power shell, etc). I realize DDE isn't ideal for the reasons you described, but it was the only solution that worked for me so far. Thanks for your input!

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Tom — Sat, 18 May 2024 19:09:00 GMT

Qucik google search found this StackOverflow question that has a visual basic script for converting all worksheets in all XLSX files in a folder into text files.

https://stackoverflow.com/questions/17141885/batch-convert-excel-to-text-delimited-files

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

ballardw — Sun, 19 May 2024 04:18:52 GMT

@robeldritch wrote:

Ok, so here's the really roundabout solution involving DDE (yup...) I came up with, appropriating some things from a few different resources, including some old SAS Communities threads (I apologize for not citing/tagging the folks who have unbeknownst to them contributed to this ridiculous block of code):

IF you put this into a production process you will need to be aware that DDE is no longer supported and that the internal communications channels used by DDE may be preempted by other programs. I had one program that used DDE to "get around" opening files. Until it didn't work. Because our organization started using Cisco Jabber.

The processes on the system running SAS had to have Jabber KILLED, not just turned off, to allow DDE to work. That was 7 years ago. I cannot believe that there are fewer programs that will interfere with DDE now.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Patrick — Sun, 19 May 2024 06:35:24 GMT

@robeldritch

Based on your answers it appears you're dealing with somewhat conflicting requirements and expectations. It might be worth for you to have a discussion with your customer and explain some of the differences between an Excel spreadsheet and a (SAS) table.

You simply can't have source date strings that don't represent a valid date and then expect to store this as a SAS data value (that just MUST be a valid date). You also can't apply formats "cell based" like in Excel.

The only way I can think of to get strings in a SAS table as seen in an Excel spreadsheet is to save the Excel sheets as csv using the Excel save as... function and then use SAS to read these csv files into SAS character variables (and only character variables).

@Tom already shared a link with you for a vbs script that does such conversion to csv files. I've also made a test with a chatGPT generated script which worked quite well from start (Microsoft knows Microsoft...).

If you want to take above avenue then for automation first questions are:

- Do you have option XCMD set?

- Can you confirm that your SAS environment executes under Windows.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sun, 19 May 2024 14:19:51 GMT

If there are such programs that conflict with the DDE's requirements for establishing/maintaining a connection in our organization, they're not conflicting here. The only thing I'm doing (temporarily) to ensure the connection is established and maintained (until I close it) is to relax the protected view Trust settings when generating copies of the files in the folder (also done in SAS). This is just a one-time thing--I just want to be able to have it work this one time and as simply as possible (i.e., the less applications involved, the better). I'm appending specific row/column ranges from each sheet to a master table and inserting a column in each one with another value from elsewhere on each sheet as an identifier before appending--all of which I know how to do easily in SAS. So if I can do all the steps with one script in SAS, then that's more ideal for my situation.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

robeldritch — Sun, 19 May 2024 15:06:08 GMT

Thanks for the reply, Patrick. If I had more time, I would implement a solution with one of those other applications. Since I'm most familiar with SAS, if I can get it all done (by "it", I mean all of the data transformation and appending this project requires after reading in the files, aside from reading in the files themselves) with just clicking the "Run" button in a single instance of SAS, which is what I've got now, then that's what I'm going with. I appreciate the other solutions y'all have presented in this thread using those other applications (VBS, Powershell, ChatGPT), and I'll certainly look to those for future projects involving this kind of purpose. I'm just not familiar with those, and for me to get as familiar with, for example, VBS, as I would need to get in order to make that script work (to be clear, I've never worked with VBS programming before) would take more time than I have to devote to this project at the moment.

Re: Importing an xlsx file, preserving all characters as text as they appear in Excel

Patrick — Mon, 20 May 2024 07:12:02 GMT

@robeldritch

I've heard this "no time" argument already way too often. I agree that not everything needs to be "perfect" but I also believe you need to always ask yourself what level of robustness you need.

For an adhoc job your DDE would be good enough for me but when it comes to production jobs that run regularly then spending the time upfront is imho a must. If you don't then it's only a question of time until something falls over and then it's a production issue with all the visibility, loss of customer trust and loss of your reputation. ...plus investigation and fixing things will likely add much more time than trying to "get it right" from start.

Also be aware that besides of everything else that already has been said DDE is really slow.

Creating and calling a .vbs for conversion of Excel to csv isn't hard. With the right prompts chatGPT provided me with code that I could use almost "as is". It's even given me the syntax how to call the .vbs with parameter passing. The vbs is basically automation of Excel "Save As... csv".

sheet.SaveAs targetPath, 6     REM 6 refers to the CSV format

Not sure if it's still worth it but because I've got something working here the code:

/** sample call **/
/* 1. create driver table with source paths to excel and libref for target tables */
%let source_dir=C:\temp\xlsx2csv;
%let target_lib=WORK;
data ExcelsToRead;
  infile %tslit(dir /B "&source_dir\*.xls*") pipe truncover end=last;
  input ExcelWorkbook $100.;
  if upcase(scan(ExcelWorkbook,-1,'.')) in ('XLSX','XLSM');
  target_lib="&target_lib";
run;
  
/* 2. call macro once per source Excel */
filename codegen temp;
data _null_;
  file codegen;
/*  file print;*/
  set ExcelsToRead;
  length cmd $300;
  cmd=cats('%convertExcelwb2SAStbl(source_file=%nrstr(',"&source_dir\",ExcelWorkbook,')');
  cmd=cats(cmd,',outlib=',target_lib,');');
  put cmd;
run;

%include codegen /source2;
filename codegen clear;

And here the macro that gets called and does all the work:

%macro convertExcelwb2SAStbl(
  /* required parameters */
  source_file=
  /* optional parameters */
  ,outlib=WORK
  ,csv_folder=       /* WARNING! All .csv files under this folder will get deleted. Default points to folder under WORK. */
  ,vbscript=
  ,vbscript_gen=yes
  ,delObsAllMissing=no
  );

  %local work_path;
  %let work_path=%sysfunc(pathname(work));

  %if %nrbquote(&csv_folder) = %nrbquote() %then %let csv_folder=&work_path\csv_folder;
  %if %nrbquote(&vbscript)   = %nrbquote() %then %let vbscript  =&work_path\excel2csv.vbs;

  /** create vb script for conversion of Excel workbook to csv files (save as... csv) **/
  %global vbscript_gen_flg;
  %if %upcase(&vbscript_gen)=YES and %nrbquote(&vbscript_gen_flg) ne %nrbquote(1) %then
    %do;
      %macro gen_vbs(
        outpath=&vbscript
        );
        data _null_;
        /*  file print;*/
          file "&outpath";
          put
            'REM Usage:' /
            'REM cscript "C:\path\to\vbs\ConvertXlsxToCsv.vbs" "C:\path\to\source.xlsx" "C:\path\to\target\folder"' /

            'REM Get the arguments' /
            'Dim args' /
            'Set args = WScript.Arguments' /

            'If args.Count <> 2 Then' /
            '    WScript.Echo "Usage: cscript ConvertXlsxToCsv.vbs <source path> <target folder>"' /
            '    WScript.Quit 1' /
            'End If' /

            'Dim sourcePath, targetFolder' /
            'sourcePath = args(0)' /
            'targetFolder = args(1)' /

            'REM Create Excel application' /
            'Dim excelApp' /
            'Set excelApp = CreateObject("Excel.Application")' /

            'REM Disable Excel alerts' /
            'excelApp.DisplayAlerts = False' /

            'REM Open the source workbook' /
            'Dim workbook' /
            'Set workbook = excelApp.Workbooks.Open(sourcePath)' /

            'REM Loop through each sheet in the workbook' /
            'Dim sheet, sheetName, targetPath' /
            'For Each sheet In workbook.Sheets' /
            '    sheetName = sheet.Name' /
            '    REM Set the target path for each sheet' /
            '    targetPath = targetFolder + "\" + sheetName + ".csv"' /
            '    REM Save the sheet as CSV' /
            '    sheet.SaveAs targetPath, 6 REM 6 refers to the CSV format' /
            'Next' /

            'REM Close the workbook' /
            'workbook.Close False' /

            'REM Quit Excel' /
            'excelApp.Quit' /

            'REM Clean up' /
            'Set sheet = Nothing' /
            'Set workbook = Nothing' /
            'Set excelApp = Nothing' /

            'WScript.Echo "Conversion complete. CSV files saved to " + targetFolder'
            ;
      %mend;
      %gen_vbs();
      %let vbscript_gen_flg=1;
    %end;

  /* create target folder &csv_folder for csv files if not exist */
  %if %sysfunc(fileexist(&csv_folder))=0 %then
    %do;
      data _null_;
        infile %tslit(mkdir "&csv_folder") pipe;
        input;
        put _infile_;
      run;
    %end;
  /* if folder &csv_folder exists: delete any csv file potentially created by an earlier run */
  %else
    %do;
      data _null_;
        infile %tslit(del /Q "&csv_folder\*.csv") pipe;
        input;
        put _infile_;
      run;
    %end;

  /* execute vbs: creates csv files from Excel Workbook under folder &csv_folder */
  data _null_;
    infile %tslit(cscript "&vbscript" "&source_file" "&csv_folder") pipe;
    input;
    put _infile_;
  run;

  /* populate macro vars with names of generated csv files */
  data work.__csv_files;
    infile %tslit(dir /B "&csv_folder\*.csv") pipe truncover end=last;
    input csv_file $35.;
    call symputx(cats('csv_file_',_n_),cats('%nrstr(',csv_file,')'),'l');
    call symputx(cats('out_tbl_',_n_), cats("'",substr(csv_file,1,length(csv_file)-4),"'n"),'l');
    if last then
        call symputx('n_csvfiles',_n_,'l');
  run;

  /** read generated csv files into SAS tables **/
  %do i=1 %to &n_csvfiles;
    /* read csv files into SAS table                                          */
    /* - use proc import to automatically determine required variable lengths */
    /* - read header row as data so all variables become character            */
    proc import 
      datafile=%tslit(&csv_folder\&&csv_file_&i)
      out=work.__csv_data
      dbms=csv
      replace;
      getnames=no;
      datarow=1;
      guessingrows=max;
    run;

    /* populate macro vars with code to rename variables with values from first row (=header from csv file) */
    data _null_;
      set work.__csv_data(obs=1) nobs=__nobs;
      array __charvar{*} _character_;
      do __i=1 to dim(__charvar);
        call symputx(cats('ren_var_',__i),cats(vname(__charvar[__i]),"='",__charvar[__i],"'n"),'l');
      end;
      call symputx('N_ren_var',dim(__charvar),'l');
    run;

    /* create final output table with table and variable names as per Excel sheet */
    /* - skip first obs with header names                                         */
    /* - execute generated code for renaming variables                            */
    data &outlib..&&out_tbl_&i;
      set work.__csv_data;
      if _n_<2 then delete;
      %if %upcase(&delObsAllMissing)=YES %then
        %do;
          if cmiss(of __charvar[*])=dim(__charvar) then delete;
        %end;
      /* generate variable rename code */
      %do k=1 %to &N_ren_var;
        rename &&ren_var_&k;
      %end;
    run;
    proc datasets lib=work nolist nowarn;
      delete __csv_data;
    quit;
  %end;

  /** housekeeping **/
  proc datasets lib=work nolist nowarn;
    delete __csv_files;
  quit;

%mend;

If it was me then I'd eventually would still put in a bit more work for a production job and add additional functionality and checks. For example a parameter that allows to request the target tables being created in sub-folders with the name of the Excel workbook.

The potential current "challenge" when creating tables for multiple Excel files is that the same sheet name results in the same table name and though if storing all under the same folder/libref there is a risk that tables get overwritten. Right now provide different librefs for &outlib if there is such a risk.

I've already put in some work for testing with special characters like & so they don't create issues (in path, file, tab and column names). I'd put in more unit test work if it was me to release the macro into Test/Prod.