Question

Pro-Tip Search: How do you deal with CSVs?

I deal with a lot of CSVs. I export them from analytics software, from email/CRMs, etc. I almost never want them in Excel, I want them in Google Sheets or even just to copy/paste the values out of it.

I would really like to do this without opening Excel every time for a copy/paste. Has anyone solved this janky painpoint? Pretty close to writing an AppleScript that just moves it to my clipboard after download.

Share
phionahq's avatar
19d

We're working to solve this pretty common pain point you describe. CSVs are pretty messy to work with, and Excel generally makes them worse (incorrect delimiters, non-escaped quotation marks, etc). Going further, in building our CSV parser, we encountered dozens of different ways that CSVs didn't upload correctly if they had been modified in Excel (or even just opened once!) beforehand.

Small plug - we built Phiona to take CSV information (either local files, or from cloud drives like Dropbox or Google Drive) and then send them to Google Sheets. We use it every day to run our business too- there are so many applications where the easiest way to access data is in CSV format, and it's easier to put the data in Phiona and send it to Google Sheets than having to deal with manual copy-pasting!

5 points
maguay's avatar
@maguay (replying to @phionahq )
18d

Interesting, is Phiona syncing data between different spreadsheet sources as well?

2 points
phionahq's avatar
@phionahq (replying to @maguay )
18d

That's right- you can use multiple different spreadsheet options (Google Sheets, Airtable, XLS/CSV files on Dropbox) as both a source and destination. You can set them to sync on a schedule or trigger them to sync via webhook.

We've added support for different databases as well- lots of folks want to pull information from a Postgres database into Google Sheets on a regular basis.

2 points
Oshyan's avatar
19d

This must be a bigger problem than I was aware of if someone is developing a product for it. Nonetheless I'll just point out that putting a .csv in Drive and then right-clicking and choosing Open With Sheets will just immediately open it as a spreadsheet. So there's no intrinsic need for Excel as an intermediary step. Obviously weird csv formattings are going to be a problem, Sheets doesn't seem to provide a csv import dialog (though you can always use split to column operations). And of course if you only want a portion of the data you're going to have to select and prune that at some point, in some tool. But it's easy enough in Sheets, unless your concern is not wanting to try to manage a huge data set in an online (slower) tool.

2 points
ezramechaber's avatar
@ezramechaber (replying to @Oshyan )
19d

Interesting - I'm not sure I have this option. I can "View with Google Drive" but I don't think I have a "Open With Sheets" type option within my mac's right-click menu. That would definitely get me far enough.

2 points
Oshyan's avatar
@Oshyan (replying to @ezramechaber )
19d

It has to be already in Drive and viewing from the GDrive web UI...

2 points
ezramechaber's avatar
@ezramechaber (replying to @Oshyan )
19d

Ah, I was trying with my local drive folder! Got it.

2 points
derekzhou's avatar

I assume you want to do something with the CSV files, like some ad-hoc data mining? The best tool for this job is Julia, using packages of CSV + DataFrames + Query https://dataframes.juliadata.org/stable/
You can import/export CSV files, use a SQL like interface to query and manipulate them, all in the command line. Try doing that in excel (or google sheet) to a one million line long CSV with hundreds of fields.
Once I got the CSV beaten down to a manageable size and I want to visualize using a convenient GUI tool I'll skip excel or google sheet, and use tableau.

2 points
bigal123's avatar
19d

I hear your pain. I've been working with CSV dumps for 10+ years now. If I need to get the data into Google Sheets, I would use Google Apps Script and just avoid downloading the CSV altogether and use the analytics software's API (if possible). Otherwise, I would have the CSV dumped to a folder in Google Drive and then use the Google Sheets API to read the data from that file into the Sheet. Lot of different ways to do this, but all of them unfortunately require a little bit of scripting.

1 point
Email-based sales tool

Our current sales process is a very barebones: when a lead completes a form on our website, we send them an automatic email. When the lead responds, we use Front to assign the threads to each other...

Does any software have a "beginner's mode?"

Ever been frustrated learning how to use a new piece of software because of all the options and not sure where to start? Is there any software that can switch to a "beginners" mode? Meaning, hide...

The community for power users.