CSV Exports Open As One Column in Excel - Help!

balthus
balthus Member ✭✭✭
edited March 2022 in Reports (Mac)
Hi - I only do this once a year at tax prep time and every year I have an issue but forget the solution or workaround from prior year. I have exported various reports for 2021 - Transaction, Category, Tax Schedule, etc to CSV but when I "open" the file all the column data is in one column instead of separate columns. I need a Category Summary or Tax Schedule that I can open in Excel and manipulate to send to my accountant. This has bene going on for a few years and is extremely frustrating that I can't get my data. I am using the latest version of Quicken Premier and OS 12.0.1 Thanks for any help.

Answers

  • Jon
    Jon SuperUser, Mac Beta Beta
    If you open one of the CSV files in TextEdit, what does it look like? I don't have Excel, but when I exported last year's Tax Report it looked as expected (values enclosed in quotes & separated by commas) and imported into Numbers without issue.

    Quicken Mac subscription. Quicken user since 1990.

  • balthus
    balthus Member ✭✭✭
    edited February 2022
    Not sure what you mean by open in TextEdit.

    When I display the report in Quicken it looks like the 1st file and when I open the CSV file in Excel it looks like the 2nd file.

    Thanks.
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    @balthus Hmm, this sounds very strange. I have never had any such problems with exported CSV files and opening them in Excel — including the current version. I just tried exporting a few reports, and they worked as expected. A few thoughts about things to try…

    What happens if you open the file in Numbers? Try dragging one of the exported CSV files over the icon for the Numbers application.

    What happens if you launch Excel, then go to File > Open and navigate to one of the CSV files, and select it to Open (instead of double-clicking on the CSV file, or dragging it onto Excel)?
    Quicken Mac Subscription • Quicken user since 1993
  • Jon
    Jon SuperUser, Mac Beta Beta
    edited February 2022
    It looks like Excel is having difficulty parsing the CSV - you're getting different results on a row by row basis. Unfortunately, Quicken doesn't provide any other delimiting options like tab separated values.

    As for opening in TextEdit, just right-click on the file and select Open With > TextEdit. If I'm not mistaken, TextEdit comes with Mac OS & is installed by default so you should have it. It would let you look at the raw text to see if there are any formatting issues in the file.

    If you have Numbers on your Mac, try opening the CSV file with that to see if it has any better luck with the file. If Numbers can read it correctly you could export to an Excel file; not an ideal solution but it would get you up & running.

    The only other thing I can suggest is looking at the import dialog in Excel to see if there any any options related to CSV files that you can experiment with. I wish I could be more specific. Hopefully someone else with Excel will see this & have a better suggestion.

    Quicken Mac subscription. Quicken user since 1990.

  • balthus
    balthus Member ✭✭✭
    Thanks. I have used Quicken for MAC for many many years and it used to be easy to export all reports to Excel. I use a 3rd party application for work where I have to export files as csv and have no issues opening those files with excel. Every years for the past few years come tax time I find myself in same situation and have found workarounds but none of those seem to be working now. I am using Excel for MAC which is part of the Microsoft 365 subscription. I will try the Numbers option tomorrow. I do have it but it’s just foreign to me. Quicken has gotten wonky for me with online account updating and causes me a lot more manual work correcting transactions. I really liked how it worked years ago. Thanks.
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    My Excel is an old version, so I wouldn't rely on it here in pinning this down. But I know some versions of Excel deal with a CSV file differently if you open the file by selecting it from within the program (via File > Open). And Numbers opens the CSV files fine for me as well.
    Quicken Mac Subscription • Quicken user since 1993
  • RickO
    RickO SuperUser, Mac Beta Beta
    edited February 2022
    I'm using the update to date versions of QMac and Excel on macOS Big Sur. If I export a CSV from a Transaction by Category report then double click it to open in Excel, it formats just fine. Looking at the file in TextEdit looks like it has the correct formatting. 

    You could try clicking the Export button on the report, then Copy to Clipboard. Switch over to Excel and paste into a blank spreadsheet. But I really don't expect any different result.

    Another thing you can try is instead of double clicking the exported file or opening it from Excel with File > Open, try importing it to Excel with File > Import. You will be offered the option to import a CSV file. But again, I don't expect the result to be any different.

    You could also try to open it in Google Sheets (docs.google.com/spreadsheets). Create a new blank spreadsheet then File > Import and Upload the .CSV file. That works fine for my test case also.

    I'm kind of at a loss to explain why it's not opening correctly in your copy of Excel. If you'd be willing to share a .CSV file generated from a small report that doesn't have too much personal info, I can try opening it on my Excel and see if it works. If you want to do this, send me a PM with a link to the file that you've uploaded to a file share service such as Dropbox. (You can' share the the file directly in the PM since this forum software doesn't allow upload of .CSV or .ZIP files.)
    Quicken Mac Subscription; Quicken Mac user since the early 90s
  • balthus
    balthus Member ✭✭✭
    Morning. Thank you so much. The copy to clipboard and then paste in a blank Excel file worked! Awesome. Much appreciated. I tried the File Import but import is greyed out on my Excel. I may send you a small csv just to try on your end sometime the next few days. But again thanks so much for this!
  • RickO
    RickO SuperUser, Mac Beta Beta
    You're welcome.

    Re Import being grayed out: did you have a blank Excel document open? The option would be grayed out if you don't have a document open.
    Quicken Mac Subscription; Quicken Mac user since the early 90s
This discussion has been closed.