Building the Inventory File

[ankrups]

Hello,

I need to build the inventory file which I am receiving from different vendors in csv link format. I want to convert those data into the one standardize format where I can upload into my system.

So what I want is as per below...
1. Download the raw data from each vendor who has gave us the csv link.
2. Paste the data into dedicated tabs and run the existing formula to calculate price, SOH (National), Web Price
3. Convert the data for standardized inventory file for ready to upload in our system.
4. Create report for new products to be added into the system and populate the information in each specified columns for the new product upload template.

I know there are many steps and processes involving in this project. I am not sure it can be done via Access or Excel but I need to create solution where we can upload this data daily.

Currently due to time consuming of gathering this information we are doing two times a week.

Appreciate your help in this regards.

 

[gelu]

Hi A,

Power Query can do this.

1 - Go to Data Manu
2 - click from Web
3 - add the url, click OK
4 - click Transform Data

Now the data is in PQ and you work in PQ interface

5 - delete the last Changed Type (click the x on its left)
6 - right click FILE-csv on the left (under smiley) and select Reference (to create a copy of the Source)
7 - Right click and rename FILE-csv (1) to Work File-csv

8 - You can edit the headings, move columns from here to there) to have them standardized.
9 - Select Close and Load to

10 - make your choices

If you use a new worksheet, the tab will have the name of the query

11 - Next day open the file
12 - Save as - New name (if you need to preserve the previous data)

13 - right click any cell in the table, select Refresh
It will connect automatically and download the new data from the supplier's csv file
- the new data will replace the old

There are two books on PQ, good ones by Gil Raviv and Ken Pulse.

Success!

G

 

[ankrups]

Hi A,

Power Query can do this.

1 - Go to Data Manu
2 - click from Web
3 - add the url, click OK
4 - click Transform Data

Now the data is in PQ and you work in PQ interface

View attachment 84996


5 - delete the last Changed Type (click the x on its left)
6 - right click FILE-csv on the left (under smiley) and select Reference (to create a copy of the Source)
7 - Right click and rename FILE-csv (1) to Work File-csv

View attachment 84997

8 - You can edit the headings, move columns from here to there) to have them standardized.
9 - Select Close and Load to

View attachment 84998

10 - make your choices

View attachment 85000

If you use a new worksheet, the tab will have the name of the query

View attachment 85001


11 - Next day open the file
12 - Save as - New name (if you need to preserve the previous data)

13 - right click any cell in the table, select Refresh
It will connect automatically and download the new data from the supplier's csv file
- the new data will replace the old

There are two books on PQ, good ones by Gil Raviv and Ken Pulse.

Success!

G

Thanks buddy. Will try and let you know.

 

[ankrups]

HI Gelu,

Thank you so much for your help. It works and I am almost there to make this automated. However one issue arise that one of the vendor gives the data in a way that it does not repeat the value from one row above. See below screens shot. What I need is to repeat the value from above row.

 

[ankrups]

also can I delete the file at the step 6 created?

6 - right click FILE-csv on the left (under smiley) and select Reference (to create a copy of the Source)

Please advise.

 

[ankrups]

Hello Gelu,

Recently I am having below error while amending one of the supplier for this PQ. Can you please advise why I am not getting data populated.

 

[gelu]

Hi A,

In the file/Query that gives you trouble:

1 - Open on Source on the right (in Applied Steps)
2 - Check the URL from where the data should be coming from. Is it still valid or do you have to replace it with a new one?

Gelu

 

[ankrups]

Hi A,

In the file/Query that gives you trouble:

1 - Open on Source on the right (in Applied Steps)
2 - Check the URL from where the data should be coming from. Is it still valid or do you have to replace it with a new one?

Gelu

I have just copy the link provided by the supplier. When I copy the link in the web browser it does download the .csv file.

but some how it doesn't work in PQ.

Any thoughts?

-Ankit

 

[gelu]

I have just copy the link provided by the supplier. When I copy the link in the web browser it does download the .csv file.

but some how it doesn't work in PQ.

Any thoughts?

-Ankit

Can you download it outside of PQ?

 

[ankrups]

Can you download it outside of PQ?

yes I can download the CSV file outside the PQ.