I am attempting to take a deposit in QuickBooks, parse out the data and insert it into FileMaker pro.
Here is the issue.
The only way I know of to get the deposit information from QB is to print to PDF, copy the text from the PDF, insert that text into Quark and then parse it out into a usable list that can then be scripted to upload into FMP.
(I should be able to figure this out - where I need help is below)
Here is the data exactly as it appears once it is in the Quark doc. There are returns in this if that helps.
Deposit Summary
05/26/07
Summary of Deposits to MyBank on 05/24/07
Chk No.PmtMe…Rcd FromMemoAmount
12683This Customer (975)60708298.90
16568This Customer1 (1083)607209162.21
1652This Customer2 (2307)50728075.00
5546This Customer3 (1321)607185147.99
11757This Customer4 (1472)607275130.49
11808Customer5 (389)60725688.78
Less Cash Back:
Deposit Total:100.00
Page 1
What I need to get out of “12683This Customer (975)60708298.90” is this.
Check Num Not this ID Invoice amt
“12683” This Customer ( “975” ) 607082 98.90
The check number varies from 1 to 15 or so digits.
Customer ID is always between ( ) and has 3 or 4 digits.
Invoice number is based on month year and 3 digit which means that Oct, Nov, Dec have one more digit in them. ex 1007082
Amount will also vary.
Is this feasible? --is there a better program than QB for the mac?
This is what I have so far. I am now working on the invoice and amounts. How does it look so far?
global dataList, chkNum, theAccountNum, theAccountNumList
tell application "QuarkXPress"
tell document 1
set docText to text of text box 1
my setNewDelimiter(docText, return)
end tell
end tell
getCheckNumbers(dataList) --are returned in the variable chkNum
-- RETRIEVE INFO LOOPING THROUGH DATA
on getCheckNumbers(dataList)
set numOnly to {}
set chkNum to {}
set theAccountNumList to {}
repeat with i from 5 to (length of dataList) - 4
set thisItem to item i of dataList
--get account number
my getAccountNumbers(thisItem)
set end of theAccountNumList to theAccountNum
--end account number
set allCharacters to characters of thisItem
repeat with a from 1 to length of allCharacters
set thisCharacter to item a of allCharacters
if thisCharacter is not in "1234567890" then
exit repeat
else
set end of numOnly to thisCharacter
end if
end repeat
set thisChkNum to every item of numOnly as string
set end of chkNum to thisChkNum
set numOnly to {}
end repeat
return chkNum
end getCheckNumbers
--GET DATA AS LIST
on setNewDelimiter(theText, SearchString)
set OldDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to SearchString
set dataList to text items of theText
set AppleScript's text item delimiters to OldDelims
return dataList
end setNewDelimiter
--GET ACCT NUMBERS
on getAccountNumbers(dataList)
set firstOffset to offset of "(" in dataList
set OldTid to text item delimiters
set text item delimiters to {")"}
set dataListItems to text items of dataList
set lastItemLength to (length of (last item of dataListItems)) + 1
set text item delimiters to OldTid
set theAccountNum to text (firstOffset + 1) thru -(lastItemLength + 1) of dataList
return theAccountNum
end getAccountNumbers
set docText to "Deposit Summary
05/26/07
Summary of Deposits to MyBank on 05/24/07
Chk No.PmtMe...Rcd FromMemoAmount
12683This Customer (975)60708298.90
16568This Customer1 (1083)607209162.21
1652This Customer2 (2307)50728075.00
5546This Customer3 (1321)607185147.99
11757This Customer4 (1472)607275130.49
11808Customer5 (389)60725688.78
Less Cash Back:
Deposit Total:100.00
Page 1"
set Paralist to paragraphs of docText
set RecordList to {}
repeat with i from 1 to number of items in Paralist
set this_item to item i of Paralist
if this_item contains "Customer" then
try
set AppleScript's text item delimiters to "This Customer"
set chckNum to text item 1 of this_item
set cid to word 3 of this_item
set amt to word 4 of this_item
on error
set AppleScript's text item delimiters to "Customer"
set chckNum to text item 1 of this_item
set cid to word 2 of this_item
set amt to word 3 of this_item
end try
set record_ to chckNum & tab & cid & tab & amt as string
copy record_ to end of RecordList
end if
end repeat
set AppleScript's text item delimiters to ""
RecordList
Below is the script:
I am getting incorrect results for the line at bottom that reads:
set amountNum to text (firstOffset + 7) thru -(lastItemLength) of dataList
This line should produce the Amount but it grabs the Invoice.
Suggestions?
global dataList, chkNum, theAccountNum, theAccountNumList, invoiceAndAmount, invoiceNumList
global amountNumList
tell application "QuarkXPress"
tell document 1
set docText to text of text box 1
my setNewDelimiter(docText, return)
end tell
end tell
getCheckNumbers(dataList) --are returned in the variable chkNum
-- RETRIEVE INFO LOOPING THROUGH DATA
on getCheckNumbers(dataList)
set numOnly to {}
set chkNum to {}
set theAccountNumList to {}
set invoiceNumList to {}
set invoiceAndAmount to {}
set amountNumList to {}
repeat with i from 5 to (length of dataList) - 4
set thisItem to item i of dataList
--get account number
my getAccountNumbers(thisItem)
set end of theAccountNumList to theAccountNum
--get invoice and amount numbers
my getInvoiceNumbers(thisItem)
set end of invoiceNumList to item 1 of invoiceAndAmount
set end of amountNumList to item 2 of invoiceAndAmount
set allCharacters to characters of thisItem
repeat with a from 1 to length of allCharacters
set thisCharacter to item a of allCharacters
if thisCharacter is not in "1234567890" then
exit repeat
else
set end of numOnly to thisCharacter
end if
end repeat
set thisChkNum to every item of numOnly as string
set end of chkNum to thisChkNum
set numOnly to {}
end repeat
return chkNum
end getCheckNumbers
--GET DATA AS LIST
on setNewDelimiter(theText, SearchString)
set OldDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to SearchString
set dataList to text items of theText
set AppleScript's text item delimiters to OldDelims
return dataList
end setNewDelimiter
--GET ACCT NUMBERS
on getAccountNumbers(dataList)
set firstOffset to offset of "(" in dataList
set OldTid to text item delimiters
set text item delimiters to {")"}
set dataListItems to text items of dataList
set lastItemLength to (length of (last item of dataListItems)) + 1
set text item delimiters to OldTid
set theAccountNum to text (firstOffset + 1) thru -(lastItemLength + 1) of dataList
return theAccountNum
end getAccountNumbers
--GET INVOICE NUMBERS
on getInvoiceNumbers(dataList)
set firstOffset to offset of ")" in dataList
set OldTid to text item delimiters
set text item delimiters to {")"}
set dataListItems to text items of dataList
set lastItem to last item of dataListItems
set lastItemLength to length of lastItem
set lastItemCharacters to characters of lastItem as string
set text item delimiters to OldTid
set firstTwoCharacters to characters 1 thru 2 of lastItemCharacters as string
if firstTwoCharacters is in {"10", "11", "12"} then
set invoiceNum to text (firstOffset + 1) thru (firstOffset + 7) of dataList
set amountNum to text (firstOffset + 8) thru -(lastItemLength) of dataList
else
set invoiceNum to text (firstOffset + 1) thru (firstOffset + 6) of dataList
set amountNum to text (firstOffset + 7) thru -(lastItemLength) of dataList
end if
set end of invoiceAndAmount to invoiceNum
set end of invoiceAndAmount to amountNum
return invoiceAndAmount -- returns two numbers each time
end getInvoiceNumbers
I don’t have QuickBooks, but I guess, there is a more reliable way to retrieve the data,
at least with GUI scripting. Without any delimiter how should the parsing routine know, where the phone
number ends and the amount number begins?
I have not found a way to get info out of QB. It does not allow export of “ANY” data. Some reports can be saved
as text but some cannot. So I use GUI and workarounds to get the job done. There is a program on the PC called FileBooks Link that allows data transfer between FileMaker and QB but they say Intuit has not opened up the arcitecture for the Mac.
Their suggestion is to set up a PC running QB and FileMaker and network it with your Macs. Too much for a small business.
Anyway, the script is working at getting the data into lists. I only have to put all of the lists together into one list and
then start on the code to put it into FileMaker. I like FMP. Very easy to use.
I would still appreciate any suggestions on tightening up the code.
To everyone who contributes to this site, I thank you. A couple of books and this site have taught me a lot over the
past year. More than you can imagine.
I’ve just downloaded the trial version of QB. It is very well scriptable like an AppleScript Studio application !!!
So you can get the data directly. If you like, you can send me a QB-document with your database structure and some sample data
and I will help you to write the script
--set AppleScript's text item delimiters to ""
global invoice, amt, chckNum
set docText to "Deposit Summary
05/26/07
Summary of Deposits to MyBank on 05/24/07
Chk No.PmtMe...Rcd FromMemoAmount
12683This Customer (975)60708298.90
16568This Customer1 (1083)607209162.21
1652This Customer2 (2307)50728075.00
5546This Customer3 (1321)1007209147.99
11757This Customer4 (1472)607275130.49
11808Customer5 (389)60720988.78
11809year2020 december (389)122020988.78
Less Cash Back:
Deposit Total:100.00
Page 1"
set Paralist to paragraphs of docText
set nums to "1234567890"
set RecordList to {}
repeat with i from 1 to number of items in Paralist
set this_item to item i of Paralist
if this_item contains "(" then
set AppleScript's text item delimiters to "("
set part to text item 1 of this_item
set cha to ""
my split(part, nums, cha)
set next to text item 2 of this_item
set cid to word 1 of next
set AppleScript's text item delimiters to ")"
set part to text item 2 of this_item
set AppleScript's text item delimiters to ""
my split2(part)
set record_ to cid & tab & invoice & tab & chckNum & tab & amt as string
copy record_ to end of RecordList
end if
end repeat
set AppleScript's text item delimiters to ""
RecordList
on split(part, nums, cha)
repeat with ii from 1 to length of part
set Cha_item to character ii of part
if Cha_item is in nums then
set cha to cha & Cha_item as string
else
exit repeat
end if
end repeat
set chckNum to cha
end split
on split2(part)
set r to {}
set desiredRepeatValue to 129 -- This is the highest value I want to use
repeat with a from 110 to 129
set r to r & (a mod (desiredRepeatValue + 1) as string)
end repeat
set z to characters 1 thru 3 of part as string
if z is "100" or z is in r then
set invoice to characters 1 thru 7 of part as string
set amt to characters 8 thru -1 of part as string
else
set invoice to characters 1 thru 6 of part as string
set amt to characters 7 thru -1 of part as string
end if
end split2
Edit**
To get the split in invoice and amount.
I realised that if the date got past October then there is a formula of the first two characters will always be:
10,11,12
The first two characters along with the 3rd character, the range will be
100 (oct 0000 thru 2009 ) or 110 thru 129 ( from nov 2007 to dec 2090)
So all I had to do was match if the characters where 100, or in the range of 110-129 and then split accordingly
invoice being either the first 6 or 7 characters.
amount being either the last 7 or 8 characters.
Edit2
And how embarrassing, I just looked through your script a realise if I had bothered before I would have saved some time working out the date thing. :rolleyes:
I put it down to 1 hangover, 2 I find it easier to just go down my own logic route rather than some one else’s.
I know how you feel. StefanK downloaded a demo version of QB and found out it is scriptable! I had read that it was not
so I never checked for myself! AAARRRGG! I sure am glad that he checked.
BTW, thanks for your script. I like the way it is put together. It always helps me to see someone else
re-work a script and make it simpler.
