inport online excel (.xls) file

[mathias]

Hello

I need to import an excel file from http location (http://statbel.fgov.be/fr/binaries/311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls)

I tried proc import and data infile:

proc import

  out=test

  datafile='//statbel.fgov.be/fr/binaries/311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls'

  dbms=excel

  replace;

run;quit;

ERROR: DBMS type EXCEL not valid for import.

DATA pop1990_2011;

    LENGTH

        REFNIS             8

        ENTITE           $ 40

        '1990'n            8

        '1995'n            8

        '2000'n            8

        '2004'n            8

        '2005'n            8

        '2006'n            8

        '2007'n            8

        '2008'n            8

        '2009'n            8

        '2010'n            8

        '2011'n            8 ;

    FORMAT

        REFNIS           BEST12.

        ENTITE           $CHAR40.

        '1990'n          COMMA12.

        '1995'n          COMMA12.

        '2000'n          COMMA12.

        '2004'n          COMMA12.

        '2005'n          COMMA12.

        '2006'n          COMMA12.

        '2007'n          COMMA12.

        '2008'n          COMMA12.

        '2009'n          COMMA12.

        '2010'n          COMMA12.

        '2011'n          COMMA12. ;

    INFORMAT

        REFNIS           BEST12.

        ENTITE           $CHAR40.

        '1990'n          COMMA12.

        '1995'n          COMMA12.

        '2000'n          COMMA12.

        '2004'n          COMMA12.

        '2005'n          COMMA12.

        '2006'n          COMMA12.

        '2007'n          COMMA12.

        '2008'n          COMMA12.

        '2009'n          COMMA12.

        '2010'n          COMMA12.

        '2011'n          COMMA12. ;

    INFILE '//statbel.fgov.be/fr/binaries/311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls'

        LRECL=144

        ENCODING="WLATIN1"

        TERMSTR=CRLF

        DLM='7F'x

        MISSOVER

        DSD ;

    INPUT

        REFNIS           : BEST5.

        ENTITE           : $CHAR40.

        '1990'n          : BEST32.

        '1995'n          : BEST32.

        '2000'n          : BEST32.

        '2004'n          : BEST32.

        '2005'n          : BEST32.

        '2006'n          : BEST32.

        '2007'n          : BEST32.

        '2008'n          : BEST32.

        '2009'n          : BEST32.

        '2010'n          : BEST32.

        '2011'n          : BEST32. ;

RUN;

ERROR: Physical file does not exist,

       \\statbel.fgov.be\fr\binaries\311111Population%20de%20droit%201990-2011_fr_tcm326-55771.xls.

SAS 9.3, EG 5.1

Site number:  50600655.

Operating System:   WX64_SV .

---Base SAS Software

---SAS/STAT

---SAS/GRAPH

---SAS/ETS

---SAS/CONNECT

---SAS/IntrNet

---SAS Integration Technologies

---SAS/ACCESS Interface to ODBC

---SAS Workspace Server for Local Access

---SAS Workspace Server for Enterprise Access

---DataFlux Trans DB Driver

---SAS Framework Data Server

---SAS Add-in for Microsoft Excel

---SAS Add-in for Microsoft Outlook

---SAS Add-in for Microsoft PowerPoint

---SAS Add-in for Microsoft Word

[LinusH]

To be able to use PROC IMPORT DBMS=EXCEL you need a license for SAS/ACCESS to PC FIle Formats.

You could use SAS/ACESS to ODBC to read Excel, but I have no clue if it's possible to register a remote Excel as a datasource.

If this is something that should be done on a regular basis, you could develop a scripts that first download the Excel file, and then import it locally, seems easier...

[ChrisHemedinger · Accepted solution]

Without SAS/ACCESS to PC Files, you can accomplish this in a few steps:

• use SAS to copy the file from the web to a path on your SAS Workspace
• use SAS Enterprise Guide and the Copy Files task to download the XLS file to your local PC (not necessary if your SAS session and SAS Enterprise Guide are on the same machine)
• use SAS Enterprise Guide and the Import Data task to create the SAS data set.

Chris

[Doc_Duke]

The 'physical file does not exist' error comes from the "%20" in the file name.  Replace them with spaces (" ") and SAS will be able to find the file.  That still leaves the SAS/Access problem.

["%20" comes from uploading a file from windows to a *nix based file server and then downloading it to windows again.  If you paste the "%20" version into a web browser, it will do the proper translation, but the direct file reference won't.]

Doc Muhlbaier

Duke

[mathias]

Doc@Duke

INFILE '//statbel.fgov.be/fr/binaries/311111Population de droit 1990-2011_fr_tcm326-55771.xls'

ERROR: Physical file does not exist, \\statbel.fgov.be\fr\binaries\311111Population de droit

       1990-2011_fr_tcm326-55771.xls.

Chris@SAS

Step1 ok

Step2 not necessary

Step3 ?

I just need to use the Import Data wizard of EG ? It's creating a "temporary text file" like 'I:\SASWork\_TD23484_SAS9XBI_\#LN00135'

Will this temporary text file be updated when the Imported XLS file is updated ?

[ChrisHemedinger]

Yes, EG converts the XLS file into a text version that SAS will import with a DATA step.

When you re-run these steps again, the XLS file is updated with the new copy, the Import Data task recreates the text file, and the data is refreshed in your SAS session.

Learn more at:

Behind the scenes: importing Excel files using SAS Enterprise Guide - The SAS Dummy

Chris

[art297]

As long as you can add a step copying the file to your system's clipboard, you can also use base SAS to import the file without needing SAS/Access for PC File Formats.  Take a look at: Copy and Paste from Excel to SAS - sasCommunity

[Peter_C]

Art

having looked at your paper, I think

a small dde demo might be in order

data orsales_short;

   array cols(30) $20 ;

   infile clipboard device=dde notab dsd dlm='09'x truncover ;

   input cols(*) ;

run ;

That successfully decoded the data from a range I expanded in excel until record length exceeded more than 256

NOTE: The infile CLIPBOARD is:

      DDE Session,

SESSION=Excel|D:\Peter\Downloads\[311111Population de droit

1990-2011_fr_tcm326-55771.xls]fr!R2C1:R12C40,

      RECFM=V,LRECL=32767

NOTE: 11 records were read from the infile CLIPBOARD.

      The minimum record length was 203.

      The maximum record length was 387.

NOTE: The data set WORK.ORSALES_SHORT has 11 observations and 30 variables.

All cells were loaded in a reasonable way (although I have what I defined  - just strings).

So perhaps the lack of support for LRECL= needn't  become a problem

However I regret using dde.

It is so deprecated 😉

regards

Peter

[art297]

: With DDE one can use LRECL

filename clippy dde 'clipboard';

  data want;

  infile clippy lrecl=400 dsd notab missover dlm='09'x firstobs=2;

  informat long_field $char327.;

  informat quarter yyq6.;

  format quarter YYQ6.;

  informat product_group $char24.;

  input quarter product_group quantity profit long_field;

run;

filename clippy clear;

It may be deprecated, but sure can still come in handy

[ScottBass]

Yet another approach:  use Powershell to run a query against the Excel file, output as CSV, then read as CSV either via unnamed pipe, temp file and data step, or temp file and PROC IMPORT DBMS=csv (which just creates a data step anyway, but is still handy when the file structure changes).

See attached file for a Powershell script that runs an SQL query against Excel.  If you're interested further I can provide supporting SAS code.

Hope this helps,

Scott