topic Re: Using DBMS=EXCELCS To Import via SAS PC File Server From SharePoint Question... in SAS Programming

Using DBMS=EXCELCS To Import via SAS PC File Server From SharePoint Question...

JayS — Mon, 24 Jan 2022 20:27:02 GMT

Have EG connected to a Mapped SharePoint drive. Am using the DBMS=EXCELCS to import a *.xlsx file off of SharePoint. Having problems defining the startrow=2 value as the first row has text garbage that I need to skip. Seems like the EXCELCS has different parameters than other DBMS identifiers... How can I point the import to start at row_2 (headers) row_3 down has all data.... SAS EG: 7.15 TIA.. J ******************************************** Error Message: startrow=2; ________ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Using DBMS=EXCELCS To Import via SAS PC File Server From SharePoint Question...

Reeza — Mon, 24 Jan 2022 20:34:13 GMT

Instead of startrow try specifying the RANGE.

Range = "Sheet1$A2:0";

The 0 indicates that it should read to the end of the data.

Re: Using DBMS=EXCELCS To Import via SAS PC File Server From SharePoint Question...

JayS — Mon, 31 Jan 2022 18:18:09 GMT

ISSUE: Need to skip over the First Row of the Excel document on a Mapped SharePoint site using PC File Server.

Still struggling with this one...

Read through numerous white papers and tried multiple combinations of Options...

It seems the issue is with the following statement, as it points to PC SAS Server running my laptop: DBMS=EXCELCS

Are there any articles that specifically refer to the EXCELCS and values I can assign.

It seems the documentation uses the DBMS=EXCEL or the extension variations of Excel (xls…) which is different from my approach.

When I use the Range= option I get the: ERROR: File _IMEX_.'MasterInput$A2:0'n.DATA does not exist. regardless of how I identify the range.

The Range I need is $A2:0

Note: The excel document is fairly large: CL 1698 is the last cell I need to read in...

Thinking maybe I'll just have to modify the Excel and save it off in the format SAS EG likes...

Thanks...

Re: Using DBMS=EXCELCS To Import via SAS PC File Server From SharePoint Question...

SASKiwi — Mon, 31 Jan 2022 20:03:54 GMT

I suggest you work with SAS Tech Support on this. The RANGE option is supported with EXCELCS so there must be something else going on.

Have you tried specifying the range without the sheet name?

Also please post your complete SAS log using </> so we can see clearly what is happening.

Re: Using DBMS=EXCELCS To Import via SAS PC File Server From SharePoint Question...

JayS — Mon, 31 Jan 2022 20:30:58 GMT

Thanks, Yes tried numerous combinations of and values...

I just confirmed the *.xls is 'flat' no lookups or links which is what I though may have been causing the issue (white papers)

I'll check with SAS Tech Support and see what they come up with.

Thanks, Jay