Uploading data from different sheet

[#1]

HI,

i have an excel with country data and each sheet has different country,  in wht way can i upload the data
thanks

[#2]

Thanks for ur efforts.  Now it has worked, but not able to get the country Field, can u suggest if u have any idea that particular sheet data is loaded for each country

[#3 · Accepted solution]

This script will also give you the "sheet name" as country in the load script.

[its_anandrjs]

Hi,

Sorry for the late response see the script code.

Example below

India:

LOAD ColA

FROM

CountryData.xlsx

(ooxml, embedded labels, table is India);

Japan:

LOAD ColB

FROM

CountryData.xlsx

(ooxml, embedded labels, table is Japan);

China:

LOAD ColC

FROM

CountryData.xlsx

(ooxml, embedded labels, table is China);

Store India into India.qvd;

Store Japan into Japan.qvd;

Store China into China.qvd;

Drop tables India,Japan,China;

LOAD ColA,

     filebasename() as Country

FROM

India.qvd

(qvd);

LOAD ColB,

     filebasename() as Country

FROM

Japan.qvd

(qvd);

LOAD ColC,

     filebasename() as Country

FROM

China.qvd

(qvd);

HTH

Rgds

Anand

[its_anandrjs]

Hi,

Let me know if it is works for you.

Hope this helps you

Rgds

Anand

[#6]

hi robert

still i am not able to get the country field

The  script is as below:

for

each sheet in '[Monthly TOTAL]', 'US','Japan', 'Germany', 'UK', 'Canada'

Salesdata:

crossTable

(Months, Data, 1)

Load

[all periods "AS BOOKED"],

[January cum.]

as Jan,

[February cum.]

as Feb,

[March cum.]

as Mar,

[April cum.]

as Apr,

[May cum.]

as May,

[June cum.]

as Jun,

[July cum.]

as Jul,

[August cum.]

as Aug,

[September cum.]

as Sep,

[October cum.]

as Oct,

[November cum.]

as Nov,

[December cum.]

as Dec,

'$(sheet)'

as Country

From

[Source data\country_sales 2011.xlsx] (ooxml, embedded labels, table is $(sheet) );

next;

let me know

[#7]

Finally got it, As i was using cross table to upload the data, the country field was not getting displayed, i have once again written "For" statement  separately to get the sheet name as country, below is the script

After running the previous script from wch i have removed country and jst added the below script

for

each sheet in '[Monthly TOTAL]', 'US','Japan', 'Germany', 'UK', 'Canada'

Salesdata1:

load

'$(sheet)' as Country

From

[Source data\country_sales 2011.xlsx] (ooxml, embedded labels, table is $(sheet));

next;

[#8]

Hi Anand thanks for your help reply ur script work fine bt its too long,