Import data from Excel and update a QVD

[ecabanas]

Hi all,

I have a QVD with the products prices and I want to update the new product prices.

My idea is load from an Excel, créate a table and this table update the main Price QVD

Is it posible?

Many thank's

Eduard

[Anonymous]

Hi Eduard,

You can load QVD file, then load Excel File with a left join, like this:

QVD:

LOAD

Item_id,

a,

b,

price

FROM QVD.qvd (qvd);

//it is better to do a copy

STORE QVD into QVD-copy.qvd (qvd);

Excel:

Left Join (QVD)

LOAD

Item_id,

a               as aNew,

b               as bNew,

price           as priceNew

FROM ExcelFile;

NoConcatenate

LastTable:

LOAD

Item_id,

IF(IsNull(aNew),a,aNew)     as a,

IF(IsNull(bNew),b,bNew)     as b,

IF(IsNull(priceNew),price,priceNew) as price

RESIDENT QVD;

STORE QVD into QVD.qvd (qvd);

Kind regards.

[Gysbert_Wassenaar]

Yes. Load the data from excel and store the resulting table in the qvd.

[Kushal_Chawda]

If you just want to replace the Product and price value then you can just load the data from excel and store it into the QVD

[ecabanas]

Hi Gysbert,

1º Is it possible to open a file manager to select where the file is?

2º I want to add data to the QVD, not replace it,

Many many thank's

Eduard

[Gysbert_Wassenaar · Accepted solution]

Use the Table Files wizard in the script editor to select your excel file. You can't use a file manager.

Load the data from the excel file and append the data from the qvd file to it. Then store the complete table into the qvd. Or the other way around: load the data from the qvd file and append the excel data to it and then store the table to qvd again.

[jonathandienst]

Something like this:

T_Products:

LOAD ProductKey,

  Price as T_Price,

  ...

FROM Products.qvd (qvd);

Left Join (T_Products)

LOAD Productkey,

  Price as NewPrice,

  1 as UpdateFlag

FROM PriceUpdate.xlsx

(ooxml, ....);

Products:

LOAD *,

  If(UpdateFlag = 1, NewPrice, T_Price) as Price

Resident T_Products;

Store Products into Products.qvd (qvd);

This should get you started. You will need to amend the above to the correct field names and file paths; and fill in the blanks.

You may also want to drop the UpdateFlag and T_Price fields (Drop Fields UpdateFlag, T_Price;) before storing the result.

[MindaugasBacius]

Take a look at the example attached.

You can apply it to your business needs.

Data:
LOAD Product,
     Price,
     Comment
FROM

(qvd);


Left Join (Data)
LOAD Product,
     Price as [New price]
FROM

(ooxml, embedded labels, table is Sheet1);

UPDATE: after all the sintax you can store everything back to qvd:

STORE Data into ;

or just rewrite onto the old one.  C:\Users\Mindaugasb\Desktop\Data.qvd

[didierodayo]

Hi Gysbert,

I need similar help below and will appreciate you assistance.

Thanks

Updating QVD with excel