Parsing QB trial balance export to Excel

Any non-Tax accounting topics go here.
#1
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
client exported their lengthy detailed QB accrual basis trial balance to Excel. Then their controller made a bunch of aje's to restate correctly to the cash basis.

I'm trying to suck the trial balance into TR's Workpapers CS but because QB imbedded the gl account numbers into the account name when it was exported, I don't have clean separate columns for the account number and one for the account title.

a single cell looks like this:

500-000 · DIRECT COSTS:590-000 · OTHER DIRECT COSTS:591-000 · ODC - Fringe Benefits:591-100 · ODC - Paid Time Off:591-101 · ODC - Vacation

I can parse the 500-000 into a separate column because of the dot separator, then the : separator and then parsing each section by section. Have to remember to insert lots of columns to the right so they don't get overriden.

Anyone know of an IIF file converter/tool?

Problem is those darn nested QB sub sub accounts.

(don't know macros or vb, but if that's what it will take I'll ask a buddy)
 

#2
Posts:
2468
Joined:
24-Apr-2014 7:54am
Location:
Wisconsin
There's so many sub accounts your client needs to send you a $5 footlong.

There are a couple formulas you can use to find the last instance of the colon and then work afterwards from that. Here's the top one in my Google search:

https://trumpexcel.com/find-characters- ... rd%20slash.
 

#3
cp_acwt  
Posts:
98
Joined:
22-May-2014 1:59pm
Location:
MichigaN
https://www.transactionpro.com/

Look at this it might help.
 

#4
Posts:
2887
Joined:
21-May-2018 7:50am
Location:
Northern MI and Coastal SC
cp_acwt wrote:https://www.transactionpro.com/

Look at this it might help.


Works well for some things, this is not one of them.

Simplest method I have found (and I have done this A LOT--Quickbooks is not the only one that does it) in these cases is various functions within Excel (I do not have them memorized, have to look them up each time at this point since I am using them less frequently). It is time consuming, it may be simpler to manually crate and enter into a clean Excel format you can then use to import.
 

#5
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
yeah, I'm just about at same conclusion that it would have been better to manually create the chart of accounts via manual editing in Excel. I looked at the Excel formula method and not obvious to me it would be useful when theres a variety of account accounting structurs from QB. The text to column method kinda works but time consuming also

https://www.propersoft.net offers conversions from and to QB as an app and as a service. They said send them an exported chart of accounts IIF and they'll give me a quote etc.
 

#6
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
What about using MS "Power Query" ?

I called my client's cfo. At first he said he'd use the Excel text to column. Then he agreed that was a pain in this situation.

He's going to try the built in Power Query which he thought would be easy.
 

#7
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
 

#8
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
client cfo used Power Query and got it back to me this morning. Havent tried to import into TR WPCS, but looks parsed darned nicely.

"Yes, it took about 20 minutes.

I had to do 2 Column splits. 1st by each “·” that it found in the column. That created about 5 columns I think. The I had to run the Split columns again on all the columns to split out the GL No from the text. The delimiter for that split was the colon “:”. That gave me about 10 columns maybe. Then finally I created 2 new columns (Account No and Account Description) using an If-Then statement on all the account number columns and then again on the account description columns. It sounds like a lot but it was really only about 7 steps. The If Then statement is plug and run. Pretty easy overall.

You can view the Query by going to the Data Tab – Show Queries. Then just right click the query to edit it and you can see all the steps."
 


Return to General Accounting



Who is online

Users browsing this forum: No registered users and 17 guests