SAS ETL Studio job for excel file import

[deleted_user]

Hi

We have a project scenario where we need to create ETL jobs for importing excel files received monthly.

Is it possible to import data from Excel file using ETL studio?

Thanks

Srini

[Cynthia_sas]

Hi:
Assuming that you have everything on the platform configured and set up and the required SAS/Access product on the right servers, then you should be able to set up an ETL (now DI Studio) process flow to do that import. Then, it would also be possible to turn the ETL or DI Studio job into a Stored Process.
The ETL and/or DI Studio documentation outlines how to put together process flows and how to turn ETL/DI Studio jobs into stored processes. Or, you could contact Tech Support for more help.

cynthia

[K_rn_tiR_jesh]

Hi Srinig,

As Cynthia@sas said the requiremnet for the SAS DI , it is core part (installation) and ,

Let me tell you first step is 1) I think you know how to create the library in DI.
2) By using the source designer Short cut icon,then u will get a window containing external file option . This will helpful for any external data extraction either it is excel, .txt or .csv.
3) place the source and target ....by using the table loader tansformation job
4) then schedule the job..as per u r requirement.

I hope that this will slove your problem.

Thanks & Regards
RAJESH.KARNATI

[deleted_user]

Hi,

DI job to import the excel sheets is simple if you are using a basic proc import code in User Written code transformation.

But this can lead to some issues while importing data. Normally, If I am not wrong these excel sheets are used by employees who would not think of formats for a column while writing a data into it. You can even receive a column with Zero (0) entered in it for all its rows in one month and character data next month, even if its understandable from the column name that it should be a character.

Now the problem you will face is that when you are importing the sheet with character column having Zero in it then proc import will by default assign the data type for that column as NUMERIC, and when the next month data comes where it is a character as it should be defined for the same table, It will throw an error since while creating the table it was numeric, and now inserting character data in it.

So you need to figure out a proper format for all the columns and transform those columns in that format as required.

Then you can insert the data monthly.

Believe me it could take a while initially for the problem to be solved, but when you complete it then it becomes quite easy to use the code for many other purposes.

Do reply to it if you need any help. I have the code for such an import done already.


Thanks & Regards,
Saurabh

[nileshwani12]

i want to import the excel file in di to automate the job so please give me some suggesion to import excel file and i will give proper format and informat and type...

[LinusH]

1. Don't hijack old posts. This one is 9 yeara old!
2. The hints in this thread should at least get you started.
Please create new thread (in the Data Management forum), and try to be more specific of wgat you don't know how to do.