Importing .csv Files (Or Converting Them to .qfx) (into Q Mac)

nello
nello Member ✭✭
I’m still working on getting credit card accounts set up (as discussed in: https://community.quicken.com/discussion/7925665/set-up-and-uses-for-credit-card-account)

Unfortunately, some credit card companies provide transaction data in .csv files and .qfx files are NOT an option.

How do I import (or convert) a .csv file?

Best Answers

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    edited February 2023 Answer ✓
    Try those rules using "If statement name contains" instead of "If Quicken name is". 

    Statement name is exactly what the financial institution sends to Quicken; Quicken name is what Quicken has changed it to based on its server-based renaming rules. 

    Also, you may be aware, but these renaming rules apply to new transactions as they are downloaded; they won't affect existing transactions in your database. (If the latter is what you're trying to do, post back and we can let you know how to tackle that.)
    Quicken Mac Subscription • Quicken user since 1993
  • Jon
    Jon SuperUser, Mac Beta Beta
    edited February 2023 Answer ✓
    Renaming rules don't apply to manually entered transactions, only to downloaded transactions. 

    Quicken Mac subscription. Quicken user since 1990.

«1

Answers

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    You can't.

    Unless you want to pay for a third-party utility which accomplishes it by spoofing the ID number of a financial institution which is authorized to work with Quicken. We can't discuss those products, since they cheat Quicken's systems, but if you Google converting csv to qfx, you'll see what's out there.
    Quicken Mac Subscription • Quicken user since 1993
  • nello
    nello Member ✭✭
    Can transaction files in any format other than qfx be imported by Quicken for Mac?

    For example, Quicken for Mac apparently supports Quicken Interchange Format (.qif) files for converting from other applications, including Banktivity or Moneydance. (See: https://www.quicken.com/support/converting-your-data-quicken-mac)

    Can transactions formatted as .qif be imported to an existing Quicken for Mac file/database? Or, alternatively, can .qif be used only to create a new Quicken for Mac file/database?
  • Jon
    Jon SuperUser, Mac Beta Beta
    You can only import a QIF as part of creating a new file.

    Look at the File -> Import menu to get an idea of what you can import to your current file.

    Quicken Mac subscription. Quicken user since 1990.

  • nello
    nello Member ✭✭
    Apparently Quicken for Mac can import transactions from a Mint CSV.

    Can you give any idea how Quicken for Mac validates that a CSV is from Mint?
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    If you're trying to spoof a Mint CSV file, I'd suggest you create a Mint account, enter two dummy accounts, enter a few dummy transactions, and then do an export to CSV to see the format Mint uses. (You might want to try to import that CSV file before you go much further, to see if the import of the Mint CSV file still works. This is the type of feature which is easily broken if Mint has changed anything in their export and Quicken hasn't made a corresponding update to their import filter.)
    Quicken Mac Subscription • Quicken user since 1993
  • Jon
    Jon SuperUser, Mac Beta Beta
    This thread shows the Mint CSV formatting.

    Quicken Mac subscription. Quicken user since 1990.

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Jon said:
    This thread shows the Mint CSV formatting.
    There's a good chance that will work; I didn't cite that thread because it's 6+ years old, with an update that's 4+ years old, and I don't know if Mint's exports are still identical. 
    Quicken Mac Subscription • Quicken user since 1993
  • Jon
    Jon SuperUser, Mac Beta Beta
    I tried it just now using the sample file from that thread, it still works.

    Quicken Mac subscription. Quicken user since 1990.

  • nello
    nello Member ✭✭
    @Jon @jacobs

    Here is a November 2022 example that also confirms the Mint CSV format:

    https://help.monarchmoney.com/hc/en-us/articles/9163024504468-Exporting-data-from-mint
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    @Jon Thanks for verifying that it works.

    @nello So it seems you have the solution you were looking for. 😀 Have you tried importing some data from your credit card download?
    Quicken Mac Subscription • Quicken user since 1993
  • nello
    nello Member ✭✭
    @jacobs
    No, not yet. I’m fishing for help to automate transforming my credit card data (ccCSV) into a Mint CSV (mintCSV) and I’m getting some nibbles.

    If I do develop an AppleScript to transform ccCSV → mintCSV, is it appropriate to post it somewhere in a Quicken forum?

    Thank you for all your help!
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    First, I’d note that every credit card company may use a slightly different format in their CSV downloads. (The lack of any standards is part of the reason Quicken mostly does not allow CSV imports). So I suspect that your AppleScript may not work for others.

    Second, I think Quicken wouldn’t want to encourage this, because some financial institutions pay a fee to Intuit to be a registered participant, and they don’t want to make it easier to bypass that infrastructure. They created the Mint-only CSV import because it could help them sell Quicken to people who wanted to move off Mint; I think if this little opening were widely exploited, they might just remove it.

    Finally: AppleScript? Apple has been slowly phasing it out in favor of newer automation technologies, so who knows how long it will remain part of macOS. 😉
    Quicken Mac Subscription • Quicken user since 1993
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    For what it's worth, there are converters out there (on the Internet) that do convert from CSV to QFX.  Try searching for "CSV to QFX".
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Yup, I mentioned converter software in the second post. 😀
    Quicken Mac Subscription • Quicken user since 1993
  • Chris_QPW
    Chris_QPW Member ✭✭✭✭
    jacobs said:
    Yup, I mentioned converter software in the second post. 😀
    Oops.   :/
    Signature:
    This is my website: http://www.quicknperlwiz.com/
  • nello
    nello Member ✭✭
    edited February 2023
    The format of data exported from Mint as a CSV file is (apparently):
    Date,Description,Original Description,Amount,Transaction Type,Category,Account Name,Labels,Notes

    I have questions about how Quicken validates and uses the data in these fields during an import:

    Date
    - Can it be empty?
    - Are leading zeros required? In other words, is ‘4/1/2022’ valid or must it be ’04/01/2022’?
    - Must the century portion be four digits?

    Description,Original Description
    - Can either or both be empty?
    - Which one does Quicken actually use for the value of Quicken’s Payee field?
    - Are they both required or may one be empty? In other words, is there any reason to have duplicate values in them (as I see in examples)?

    Transaction Type
    - Is there an assumed value if it is empty?
    - Is any value valid other than ‘credit’ or ‘debit’?

    Amount
    - Can it be empty?
    - Are any signs, e.g., ‘+’, ‘-‘, or ‘$’, permitted or required?
    - Must it always contain a decimal point followed by two digits? In other words, is any of these valid: ‘1’, ‘1.’, or ‘1.1’?

    Category
    - Can it be empty?
    - What happens if it is empty or does not match any existing Quicken Category? For example, does Quicken ask the user to select or create a valid Category and then continue with the import?
    - If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Category fields?
    - During the import, does Quicken apply the Payee Quickfill Rule, if any, based on the value of Description (or Original Description)?- If so, is a Rule applied only when Category is empty, or does the Rule overlay whatever is already in Category?

    Account Name
    - Can it be empty?
    - What happens if it is empty or does not match any existing Quicken Account? For example, does Quicken ask the user to select a valid Account and then continue with the import?
    - If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Account fields?

    Labels
    - Can it be empty?
    - What does does Quicken do with this data? In other words, which Quicken field is the data put into?

    Notes
    - Can it be empty?
    - Is this value used for the Quicken Memo/Notes field?


    Thank you for your patience with my tedious questions.
  • Jon
    Jon SuperUser, Mac Beta Beta
    edited February 2023
    (this post was in reply to nello's questions which somehow got moved after this post)

    Account Name, Labels & Notes can all be empty - the sample file in the thread I linked to above shows a transaction like that. Account name is probably only used in case of a transfer, they would need to know where the money is going to or coming from.

    Why on earth would the date or amount fields be empty? Seems to me like those two would be the bare minimum required.

    It seems like most of these questions could be easily answered by simply trying them out for yourself. 

    Quicken Mac subscription. Quicken user since 1990.

  • nello
    nello Member ✭✭
    UPDATE:

    I am experimenting with this and will post my findings.


    ORIGINAL POST


    The format of data exported from Mint as a CSV file is (apparently):
    Date,Description,Original Description,Amount,Transaction Type,Category,Account Name,Labels,Notes

    I have questions about how Quicken validates and uses the data in these fields during an import:

    Date
    - Can it be empty?
    - Are leading zeros required? In other words, is ‘4/1/2022’ valid or must it be ’04/01/2022’?
    - Must the century portion be four digits?

    Description,Original Description
    - Can either or both be empty?
    - Which one does Quicken actually use for the value of Quicken’s Payee field?
    - Are they both required or may one be empty? In other words, is there any reason to have duplicate values in them (as I see in examples)?

    Transaction Type
    - Is there an assumed value if it is empty?
    - Is any value valid other than ‘credit’ or ‘debit’?

    Amount
    - Can it be empty?
    - Are any signs, e.g., ‘+’, ‘-‘, or ‘$’, permitted or required?
    - Must it always contain a decimal point followed by two digits? In other words, is any of these valid: ‘1’, ‘1.’, or ‘1.1’?

    Category
    - Can it be empty?
    - What happens if it is empty or does not match any existing Quicken Category? For example, does Quicken ask the user to select or create a valid Category and then continue with the import?
    - If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Category fields?
    - During the import, does Quicken apply the Payee Quickfill Rule, if any, based on the value of Description (or Original Description)?- If so, is a Rule applied only when Category is empty, or does the Rule overlay whatever is already in Category?

    Account Name
    - Can it be empty?
    - What happens if it is empty or does not match any existing Quicken Account? For example, does Quicken ask the user to select a valid Account and then continue with the import?
    - If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Account fields?

    Labels
    - Can it be empty?
    - What does does Quicken do with this data? In other words, which Quicken field is the data put into?

    Notes
    - Can it be empty?
    - Is this value used for the Quicken Memo/Notes field?


    Thank you for your patience with my tedious questions.
  • nello
    nello Member ✭✭
    Whenever I File → Import a mint csv file, Quicken creates a new Account with the name of the file (including the extension).

    How do I import a mint csv file into an existing Account?
  • Jon
    Jon SuperUser, Mac Beta Beta
    edited February 2023
    nello said:
    How do I import a mint csv file into an existing Account?
    That might not be possible. Since importing a Mint file would be something you do only once (when switching from Mint to Quicken) it might just be hardwired to create a new account for each csv file. Even if you give the file the same name as an existing account it creates a new account anyway.

    Quicken Mac subscription. Quicken user since 1990.

  • nello
    nello Member ✭✭
    edited February 2023
    > @Jon said:
    > Even if you give the file the same name as an existing account it creates a new account anyway.

    Yes, that’s what I found too.

    I guess that I misunderstood the comments from @jacobs I thought he was saying that I could import Mint CSV data just like a QFX file; it would add the transactions to an existing Quicken Account, not create a new one.

    Is there any way to merge two accounts, the imported “account” and an existing real account?
  • Jon
    Jon SuperUser, Mac Beta Beta
    edited February 2023
    Just drag & drop the transactions from the imported account into the existing account.

    Quicken Mac subscription. Quicken user since 1990.

  • nello
    nello Member ✭✭
    edited February 2023
    > @Jon said:
    > Just drag & drop the transactions from the imported account into the existing account.

    Excellent; that works fine.

    Still one more question …

    I see my credit card statements have long names that I’d like to create a rule that renames them to shorter Payees that I have already following the instructions on this page:

    https://www.quicken.com/support/renaming-rules-past-and-future-transactions-quicken-mac

    Attached are screenshots of both my rename Rule as well as a register in which it is NOT firing.

    What am I doing wrong? How can I get the rule to fire?

    Thank you.
  • Jon
    Jon SuperUser, Mac Beta Beta
    Are those transactions that were part of the Mint import? I'm not sure those rules would apply to that.

    Quicken Mac subscription. Quicken user since 1990.

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    edited February 2023 Answer ✓
    Try those rules using "If statement name contains" instead of "If Quicken name is". 

    Statement name is exactly what the financial institution sends to Quicken; Quicken name is what Quicken has changed it to based on its server-based renaming rules. 

    Also, you may be aware, but these renaming rules apply to new transactions as they are downloaded; they won't affect existing transactions in your database. (If the latter is what you're trying to do, post back and we can let you know how to tackle that.)
    Quicken Mac Subscription • Quicken user since 1993
  • nello
    nello Member ✭✭
    > @Jon said:
    > Are those transactions that were part of the Mint import? I'm not sure those rules would apply to that.

    Yes, but I created a new Rule (companyName2 → CompanyName3) and it doesn’t fire when manually entered in either a normal credit card or normal checking account.
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Renaming rules are for downloaded transactions; they don't work on manually-entered transactions. I think Quicken assumes that if you're entering the transaction manually, you'll give the Payee the name you want. Renaming is for changing the Payee name upon download of transactions from an online source.

    And I agree with @Jon that it's unlikely they would have built the Mint import feature to use the rules. For one thing, the Mint import code was written before the addition of Renaming Rules, and I doubt they went back and re-wrote the Mint import code. Also, I'd guess they figured that whatever you had in Mint is what you'd want to have in Quicken; they wouldn't want to go changing your Payee names. Keep in mind that the Mint import was built for the specific purpose of being used one time for users migrating their data from Mint to Quicken. 

    From your ongoing posts here, I think you'd likely be better served by one of the commercial converters which do CSV to QFX.
    Quicken Mac Subscription • Quicken user since 1993
  • Jon
    Jon SuperUser, Mac Beta Beta
    edited February 2023 Answer ✓
    Renaming rules don't apply to manually entered transactions, only to downloaded transactions. 

    Quicken Mac subscription. Quicken user since 1990.

  • nello
    nello Member ✭✭
    > @jacobs said:
    >
    > Also, you may be aware, but these renaming rules apply to new transactions as they are downloaded; they won't affect existing transactions in your database. (If the latter is what you're trying to do, post back and we can let you know how to tackle that.)

    What I’m trying to do is change the names when importing a mint.csv. Unfortunately, I don’t see any way to map imported CSV data to Quicken’s “Statement Payee” column. No, data in the mint.csv column “Original Description” maps to nowhere (that I can find).

    But my testing in the post to which you replied was for a Rule with manually entered data, so I guess my testing (as opposed to my intended use) falls in to “the latter” as you called it.
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    nello said:
    What I’m trying to do is change the names when importing a mint.csv. Unfortunately, I don’t see any way to map imported CSV data to Quicken’s “Statement Payee” column. No, data in the mint.csv column “Original Description” maps to nowhere (that I can find).
    Think about the intended use of the Mint import. If I had been using Mint for 5 years, and now decided to switch to Quicken, I wouldn't;t want Quicken mucking up my 5 years of historical transactions; I'd want it to import it as is. And that's what I expect the import it doing.

    You're trying to use the Mint import for ongoing transaction downloading, something it wasn't built to do. (Remember, Quicken doesn't want to support CSV for transaction imports.) So while you can get it to work by getting a CSV file in the right format, it's not going to behave like a regular Quicken transaction download/import. Thus the suggestion to look into a file conversion program. Or lobby your financial institution to provide QFX downloads. 
    Quicken Mac Subscription • Quicken user since 1993
This discussion has been closed.