Hacker News Reader

Comment by nradov | Hacker News Reader

For Windows, Microsoft offers a CSV ODBC driver that can make the files look like any other relational database.

https://support.microsoft.com/en-us/help/850320/creating-an-...

Oracle has a feature called external tables that lets you map regular files to the database and run SQL against them. Works with csv or any format that can be loaded to the database with the normal database loading utilities. This is very useful for getting transformation and loading done in one step!

MySQL has a barebones CSV storage engine: https://dev.mysql.com/doc/refman/8.0/en/se-csv-limitations.h...

More usefully, MySQL has LOAD DATA INFILE facilities for bulk loading of flat files.

The feature actually uses the syntax from the old SQLLDR tool, and can also specify fields in absolute columnar position as well as defining delimiters (reminiscent of gawk). There are also conditionals (when), rounding rules, and other transformations.

A fantastic feature - I've used it regularly in the past to load large log files into the database with incredible performance.

Postgres also has this feature (via file_fwd)

I can't speak to their odbc drivers, but after trying to use their oledb drivers to query excel spreadsheets:

Abandon all hope, all ye who enter here.

I have used the odbc drivers for csv previously. I agree with your assessment, never again. They are unforgiving in what they except and not a robust solution at all. I spent months cleaning up edge cases the driver should have been able to handle.

The built-in tooling for CSV in PowerShell is excellent (provided you don't care about details like if numbers are quoted or not) - I've been using that for csv-related ETL tasks.

It seems most systems that support multiple database drivers include or allow to be built drivers for delimited text. DBI, JDBC...

Reply on news.ycombinator.com