Inserting into a named range in an XLSX file

DaveBirch — Sat, 04 Jun 2016 13:44:28 GMT

I'm trying to insert data a named range in an .xlsx file with the following code:

libname MyXLSX PCFILES path="&SrcPath.\Alt_Rptg_Tree.xlsx" SCAN_TEXTSIZE=NO;
PROC SQL ;
INSERT INTO MyXLSX.Acct_Chkr
   SELECT t1.Class_Description,
     t1.Sub_Class_Description,
     t1.Item_Description,
     t1.Cost_Element_Num,
     t1.Cost_Element_Description,
     t1.Rptg_SubClass_Name,
     t1.Rptg_SC_Order
   FROM PERM.REF_CE_TREE t1;
QUIT;

But, despite having the column attributes set to number in Excel (with Format Cells…), I get the following error:
ERROR: Value 4 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

When I view the properties of MyXLSX.Acct_Chkr they are all defined as char with $255 format.

Is there any way either in SAS or Excel to force the columns to match the type in my data?

(Using SAS9.4 through EG7.1 with a Windows server.)

Re: Inserting into a named range in an XLSX file

MC1985 — Wed, 06 Jul 2016 12:10:01 GMT

did you try the PCFILES libname options: MIXED=YES or MIXED_DATA=YES or MIXED_DATATYPE ?

i found this SUGI PDF a good guide to start.

hope this help

let me know

have a nice day

topic Re: Inserting into a named range in an XLSX file in Microsoft Integration with SAS

Inserting into a named range in an XLSX file

Re: Inserting into a named range in an XLSX file