Excel load into single field of similar named columns

[tresesco]

Hi All,

I have an excel file with coulmn name say AAA, BBB many times. When i reload it, QV rename then as AAA1, AAA2, ..... BBB1, BBB2,..... What i want is : all AAA named columns should come under one filed and same for BBB too.

One more issue i am facing: I have created an ODBC connection to fetch the multi sheet excel file. The file originally contains 131 sheets. when i am loading it is showing 262(just double) tables (every sheet is duplicated by a name as originalname with an underscore follwed). I think its a driver problem. Any idea ?

your inputs are most welcome.

Thanks & regards,

tresesco

[#2]

hi tresesco

can u please post your excel file with sample data?

thanks

zaman

[tresesco]

Hi Zaman,

Thanks for your interest. Here is the attachment with sample data.

[#4]

try this

i will talk to u 2moro

zaman

[tresesco]

This is not being dynamic. Anyway thanks for your reply. Any thing else which could load logically like :

Load AAA* as AAA , // AAA* can be AAA1, AAA2.....

BBB* as BBB // any number of BBB1, BBB2....

From ......;

Can something like this be achieved in QV?

Regards,

tresesco

[#6]

Hi,

you may load the table with no label (using @1, ..@n)

and if the strucutre is not changing you know the number of columns for each labels so you can store every COKE (@1, ..@7) in one

specific month (I guess every column represent a month) with 'COKE' as label and do the same in a loop for every label

second option is use a crosstable to qualify field but you may do it for every label

reagrds

christian

[tresesco]

Thanks Christian. but i need in more dynamic way as i mentioned in my heading post. Anybody with differed idea ? Inputs are greatly appreciated and vey much valuable for me.

Regards,

tresesco

[#8]

Hi!

What about this? If you do the same for sheet 2 as i did for sheet 1 you could join it and hopefully get what u want.

Regards,

Kristofer

[tresesco]

Thanks a lot Kristofer ! I am about to learn a new area of excel loading from your valuable post. i need some clarification. would you please explain the following script portion line by line ?

Load wno,

sum([1.000000]) as [1], // ??

sum([2.000000]) as [2],

sum([3.000000]) as [3],

sum([4.000000]) as [4],

sum([5.000000]) as [5]

From

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Rotate(right), // ??

Transpose(), // ??

Transpose(), // ??

Transpose(), // ??

Rotate(right) // ??

))

group by wno

thanks & regards,

tresesco

[tresesco]

Any body please !