mam98031 • Volunteer Community Member • Buyer/Seller since 1999
I only use Excel to drop my reports into. It is easy to edit them from there.
The headings are not "gimmicks". It is important to keep in mind the reports are coming from Ebay and Ebay data, therefore the reports are labeled with their name.
You are going to need to embrace the fact that there will ALWAYS be manual manipulation with eBay reports just because of the nature of the data and how they build reports. You also need to embrace going nekked and removing the headers entirely.
Here are some tips on pulling in reports that I have from dealing with these reports.
Globally
Do NOT import eBay reports directly into your main table. I am assuming you are compiling data to track and reconcile. If you go this route, you will have headaches. I have been there; my head still hurts.
Make an import table for the reports and then once you have the data in the correct format from eBay (a feat in itself) then copy the data to your main tables. Never mix potentially bad data directly into your good data before you verify it.
Build your tables with the headers and remove the headers from the reports completely and do not import them at all, because of the below stuff (and they are not needed).
If you are appending to a table vs creating a new one for the report, then step one is to verify the report has not changed. eBay has a (bad) habit of changing formats, headings and moving fields around without notice (and for no logical reason). They just recently added a few fields for the vault and instead of adding them at the end where they could be handled easily, they added them in the middle.
They even change formatting for no reason like numerical to alpha and then randomly change it back. I am not telling you this to beat up on eBay, they are getting better with the notifications, but the risk is your data being compromised and if you have years of data, it can be destroyed by one bad report.
Some reports not only have a header, they have a footer (also with data that screws up an import). This data IS important because it is the base for the PDF reports but can cause issues with imports as you have found out.
To Remove Headers and Footers
The first step is NOT to open it in excel or you get the formatting issue as you have seen. Open the file in notepad and delete the header and footers and then save the file *** do not change the file format. This keeps the values as they should be when you import. Once you have built your table, you do not need the headers and footers at all after you have verified that nothing has changed. You can then import directly with no formatting changes.
Individual Report Items (not sure what ones you are using so I will throw them all up).
Invoice File
Has a header and a footer. Along with the above standard delete the top line, a few rows up from the bottom, they have the store subscription fee in there (if you have one) that does not match the headers and will often hose an import. The report is date driven so this line often ends up buried in the report if you have a lot of last-minute month end sales. You need to remove this line. The same for any shipping labels with UPS, FedEx or eBay International (these are also date driven and do not match the headers correctly).
Transaction Report
If your table is set up for numerical or currency, eBay has -- instead of 0 in the numerical field so your table has a conniption fit trying to import -- into a numerical field. You can fix this by a simple find replace -- with 0 when you are removing the header and footer.
If you are using the transaction report to update shipping costs somewhere else (because that shipping cost is ONLY in the transaction report), the numerical values in the Transaction report are a negative (because it is a deduction) where the other reports use a positive value on the fees etc. If you are using a mix of reports and the transaction report, most have positive values, and the transaction report has negative values.
Orders Report
The Orders Report has a header and a footer with account data at the bottom that needs to be stripped.
If you have an order where someone buys two different listings and the order is combined, there are 3 lines of data on the orders report. A main line with values that you need like total shipping etc. and then two lines for the individual items. You have to manipulate the values for the individual items and remove the top line or you will have bad data and potentially have bad totals.
And a weird issue I had to take all the way to the tippy top to ID with Microsoft that may or may not apply with Airtable.
I am not an Airtable user but big in Access. Does Airtable allow you to change the import specs when appending or importing to a table from a file? If so, you may be running into an issue that Access has as well with imports from these files where it throws a tantrum and won't import. Change the spec from Unicode to Western European Windows if that is an option. Yeah, it is silly but at least it works.
I hope this makes sense and I have not gone too far into the weeds.
It all sounds like a lot but once you know the report character flaws, the process to fix them only takes a few seconds each month. When you can get the data in the correct format it is fantastic and you can do all sorts of really, really cool stuff with it, far beyond ANY accounting application that is attached to eBay.
Feel free to reach out, I will be happy to help you if I can.
It was the same with PayPal reports. They all had their headings too. I'm unsure why some find this unusual or just something Ebay does or even something that is bad. It is just a normal thing. Headers on reports by the entity that created it.
