Excel Order Export File - how do people manage the multiple lines?

[ChrisRNF]

I am not an Excel expert and I am really struggling with the Export Orders Detail CSV file.  The one line with customer data and then the next lines with the order details.  Do people have some Excel Magic that manipulates the data so it is in more of a database format?

I exported order detail from the SquareUp space and it is more of a database format CSV but it does not have the Square Order Number it just has some Transaction and Customer ID numbers.

Any help would be appreciated - I am manually editing the Export Orders Detail excel file and I have had a few human errors, naturally 😞

Thanks so much! -Chris

[TheRealChipA]

@ChrisRNF I can certainly help with this.  I’ve been programming spreadsheets and using them as databases for decades.  However, I do not see a way to expert an orders detail CSV from the Square dashboard. Can you either tell me where you are getting that CSV from, or attach it to a reply so I can look at yours?

Thanks,

[ChrisRNF]

Hey Chip!  I would sooo appreciate your help thanks so much!     

From the SquareUP Dashboard (not square.online) I select 

Reports

Then

Item Sales

    Then provide a date range for the sales

    Then click the Export button on the top right of the screen

         A drop-down will appear asking if you want Summary or Detail.   I select Detail

I then get a CSV file with all of my orders.  It downloads automatically; you don't have to wait for an email and download like on the Square.Online site.   The only problem is there is no Square Order Number so its hard to tie it back to the Square.Online Export Orders file that you get.

Let me know if this makes any sense and works for you!

Best,  Chris

Rebecca's Nut-Free

[TheRealChipA]

@ChrisRNF Ok.  I have downloaded both files from my business.  Next, tell me SPECIFICALLY, what are you trying to do with the .online file?  What data would you want from it if it had everything in one line instead of multiple lines?

[ChrisRNF]

@chefchip OK, specifically I would like the file from Square.online to have 1 record (row) for every item ordered and for each row to also contain the buyers name address phone etc.  Right now I have 1 row with the buyers info and then 3 rows with item info if they purchased 3 items.

When I import the CSV file from the Square.Online export, the column headings start with Order Number and Date and this info is in all 4 rows (using above example).  Then column C, D, E, through AC, AD, AE (Status, Currency, Subtotal, through Billing City, Billing Region, Billing Country) only have data for the Customer so these columns only have data for the first row of the transaction.   I would like the data from this first row of the transaction to appear in the rows of each of 3 rows of the item specific rows.  

Ideally I would like to then get rid of that first row that only has the data for the customer, but that is not critical I can get rid of that by sorting it out.

I would end up with 3 rows of data and columns C, D, E, through AC, AD, AE would all have the same customer data in them and columns AF, AG, AH  (Product ID, Product SKU, Product Name through AQ, AR, AS (Coupon, Pickup Date, Fulfillment Time) would each have the info specific to the item ordered.

I hope I explained this OK!  Thanks @TheRealChipA !

[TheRealChipA · Accepted solution]

@ChrisRNF Very thorough explanation.  I believe I understand.  Also, from what I see in that file, there are a couple of solutions possible.  None of them will do EVERYTHING you want, but they will produce the data results you want.  I’ll work on this over the next few days and upload a file to you.  If you’re ok with it, please PM me a Square.online file containing your data to use.  That way, what I send back to you (via PM) will be recognizable to you.

[ChrisRNF]

@TheRealChipA  I could not for the life of me figure out how to attach an Excel Spreadsheet in the PM app so I sent an email to the email on your website - hope that is OK 🙂

[ChrisRNF]

@TheRealChipA reply above i meant to tag you 🙂

[ChrisRNF]

@TheRealChipA Thank you so much for your assistance with this - I really appreciate you taking the time to help out a fellow Square Seller!  You are awesome.  -Chris, Rebecca's Nut-Free

[TheRealChipA]

You’re very welcome, Chris. I’m glad I could help. Let me know if anything goes haywire with that. Like I said in the phone, it’s a kludge at best. But sometimes that’s all we get in the way of solutions! Best wishes,

[roundriverbaker]

@TheRealChipA and @ChrisRNF Any chance that work can be made available to others?  This is a pretty common problem/frustration and lots of us need help on this front

[TheRealChipA]

Hey again, @roundriverbaker.  I’ll look at this later today, when I look at your other issue.  It will be a few hours, though!

[TheRealChipA]

@roundriverbaker Iforgot to ask. Do you use Excel or Numbers or what spreadsheet?

[roundriverbaker]

Google sheets, thanks...

And I'll just add what I'm looking for...an easy way to take the csv export of orders, import them into google sheets, and distill that down to names and items ordered (and numbers) for each customer.  This is for monthly orders (with pickups once a week), and we need a quick and clear reference to see what each customer should get each week when they walk in...we grab fresh bread off the shelf to fulfill orders when customers come in each week, for their monthly bread subscription.

I'm open to alternative strategies for easily accessing this same info, but using the POS through our stand/register hasn't been that quick/handy...

Thanks again...

[TheRealChipA]

@roundriverbaker Google sheets can import Excel files, so you should be fine with me sending you an Excel file with instructions to use it.

Before I do, to be clear, there are orders placed through your Square Online store, correct?  In other words, the CSV export of orders will be done from the Online section of your dashboard?

[roundriverbaker]

Yes, and yes...thanks

[TheRealChipA]

Very good, @roundriverbaker.  I have a spreadsheet to send to you.  I need to do that via email.  I’m going to send you a private message with my email address.  Reply to that, and I’ll reply with everything you should need.  We can continue the conversation via email to ensure that what I’m sending works for your particular needs.