How To Open and Manipulate Large CSV Files On A Mac • Hacker News Board

Re: How To Open and Manipulate Large CSV Files On A Mac

#51

Post by throwGuardian » Tue, Nov 05, 2019, 7:33 AM UTC

> Excel for Mac performed well but it is a paid solution so I did not consider it viable for many developers

Because developers handling gigabyte size data, and wanting to reliably manipulate it in a GUI, cannot possibly be expected to pay/afford the $7/month to Microsoft.

That said, the recommended solution is probably the best option for developers, not bedside because it's free, but for the ability to run complex SQL statements, and visualize the results.

If I were to edit this article, that'd be my takeaway: use tool X for snappy vitalization of SQL queries, even on multi gigabyte sized CSVs

Re: How To Open and Manipulate Large CSV Files On A Mac

#52

Post by lerigner » Tue, Nov 05, 2019, 8:15 AM UTC

I wonder how well would Table Tool [1] perform with your large dataset? This is an open source CSV editor for Mac from the developer of Postico, my favorite PostgreSQL client for Mac [2]

[1] https://github.com/jakob/TableTool

[2] https://eggerapps.at/postico/

Re: How To Open and Manipulate Large CSV Files On A Mac

#53

Post by zmmmmm » Tue, Nov 05, 2019, 10:11 AM UTC

rabidrat wrote: post #47

zmmmmm wrote: post #43

There seem to be literally dozens of solutions to do read-only operations but very few to enable comfortable editing of the files in-place in a Unix / command line environment. Seems like a real gap in the software ecosystem atm: - fast - no limit on file size - spreadsheet style layout - command line - easily edit and update individual cell => save I've tried VIM's CSV plugins many times and have never been satisfie…

Have you tried VisiData? https://visidata.org .

Wow, that's exactly what I wanted - thanks!

Re: How To Open and Manipulate Large CSV Files On A Mac

#54

Post by grumpy-cowboy » Tue, Nov 05, 2019, 5:08 PM UTC

zaphirplane wrote: post #42

whydoyoucare wrote: post #31

I am not clear what "manipulate" means here -- what is the author trying to do with the comma separated values? FWIW, I can accomplish csv manipulation using a handful of Unix utilities: sed, awk, cut, and call it a day.

The text in a column can be quoted and inside the quotes there can be escaped quotes or commas. It requires a lot of sed awk skills to merge two columns or delete a column. If at all possible

Yes, the "CSV road to hell":

- comma separated, nothing escaped (crash when 1 column contains a comma)

- comma separated, quotes around all elements, quotes not escaped

- comma separated, double-quotes around all elements, double-quotes not escaped

- comma separated, quotes around some elements, quotes not escaped

- comma separated, double-quotes around some elements, double-quotes not escaped

- comma separated, quotes around all elements, quotes escaped (using '')

- comma separated, double-quotes around all elements, double-quotes escaped (using "")

- comma separated, quotes around some elements, quotes escaped (using '')

- comma separated, double-quotes around some elements, double-quotes escaped (using "")

- comma separated, quotes around all elements, quotes escaped (using \')

- comma separated, double-quotes around all elements, double-quotes escaped (using \")

- comma separated, quotes around some elements, quotes escaped (using \')

- comma separated, double-quotes around some elements, double-quotes escaped (using \")

And the Microsoft format (where comma == semi-colon):

- semi-colon separated, nothing escaped (crash when 1 column contains a comma)

- semi-colon separated, quotes around all elements, quotes not escaped

- semi-colon separated, double-quotes around all elements, double-quotes not escaped

- semi-colon separated, quotes around some elements, quotes not escaped

- semi-colon separated, double-quotes around some elements, double-quotes not escaped

- semi-colon separated, quotes around all elements, quotes escaped (using '')

- semi-colon separated, double-quotes around all elements, double-quotes escaped (using "")

- semi-colon separated, quotes around some elements, quotes escaped (using '')

- semi-colon separated, double-quotes around some elements, double-quotes escaped (using "")

- semi-colon separated, quotes around all elements, quotes escaped (using \')

- semi-colon separated, double-quotes around all elements, double-quotes escaped (using \")

- semi-colon separated, quotes around some elements, quotes escaped (using \')

- semi-colon separated, double-quotes around some elements, double-quotes escaped (using \")

And I'm not talking about some weird custom CSV variants to support multi-lines for example or any other "I want to fit a circle in a square" mentality.

I don't know why people doesn't simply create TSV file (Tab-separated). No characters espacing mess. MUCHHH easier to parse.

EDIT: Formatting

Re: How To Open and Manipulate Large CSV Files On A Mac

#55

Post by jwx48 » Tue, Nov 05, 2019, 9:18 PM UTC

As a huge fan of OpenRefine, I want to say thanks for introducing me to Sequel Pro.

Re: How To Open and Manipulate Large CSV Files On A Mac

#56

Post by ekianjo » Wed, Nov 06, 2019, 2:37 PM UTC

suslik wrote: post #50

ekianjo wrote: post #33

Earlier quoted context omitted.

Or R + Tidyverse will do the job nicely too.

R without tidyverse (which is just sugar) will do just as nicely.

Agree. But tidyverse just makes things a lot more elegant to write.

Re: How To Open and Manipulate Large CSV Files On A Mac

#57

Post by apta » Thu, Nov 07, 2019, 4:43 AM UTC

throwGuardian wrote: post #51

> Excel for Mac performed well but it is a paid solution so I did not consider it viable for many developers Because developers handling gigabyte size data, and wanting to reliably manipulate it in a GUI, cannot possibly be expected to pay/afford the $7/month to Microsoft. That said, the recommended solution is probably the best option for developers, not bedside because it's free, but for the ability to run complex…

> cannot possibly be expected to pay/afford the $7/month to Microsoft.

Especially after paying $2000+ for a mac.