I am trying to write a quick Data Job that will convert an .xlxs file to a delimited .txt file.
I am using the Document Conversion Node for my input file and Document Extraction node and then output to a text file.
Attached is a sample of the output that has been converted to Excel. I cannot figure out why the unique lines continuously repeat. A file of 300,000 records explodes to several million as a result. I reviewed the SAS documentation for this process and am doing everything as it says to. Unfortunately, I cannot find help anywhere else.
I would appreciate any guidance on making this perform as I expect. Thank you.
Hi
I don't think using the document conversion node is the way to go. Would suggest to download and install Microsoft Access Runtime which does provide the MS Excel ODBC drivers. Next you use an sql query node with the following connection string in the DSN value:
DBQ=C:\MyFolder\current BLIO promos.xls;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;MaxBufferSize=2048;PageTimeout=5;HDR=Yes;IMEX=1;ReadOnly=0;DFXTYPE=ODBC
or
DBQ=C:\MyFolder\current BLIO promos.xlsx;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};MaxBufferSize=2048;PageTimeout=5;HDR=Yes;IMEX=1;ReadOnly=0;DFXTYPE=ODBC
and in the SQL_STMT --> Select * from [Sheet1$]
You may need to adapt some of these above
Thank you - I will try this. If you go with this route, do you need to define a range in Excel? The Excel file I am using in the job has rows added to it daily. Would I have to be redefining the range every time I ran it?
As far as I know, you don't have to, as the sheet is already a range (Sheet1$).
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.