You are not logged in.

Massive Excel data import

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

famorocho

Trainee

  • "famorocho" started this thread

Posts: 2

Date of registration: Apr 15th 2011

Location: Colombia

Occupation: BI Consultant

  • Send private message

1

Saturday, May 14th 2011, 10:04pm

Massive Excel data import

Hi guys,

I have like 1000 Excel files (five or six models in all). Each file is multi sheet and there are more than one row as a header per sheet, depending on the model. Each row in the data area contains information of many dimensions (old or new elements) and measures. I need to import all that data to Palo. Of course, There must be a cycle for importing all elements to dimensions and another one for load data to cube.

I wonder if I could create five templates and load them dynamically and automatically from each sheet and from each file in a loop for the entire directory where the files are stored. I know that there are no functions in ETL or Excel Wizard that do that. I'm not an Excel or programmer expert; so, do any of you know what and how I can do? I would really appreciate your help in facing this problem.

Suggestions (solutions?) please.

Thanks in advance
Best regards

Fernando Amorocho

tish1

Sage

Posts: 762

Date of registration: Jul 13th 2009

Location: Vienna / Austria

Occupation: Senior Consultant @ Vector SW DV GmbH

  • Send private message

2

Wednesday, May 18th 2011, 9:27pm

Hi,

I'd first try to import the data via ETL Server and Excel ODBC.
With a few lines of batch code you can loop through the files and pass the current filename to a variable when calling the relevant ETL job.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH

famorocho

Trainee

  • "famorocho" started this thread

Posts: 2

Date of registration: Apr 15th 2011

Location: Colombia

Occupation: BI Consultant

  • Send private message

3

Thursday, May 19th 2011, 12:55am

Hi,

Thank you for your answer. I really do not know how to build the batch code you are talking about. As I told before I am not a programmer, so I appreciate a lot if You can give a sample to me.

Best regards
Best regards

Fernando Amorocho
Rate this thread