Results 1 to 8 of 8
  1. #1
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114

    Quicken export => Access import

    Hello. I have a column of account balances in an Access table. I'd like to periodically update them with the corresponding values in a Quicken file. I can export from Quicken to a text delimited file and import that data into an Excel worksheet. I can import from either the worksheet or directly from the text file into Access, but I don't see how to replace the access column with the imported data in automated fashion. The data from Quicken is not in the desired sequence. I can reorder the rows in the Excel table and copy and paste into the Access table, but I was wondering if anyone knows a more automated (or more elegant) way. I'm aware that I can link an access table to an excel table, but I'm not clear that it can be done with the desired resorting of rows.



    Thanks for any suggestion,

    RonL

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Presuming the text or Excel file have a field in common with the Access table (presumably account number), you can run an update query that will update the Access table with the corresponding value from the Quicken data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Assuming you are exporting from quicken all the account balances, or at least the ones you are interested in - not just those that have changed

    Just create a linked table the to text file.

    When you want to 'update', just replace the text file with the new one and the linked table will show the latest information

  4. #4
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114
    Quote Originally Posted by Ajax View Post
    Assuming you are exporting from quicken all the account balances, or at least the ones you are interested in - not just those that have changed

    Just create a linked table the to text file.

    When you want to 'update', just replace the text file with the new one and the linked table will show the latest information
    Ajax - the access app already exists. I don't see a way to link an existing table, let alone do it on a "field(or cell) by field" basis. (As mentioned, the account ordering in the Quicken export is not the same as in the Access table.)

    PBaldy - There is no field on which there is an exact commonality, but your suggestion provides a path forward. I can probably create sufficient unique commonality to use the update query.

    Big thanks to you both! -RonL

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. By the way, the concept of using the order of the records isn't a good one. Access is designed to be used with fields you can join on to link tables together, or to use for sorting in queries/reports. The tables themselves can be thought of as a bucket of data, with no inherent order. Don't ever count on the order of records in a table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't see a way to link an existing table
    I was referring to linking to the file you mention here, highlighted in ref
    I can export from Quicken to a text delimited file and import that data into an Excel worksheet. I can import from either the worksheet or directly from the text file into Access - just link using the facility in external data tab.

  7. #7
    RonL is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Location
    NJ
    Posts
    114
    Quote Originally Posted by Ajax View Post
    I was referring to linking to the file you mention here, highlighted in ref
    I can export from Quicken to a text delimited file and import that data into an Excel worksheet. I can import from either the worksheet or directly from the text file into Access - just link using the facility in external data tab.
    Thx, what I meant is that the linking functionality doesn't provide for linking that text file directly to the existing table in my database. A new table has to be created. Which is my plan now: to use the new table as the datasource for the update query suggested by PBaldy. (Which is probably what you had in mind all along ).

    PBaldy, yup, thanx for reminding me. When I remember that a table is just a bucket of unique rows, it helps keep the relational paradigm in focus. Thx guys.

    BTW, Quicken doesn't export the account number per se (from the report that shows balances). It exports the name that the user has given the account. So I've changed those names to include a substring of the account id in the form of a six character string xxnnnn where the x's are literal and n is a numeric character. So now I need to construct the criteria for the update query: ie. update the tblTarget.balance with tblSource.balance where the six character substring in tblSource.Name is contained in tblTarget.Name. Any suggestion for optimal string manipulation would be appreciated.

    Edit: so far I've got: mid([Source.Name],instr(1,[Source.Name],"xx"),6) as the fragment to look for in Target.Name.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Code:
    what I meant is that the linking  functionality doesn't provide for linking that text file directly to the  existing table in my database.
    for that you use a query, but think I may be misunderstanding what is required so I'll bow out

    Good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-18-2016, 09:33 AM
  2. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  3. Outlook Export Import Access
    By turk in forum Access
    Replies: 4
    Last Post: 07-17-2012, 07:31 AM
  4. Replies: 5
    Last Post: 10-28-2011, 12:20 PM
  5. Replies: 22
    Last Post: 09-06-2011, 11:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums