BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
Calcite | Level 5
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
Not applicable
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
Calcite | Level 5

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
Tourmaline | Level 20
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.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3068 views
  • 0 likes
  • 5 in conversation