topic Re: Document Conversion (.xlsx to .txt) in Data Management Studio 2.5 in SAS Data Management

Document Conversion (.xlsx to .txt) in Data Management Studio 2.5

CAHarbison — Fri, 24 Apr 2020 16:47:24 GMT

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.

Re: Document Conversion (.xlsx to .txt) in Data Management Studio 2.5

VincentRejany — Mon, 27 Apr 2020 08:38:39 GMT

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

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

Re: Document Conversion (.xlsx to .txt) in Data Management Studio 2.5

CAHarbison — Mon, 27 Apr 2020 13:42:35 GMT

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?

Re: Document Conversion (.xlsx to .txt) in Data Management Studio 2.5

VincentRejany — Mon, 04 May 2020 08:49:45 GMT

As far as I know, you don't have to, as the sheet is already a range (Sheet1$).