Sample QIF Import and Export

simplsol at aol.com simplsol at aol.com
Fri Jan 13 11:00:56 EST 2006


Scott,
Here are a set of handlers I wrote a very long time ago in HyperTalk. 
This is entirely reverse engineered (trial and error) and it was 
written for import and export to the checkbook part of my business 
system, so it may not handle all of the data you are dealing with.
Looking at it today, I can see lots of improvements from porting it to 
Transcript (30,000 character limit in fields?!?). Don't forget to quote 
all literals - HC could handle a variable called Date, Rev requires 
"Date".
This should get you started:

on exportQIF
  ask file "Enter a name for exported text:" with "Export to QIF"
  if the result is "Cancel" then exit exportQIF
  put it into fileName
  ask "Start date:" with fld Date of cd 1
  if the result is "Cancel" then exit exportQIF
  put it into startDate
  ask "End date:" with the short date
  if the result is "Cancel" then exit exportQIF
  put it into endDate
  convert startDate to seconds
  convert endDate to seconds
  put "Reviewed: 0  Exported: 0"
  put "!Type:Bank" into line 1 of temp
  repeat with i = 1 to the number of cds
    put i into word 2 of msg
    get fld "Date" of cd i
    convert it to seconds
    if (it ? startDate) and (it ? endDate)
    then
      add 1 to last word of msg
      put return after temp
      put "D"& fld "Date" of cd i after temp
      put return after temp
      put "P"& fld "Payee" of cd i after temp
      put return after temp
      put "M"& fld "Memo" of cd i after temp
      put return after temp
      if fld "Check/Deposit" of cd i is "Deposit"
      then put "T"& fld "Deposit" of cd i after temp
      else put "T-"& fld "$" of cd i after temp
      put return after temp
      if fld "Check/Deposit" of cd i is "Check"
      then
        if the number of lines of fld "Mailing Address" > 0
        then
           repeat with n = 1 to the number of lines of fld "Mailing 
Address"
            put "A" & line n of fld "Mailing Address" of cd i after temp
            put return after temp
          end repeat
        end if
        put "N"& fld "Check Number" of cd i after temp
        put return after temp
      end if
      if fld "Cleared"of cd i is not empty
      then
        put "C*" after temp
        put return after temp
      end if
      put "L" & fld "Category" of cd i after temp
      put return after temp
      put "^" after temp
    end if
  end repeat
  get the number of chars of temp
  if it>30000
  then
    answer "The export contains "&it&" characters."&¬
    " Only 30,000 fit in the text file. Try a smaller range of dates."
    clearMsg
  end if
  open file fileName
  write temp to file fileName
  close file fileName
  clearMsg
end exportQIF

on importQIF
  answer file "Import from which QIF text file?" of type text
  if the result is "Cancel" then exit importQIF
  put it into fileToImport
  if it is empty then exit importQIF
  open file fileToImport
  read from file fileToImport until eof
  go to last cd of this stack
  send "doMenu new card" to HyperCard
  put 0 into addressLine
  put "Transactions imported: 0"
  repeat with i = 2 to the number of lines of it
     --if char 2 of line i of it is empty then next repeat--to skip 
empty lines,didn't work
    if char 1 of line i of it is D
    then
      delete char 1 of line i of it
      put line i of it into fld "Date"
      next repeat
    end if
    if char 1 of line i of it is P
    then
      delete char 1 of line i of it
      put line i of it into fld "Payee"
      next repeat
    end if
    if char 1 of line i of it is M
    then
      delete char 1 of line i of it
      put line i of it into fld "Memo"
      next repeat
    end if
    if char 1 of line i of it is T
    then
      delete char 1 of line i of it
      if the number of chars of line i of it > 5
      then
        repeat with x = 1 to (the number of chars of line i of it - 6)
          if char x of line i of it is ","
          then delete char x of line i of it
        end repeat
      end if
      if char 1 of line i of it is "-"
      then
        delete char 1 of line i of it
        put line i of it into fld "$"
        put "Cash Pmt." into fld "Check/Deposit"
      else
        put line i of it into fld "Deposit"
        put "Deposit" into fld "Check/Deposit"
      end if
      next repeat
    end if
    if char 1 of line i of it is "A"
    then
      add 1 to addressLine
      delete char 1 of line i of it
      put line i of it into line addressLine of fld "Mailing Address"
      next repeat
    end if
    if char 1 of line i of it is N
    then
      delete char 1 of line i of it
      put line i of it into fld "Check Number"
      put line i of it into fld "MaxNumber"
      put "Check" into fld "Check/Deposit"
      writeOutTheAmount
      next repeat
    end if
    if char 1 of line i of it is C
    then
      delete char 1 of line i of it
      --X=reconciled, *=cleared
      if line i of it is X
      then put "?" into fld "Cleared"
      else put "?" into fld "Cleared"
      next repeat
    end if
    if char 1 of line i of it is L
    then
      delete char 1 of line i of it
      put line i of it into fld "Category"
      next repeat
    end if
    --S=Category in split
    --E=Memo in split
    --$=Dollar amt in split
    if char 1 of line i of it is "^"
    then
      put 0 into addressLine
      send "doMenu new card" to HyperCard
      set numberFormat to "#."
      add 1 to last word of msg
    end if
  end repeat
  close file fileToImport
  go to last cd of this stack
  set lockMessages to true
  send "doMenu delete card" to HyperCard
  updateRunningTtl
  go to previous card
  put "Updating totals."
  set numberFormat to "#."
  listRecentTransactions
  --answer "File "&fileToImport&" imported successfully."
end importQIF

Paul Looney

-----Original Message-----
From: Scott Kane <scott at proherp.com>
To: 'How to use Revolution' <use-revolution at lists.runrev.com>
Sent: Fri, 13 Jan 2006 15:29:31 +1100
Subject: RE: Qif Files

   Hi Paul,

> QIF is an old-style data format, it does not use tabs or commas as
> delimiters. Instead it allocates aa specific number of characters per
> field - and the fields have to be padded if they contain less
> than the
> allocated characters.
> About 10 years ago I wrote a QIF import/export routine in HyperCard.
> Let me know if you need it and I'll try to find it.

As I'm new to Rev's parsing routines it'd be great
if you could find it.  I'd really appreciate it,
but don't let it consume to much time for you...

Thanks,

Scott


_______________________________________________
use-revolution mailing list
use-revolution at lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your 
subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution

    



More information about the use-livecode mailing list