Building the Inventory File

ankrups

Board Regular
Joined
Mar 14, 2006
Messages
125
Office Version
  1. 2021
Platform
  1. Windows
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.
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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

1675947364495.png



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

1675947531389.png


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

1675947783150.png


10 - make your choices

1675947834477.png


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

1675947944037.png



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
 
Upvote 0
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.
 
Upvote 0
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.

1677903362824.png
 
Upvote 0
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.
 
Upvote 0
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.

1701494913146.png
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top