topic Re: DI Studio 4.9 Excel file Importing issue: No columns in SAS Data Management

DI Studio 4.9 Excel file Importing issue: No columns

anzabob — Tue, 13 Jul 2021 09:31:15 GMT

I am using proc import to get xlsx file to SAS like this:

PROC IMPORT
OUT=work.W32HONJM

DATAFILE="filename.xlsx"
DBMS=XLSX Replace;
GETNAMES=YES;

RUN;

The file in question only has one sheet, the first row has the column names and then there is a mix of number and text data for the columns.

In the output table i get all the data rows (33), but no columns. (total rows 34). Is there something wrong in the code, or can someone please suggest an alternative solution.

Re: DI Studio 4.9 Excel file Importing issue: No columns

AllanBowe — Tue, 13 Jul 2021 09:36:37 GMT

As you asked for an alternative solution (and please note - this totally biased, because I created the tool), you could consider Data Controller for SAS

This empowers your business user to load the file directly into the Target table. No need for a DI job, nothing to promote, full history of changes made, can load any database, and a copy of the excel is kept with every upload.

Here's a video to explain more:

Re: DI Studio 4.9 Excel file Importing issue: No columns

anzabob — Tue, 13 Jul 2021 09:50:35 GMT

Unfortunately, solutions outside DI Studio are not an option. I should have been more specific.

Re: DI Studio 4.9 Excel file Importing issue: No columns

Patrick — Tue, 13 Jul 2021 10:02:46 GMT

What you need in DIS are stable table structures. Something you won't necessarily get with a Proc Import.

A purist DIS way would be to define a libname in SAS Metadata pointing to this Excel file, register the Excel sheet as table and then use this table metadata in your DIS job. I've done that in the past and it works - documentation here.

Another approach discussed here

Re: DI Studio 4.9 Excel file Importing issue: No columns

anzabob — Tue, 20 Jul 2021 05:39:26 GMT

That is the solution i was looking for, much appreciated! 🙂

Re: DI Studio 4.9 Excel file Importing issue: No columns

RacheLGomez123 — Thu, 03 Nov 2022 08:55:35 GMT

One of the most common CSV import errors is that the file is simply too large, and the same holds true for Excel files. It may be caused by too many fields or records in the file, too many columns, or too many rows. This Excel import error can be caused by limits set by the program using the file or the amount of available memory on the system. If your import fails because of file size issues, you need to go back and break the file up into smaller files, which will enable it to avoid this file error and successfully upload.

Regards,

Rachel Gomez

Re: DI Studio 4.9 Excel file Importing issue: No columns

Patrick — Thu, 03 Nov 2022 10:52:43 GMT

@RacheLGomez123 Besides of you replying to an old track that talks about Excel: Is your comment "generic" or based on real experience with SAS reading .csv files? Proc Import generates data step code for reading a .csv and I'm not aware of any size limitations that would be a problem for real live scenarios.