Hi All, Thanks for all the replies. Here is further detail; apologies if it's cryptic... My Powershell script to query Excel is here: Powershell/Query-Excel.ps1 at master · scottbass/Powershell · GitHub Example invocations are: .\Query-Excel.ps1 C:\Temp\Temp1.xlsx "select * from [Sheet1$]" -csv -dlm "|" .\Query-Excel.ps1 C:\Temp\Temp1.xlsx "select * from [Sheet1$]" -csv -dlm "|" | % {"$_^"} # to append an EOR marker to each record .\Query-Excel.ps1 C:\Temp\Temp1.xlsx "select * from [Sheet1$]" -csv -dlm "|" | % {"$_^"} | Out-File C:\Temp\Temp1.csv -Encoding Ascii # to create an output CSV file (could also use redirection > ) Example SAS invocation: %let powershell = C:\Windows\syswow64\WindowsPowerShell\v1.0\powershell.exe; %let psoptions = -noprofile -executionpolicy unrestricted -command; %let script = E:\Powershell\Scripts\Query-Excel.ps1; %let delimiter = |; %* do not use dlm as a macro variable name! ; %let outoptions = -encoding ASCII; %let worksheet = Sheet1; %let select = select * from [&worksheet$]; options noquotelenmax; data _null_; infile "&powershell &psoptions ""&script -path '&excel_file' -query '&select' -delimiter '&delimiter' | Out-File '&outfile' &outoptions "" 2>&1" pipe lrecl=1000; input; putlog "WAR" "NING: " _infile_; %* should never occur unless script has an error ; run; options quotelenmax; Obviously you can also invoke this from the Powershell console window to test/debug. Save the output as a "normal" CSV Use PROC IMPORT to generate a data step to import the CSV Modify to suit - this is now your data import step Run the SAS code above to dynamically convert the Excel file to CSV (I write the CSV file as a temp file in the work directory. I could also read the pipe directly, but using a physical file is easier to debug.) Run the PROC IMPORT code to import as a SAS dataset Add embedded Alt-Enter into your Excel file, rinse and repeat. The embedded Alt-Enter will mess up the import of the Excel --> CSV data So, I'll need to modify my PROC IMPORT generated data step code to cater for the embedded Alt-Enter (0Ax) characters. My 2nd post is my first attempt at this code. IMO, a giant step in the right direction is a user-configuration EOR character. Here is an except from the SAS documentation: TERMSTR= specifies the end-of-line character for the file. Use this option to share files between the UNIX and Windows operating environments. If termstr is not specified, a single LF or a CRLF function as the end of line character. If termstr=CRLF, then CRLF functions as the EOL character. The following are under Windows: CRLF Carriage return line feed. Use TERMSTR=CRLF to read Windows or DOS formatted files. CRLF is the default. LF Line feed. Use TERMSTR=LF to read UNIX formatted files. If a file contains CRLF characters, the CR functions as part of the data and not an end of line character. CR Carriage Return. Use TERMSTR=CR if the end of line character is a CR. The implication is that these are the only settings available. However, TERMSTR="^" worked for me without causing an error. IMO the SAS documentation should be expanded and clarified. I'm not going to use VBA or C approaches, although I appreciate the suggestions. IGNOREDOSEOF also looks promising. However, here is the SAS 9.3.doc: IGNOREDOSEOF is used in the context of I/O operations on variable record format files. When this option is specified, any occurrence of ^Z is interpreted as character data and not as an end-of-file marker. And here is a Google hit: 45522 - IGNOREDOSEOF option not honored when specified on the FILENAME statement Is ^Z the same as 0Ax? From the SAS doc, I wouldn't have thought to use IGNOREDOSEOF for this issue. Update: IGNOREDOSEOF did not fix the issue: 827 data foo; 828 infile "c:\temp\temp2.csv" dsd dlm="|" truncover firstobs=2 ignoredoseof; 829 input Sales Inventory Returns Region ~ $10. Product ~ $20. Subsidiary ~ $20. Stores; 830 format Sales -- Returns dollar12.; 831 if missing(Region) then delete; 832 run; NOTE: The infile "c:\temp\temp2.csv" is: Filename=c:\temp\temp2.csv, RECFM=V,LRECL=256,File Size (bytes)=465, Last Modified=25 November 2014 11:50:47, Create Time=24 November 2014 15:38:51 NOTE: Invalid data for Sales in line 4 1-6. NOTE: Invalid data for Inventory in line 4 8-18. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--- 4 Casual|Addis Ababa|4 20 Sales=. Inventory=. Returns=$4 Region= Product= Subsidiary= Stores=. _ERROR_=1 _N_=3 NOTE: Invalid data for Sales in line 6 1-5. NOTE: Invalid data for Inventory in line 6 7-17. 6 Dress|Addis Ababa|7 19 Sales=. Inventory=. Returns=$7 Region= Product= Subsidiary= Stores=. _ERROR_=1 _N_=5 NOTE: Invalid data for Sales in line 11 1-6. NOTE: Invalid data for Inventory in line 11 8-18. 11 Casual|Addis Ababa|2 20 Sales=. Inventory=. Returns=$2 Region= Product= Subsidiary= Stores=. _ERROR_=1 _N_=10 NOTE: Invalid data for Sales in line 13 1-5. NOTE: Invalid data for Inventory in line 13 7-17. 13 Dress|Addis Ababa|12 20 Sales=. Inventory=. Returns=$12 Region= Product= Subsidiary= Stores=. _ERROR_=1 _N_=12 NOTE: 12 records were read from the infile "c:\temp\temp2.csv". The minimum record length was 19. The maximum record length was 47. NOTE: The data set WORK.FOO has 8 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds The below code is still my best approach so far, although it admittedly has the overhead of appending the EOR indicator to the CSV file, which might not work in other scenarios. data foo; infile "c:\temp\temp2.csv" dsd dlm="|" termstr="^" truncover firstobs=2; input @@; _infile_=compress(_infile_,"0D0A"x); input Sales Inventory Returns Region ~ $10. Product ~ $20. Subsidiary ~ $20. Stores; format Sales -- Returns dollar12.; if missing(cats(of Region--Subsidiary)) then delete; run; Message was edited by: Scott Bass:
Added update that IGNOREDOSEOF did not solve this issue.
... View more
