How to upload multiple excel files

[#1]

Hi,

I would like to upload a few excel sheets to make a bubble chart.

I dont know how to upload multiple sheets at a time, Could you please

tell me how to upload them manually or using a script. If there is a script,

please let me know where in the editor do i write/run this script.

I am pasting the screenshot of dashboard.

I would like to use two different excel files to make the empty bubble chart.

Also, I would like to refresh data monthly. How can i reflect the changes on the dashboard ?

Thanks for all your help in advance.

[amit_saini]

Harsh,

It should be like this :

SUB DoDir (Root)

    FOR each File in filelist(Root& '\*.xlsx')

    Timesheet:

LOAD

num([Emp. Code], '#####') as [Emp. Code],

     Date(Date, 'MM/DD/YYYY') as [Date Coded],

        Monthname(Date) as Month,

        Year(Date) as Year,

          Quartername(Date) as Quarter,

           'Week'&Ceil(Day(Date)/7) as  Week, 

           //to get week as Week1, Week2, Week3, Week4 and Week5 in a month

           //Week(Date) as Week,

           IF(NOT ISNULL(Date),  Day(Date)&'-'&weekDay(Date)&'-'&MonthName(Date),  NULL()) as Day,

           //used null function to remove null value from date field

    subfield(  Weekname(Date), '/', 2) as WeekNumber

    //this will give the week number of weeks in month.

FROM

[$(File)] (ooxml, embedded labels, table is Timesheet, filters(

Remove(Row, Pos(Top, 1))

))  ;

    NEXT File

        FOR each Dir in dirlist (Root&'\*')

        CALL DoDir(Dir)

    NEXT Dir

END SUB

CALL DoDir('C:\Users\asaini\Desktop\Sample');

If still this is not working than share your two excel sheets .

Thanks,

AS

[#3]

Hi Amit,

It ran successfully, but the data isn't uploaded.

Is there any way I can share the excel sheets ? I cant find any link here.

[amit_saini]

Brother you need to call you table fields in load statement , Timesheet i shared with you as an example.

Thanks,

AS

[#5]

Hi Amit,

Really thankful for your guidance and help.@

I still am not able to figure out everything that is required to be done.

I dont know this scripting and I need to get a job done.

Could you give me the final script that i need to write to upload 3 excel files saved in a folder called Sample on

desktop. It would be really helpful if i run a script that works and try to understand in what all places i made mistake.

File names are 37.xlsx , top 20.xlsx , Rating.xlsx.

Their column IDs are : 

37.xlsx

Project

Store_ID

ISD_ID

StoreSales

Rating_201411

top 20.xlsx

promotor_id

rating

project_id

year_id

month_id

ranking

Ranking.xlsx

promotor_id

rating

project_id

year_id

month_id

ranking

[amit_saini]

Try this:

SUB DoDir (Root)

    FOR each File in filelist(Root& '\*.xlsx')

    Timesheet:

LOAD

promotor_id   

rating   

project_id   

year_id   

month_id   

ranking

FROM

[$(File)] (ooxml, embedded labels, table is Timesheet, filters(

Remove(Row, Pos(Top, 1))

))  ;

    NEXT File

        FOR each Dir in dirlist (Root&'\*')

        CALL DoDir(Dir)

    NEXT Dir

END SUB

CALL DoDir('C:\Users\asaini\Desktop\Sample');

Thanks,
AS