02-03-2016 11:22 AM
Hello,
I am kinda new to file exchange. From what I read I don't see anything that says I can do what I want so I am hoping maybe someone who is more familiar can tell me if there is a way or if I am just being too optimistic.
I want to update price and quantities on my listings. I get my price and quantities from QB inventory manager as I have local sales, website sales, and ebay sales, so QB is always the most accurate. I use the SKU as my product identifier in QB because that stays the same across all sales channels. I have the SKU inputed into eBay as my custom label. I would like to create a file exchange csv with my SKU (or custom label) as the product identifier and then include price and quantity to upload new data. According to the file exchange tutorial it seems that I have to use the ebay product identifier to do this instead of the custom label.
Does anyone know if what I want to do will work or if there is a way to make it work. If not is there some magic trick to downloading the ebay item id and then matching it up with my inventory list that does not involve going through the entire list one by one to make sure I am matching the correct item id to the correct product?
Solved! Go to Best Answer
02-03-2016 10:28 PM - edited 02-03-2016 10:29 PM
Hi again @sactown_mint,
This is indeed possible. I adjust my inventory in this manner.
First, eBay's product identifier is the ItemID number. Not to be confused with Product Identifiers in the eBay catalogue.
What you need ...
1) Use FileExchange to request a "Revise Price and QTY" csv file. This file will have The FileExchange Action field, ItemID, StartPrice, Quanity, and CustomLabel fields. There are a few other fields/columns that you can delete.
a) Click on FileExchange,
b) Click on Create download report,
c) Select Active,
d) Click on the FileExchange format button,
e) Click on Save.
f) Download when file is finished.
2) Use QB (QuickBooks?) Inventory manager to create a csv file of your inventory. You will have to find the steps to do this. I do not have QB. I am sure there is a method to create a csv file from QB.
3) Excel. This is what I use. Other spreadsheets should have the same functions.
What you will do ...
Summary - You are going to import both files into Excel, do some manipulations, turn one of the files (your inventory file) into a lookup table, then pull the values from the lookup table into the FileExchange file, then create a csv file from that to upload to FileExchange. It is not hard at all. From your previous posts, you sound as if you know your way around. Do not be offended by simple instructions, others without your skill level may read this.
1) Start Excel,
2) Open the FileExchange download file,
3) Delete the unneeded columns,
a) When finished you should have the fields listed above,
4) Select Sheet 2,
5) Click on Data>From Text,
6) Select your QB inventory csv file,
a) you will need to change the delimiter to a comma (Excel defaults to Tab),
b) the CustomLabel field will have to be the first column, for the table to work for you, and must be ascending or descending. Repost if you do not know how to do this. Again, I do not know if you are comfortable in Excel,
7) Now Click on Format as table,
😎 Switch back to Sheet 1,
9) Enter this formula in the Quantity column (d2?), =vlookup(e2,table1,column number of your quantity data,FALSE)
a) Assuming your qty column in the FileExchange sheet is d2,
b) assuming your CustomLabel column in the FileExchange sheet is e2,
c) column number of your quantity data = A=1, B=2, etc.,
d) False is required for an exact match of the CustomLabel number,
10) Enter a formula in the StartPrice field in the same type of format as above. If you wish 41% in excess of cost, and your cost field is column F (6) in your lookup table, an example formula might be,
=vlookup(e2,table1,6,false)+(vlookup(e2,table1,6,false)*.41)
11) Copy the two formulas down the sheet.
12) Remember to change the ItemID number from exponential format to whole number format (you may also need to change the StartPrice to whole number with 2 decimal places),
13) Create the csv file from Sheet 1.
14) Upload file to eBay.
This seems like a lot, but you would be surprised how used to it you can become. This can be all done via a Excel macro when you know the process well enough to define one for yourself.
I would do just a few first, then check for accuracy. When you are comfortable with this, do more.
I would have double spaced to make this more readable, but it was long enough without lots of whitespace.
If you need clarification on any of this, repost.
02-03-2016 10:28 PM - edited 02-03-2016 10:29 PM
Hi again @sactown_mint,
This is indeed possible. I adjust my inventory in this manner.
First, eBay's product identifier is the ItemID number. Not to be confused with Product Identifiers in the eBay catalogue.
What you need ...
1) Use FileExchange to request a "Revise Price and QTY" csv file. This file will have The FileExchange Action field, ItemID, StartPrice, Quanity, and CustomLabel fields. There are a few other fields/columns that you can delete.
a) Click on FileExchange,
b) Click on Create download report,
c) Select Active,
d) Click on the FileExchange format button,
e) Click on Save.
f) Download when file is finished.
2) Use QB (QuickBooks?) Inventory manager to create a csv file of your inventory. You will have to find the steps to do this. I do not have QB. I am sure there is a method to create a csv file from QB.
3) Excel. This is what I use. Other spreadsheets should have the same functions.
What you will do ...
Summary - You are going to import both files into Excel, do some manipulations, turn one of the files (your inventory file) into a lookup table, then pull the values from the lookup table into the FileExchange file, then create a csv file from that to upload to FileExchange. It is not hard at all. From your previous posts, you sound as if you know your way around. Do not be offended by simple instructions, others without your skill level may read this.
1) Start Excel,
2) Open the FileExchange download file,
3) Delete the unneeded columns,
a) When finished you should have the fields listed above,
4) Select Sheet 2,
5) Click on Data>From Text,
6) Select your QB inventory csv file,
a) you will need to change the delimiter to a comma (Excel defaults to Tab),
b) the CustomLabel field will have to be the first column, for the table to work for you, and must be ascending or descending. Repost if you do not know how to do this. Again, I do not know if you are comfortable in Excel,
7) Now Click on Format as table,
😎 Switch back to Sheet 1,
9) Enter this formula in the Quantity column (d2?), =vlookup(e2,table1,column number of your quantity data,FALSE)
a) Assuming your qty column in the FileExchange sheet is d2,
b) assuming your CustomLabel column in the FileExchange sheet is e2,
c) column number of your quantity data = A=1, B=2, etc.,
d) False is required for an exact match of the CustomLabel number,
10) Enter a formula in the StartPrice field in the same type of format as above. If you wish 41% in excess of cost, and your cost field is column F (6) in your lookup table, an example formula might be,
=vlookup(e2,table1,6,false)+(vlookup(e2,table1,6,false)*.41)
11) Copy the two formulas down the sheet.
12) Remember to change the ItemID number from exponential format to whole number format (you may also need to change the StartPrice to whole number with 2 decimal places),
13) Create the csv file from Sheet 1.
14) Upload file to eBay.
This seems like a lot, but you would be surprised how used to it you can become. This can be all done via a Excel macro when you know the process well enough to define one for yourself.
I would do just a few first, then check for accuracy. When you are comfortable with this, do more.
I would have double spaced to make this more readable, but it was long enough without lots of whitespace.
If you need clarification on any of this, repost.
02-05-2016 01:39 PM
08-13-2017 06:58 AM
Will this also work with Microsoft Excel for a MAC?
08-13-2017 06:17 PM
Yes. But remember, Apple/MAC uses a different end of line than does Windows. What that means is that when you create your csv file, make sure you create the csv file as a MS DOS csv file. That option is available on your Save as screen. FileExchange will only process MS DOS format csv files.
Welcome to the Seller Tools board! You can chat with other members about seller tools and best practices in using them.
Tools related questions? Learn more about:
Videos: