Any changes in Download Orders file format?

enig

I attempted to download my orders.

If I remember correctly, previously they were auto-sorted to different (excel) sheets by months. Or perhaps I did it myself I don't really remember.

In any case. The file I am getting now is one huge mess. Some lines are of a different height (really huge) etc. There is no way I can sort it for my accountant into anything useful. Not if I dedicate a good couple days for that :(

Anyone else having this issue? Was there a change in the format, or something is getting messed up at my end?

Lukas

Lawrence

I don't believe they have ever been sorted into different months, I just tried downloading the CSV and double clicking on it and it looked "normal" in excel for me, with everything having the same line height.

robernat

@Lawrence Remember we discussed this privately late august, what Lukas (@Enig) describes is exactly what I showed you in my mails with screenshots and XL files (the date sorting problem is probaly related to Lukas trying to 'sort' it in a basicly 'non workable' file) . I dropped the discussion as it became irrelevant at the time (I needed statistics 'instantly' to investigate a new shippingservice, I finally used BL data only and discarded BO data). It may work on your side, maybe you're using newer Windows and/or Excell softwares (or Office), maybe more recent hardware, the fact ramains the download for people like Enig and myself and probably others are a true 'mess'. I requested a few lines to be removed and I'm sure if Lukas looks at his CSV file in Excell he'll confirm: buyernotes, ordernotes and shippingmethod notes are causing the biggest mess. I do understand your reasoning they might be usefull (and workable) for some sellers, but then at least create a 'light version CSV file format' for those who don't need *all* of it (including order date and time in 'trazillions' while ISO time is more then enough).
@enig I don't think a change was made on BO, it's rather an update from Windows or Office in certain versions (XP/W8) that is including a change in the interpretation of the CSV file BO generates that gets messed up.
We all know Microsoft keeps 'pushing' never and heavier softwares making older versions 'harder to run' on older hardware (I notice my 4 year old laptop running on XP is getting slower every few weeks).
Lawrence, Thinking it trough, I feel from BO's side it should be easy to solve (particulary as I already handed out the exact lines to remove from the file), if not in current CSV format, then at least in a secondary (aka lighter) version of the file format as an additional 'download' option > CSV 'full' and 'CSV light' making it workable for all users ;-)

Lawrence

@RobErNat It's a right nuisance to have to write code to get around third-party problems, but that's life. I've since read that apparently there are also regional differences with how excel handles CSV also.

I've created a TSV option which may be more reliable in Excel, could you both let me know how you get on with that?

Hoddie

CSV works fine for me. I'm in Belgium but using English (UK) Windows/Excel. I mention this because perhaps the issue is one of localisation, because a non-UK, non-English Windows/Excel might expect a comma to indicate a decimal, so a comma-delimited file would import in a very odd way.

Stellar

I also had a problem with the file that I downloaded. Before importing it to Excel I had to edit the file "plain text" and eliminate the "enters" in the order comments or refund comment, because Excel expected one line per order and this enters made that order occupy 3-4 lines, so I had to delete and leave it all in one line again, and then it worked fine.

Lawrence

@Stellar is that with TSV or CSV?

paulvdb

I have also had this problem importing orders into Excel. This is caused by multi-line order comments that will make Excel start a new row with the second (and each subsequent) line of the comment. Can you remove the line-breaks from comments when exporting to csv? That should fix the problem.

robernat

@Paul, the line breaks (Enter) are indeed part of the problem. Every 'note' (buyer note, order note, Quote note, refund note, payment details note, etc) line is actually a problem, either because of line breaks in the note, either because they are to long or because they might contain a special character causing an additional linebreak.
I suggested to Lawrence late august to simply remove all 'notes' from the CSV to avoid the problem.
I understand the reasoning some sellers might have purpose for the 'notes' to be included, but I wonder how many actually?
@Lawrence I'm not a developper, so I don't have a clue how much work it involves to 'develop' a new format to extract data, but my guessing is that your CSV format is about selecting 'fields' and putting them in a column, wouldn't it be easier to copy the current format of the CSV and leave out the fields I'm mentioning? The TSV still gives all those columns (with no resolvement, as mailed in private).

Maybe others can chime in by telling whether or not these 'notes' are relevant for them to have them in a datasheet like Excell?
Personally I don't see the purpose having the 'payment note' in the CSV, it's the sellers note to give buyers the information on 'how to pay', I'm sure every seller knows what he/she has written in there
Quote note? Order note? Buyer note? Buyer email (encrypted). Do sellers actually need those to make statistics or to build up an overview for their accountant? I doubt.
I just checked on BL, they recently added an 'MS excell' format (besides CSV, HTML, XML, tab-delimited), with 20 colomns, when I count the colomns in BO's CSV format there are around 50?
Store ID? = the number a store has on BO (mine is 165 for example): I can only download my own data, so why would that colomn need to be in the CSV, it's always the same number... 20 orders, 500 orders, 2000 orders, 10000 orders, it's always going to be the same number in that column
Tracking advise? > same note on each order, just depends whether the buyer selected one or the other > It's sellers info to the buyer > irrelevant for seller as he/she probably knows what's in the note...
Phone field?
Etc.
It's is my opinion the CSV could be simplified by leaving out non relevant information. The format of current CSV is good and works, except for all those 'notes', I'd prefer a CSV 'light' version with a lot less data (aka columns) in it.

Cheers, Eric

Stellar

> @Lawrence said:
> @Stellar is that with TSV or CSV?

Just tested, it is wrong with the 2 formats

Lawrence

@Stellar @RobErNat I've stripped line breaks from the CSV/TSV, would one of you be able to try again and see how you get on?

Stellar

@Lawrence Just tried CSV, it is perfect now.

Thanks!!

robernat

@Lawrence at first sight this looks 100% correct now, but I've only tried with 1 month. I assume if it works for 1 month it will work for any selected period :-) I'll give a wider try soon I find some extra free time ;-)