查看非常大的CSV文件？ [英] Viewing a very large CSV file?

问题描述

我有一个非常大的3.5 GB CSV文件，我希望能够阅读，排序和过滤基于各种输入的结果。我确定我可以把它导入到一个MySQL数据库，并从那里，但有没有任何程序或在线工具可用，涉及简单地上传CSV，其余的是自动的？

rel =nofollow> file_fdw （File Foreign Data Wrapper），假设该CSV文件是一个表。如果您将CSV文件替换为相同名称的另一个CSV文件，则会立即在数据库中看到新的信息。

您可以使用物化视图（PG 9.3+），它基本上从CSV数据创建了一个真实的数据库表。您可以使用 pgAgent 来刷新物化视图。

另一种替代方法是使用 COPY 语句：

  / *此表中的列与csv中的列相同：* / 
 create table如果不存在my_csv（
 some_field text，... 
）; 
 
 / * COPY追加，因此如果再次加载新数据，请截断表：* / 
 truncate table my_csv; 
 
 / * 
您需要是一个postgres超级用户才能使用COPY 
如果不能是超级用户，则使用psql \copy 
将csv文件放入/ srv / vendor-name / 
 * / 
 
 copy 
 my_csv 
从
'/srv/vendor-name/my.csv'
 with（
 format csv 
）; 
  

I have a very large 3.5 GB CSV file that I'd like to be able to read, sort through and filter for results based on various inputs. I'm pretty sure I can just import it to a MySQL database and go from there, but is there any program or online tool available that involves simply uploading the CSV and the rest is automatic?

解决方案

You could try PostgreSQL 9.1+ and its file_fdw (File Foreign Data Wrapper) which would pretend that the CSV file is a table. If you replaced the CSV file with another CSV file of the same name, then you would see the new info immediately in the database.

You can improve performance by using a materialized view (PG 9.3+) which essentially creates a real database table from the CSV data. You could use pgAgent to refresh the materialized view on a schedule.

Another alternative would be to use the COPY statement:

/* the columns in this table are the same as the columns in your csv: */
create table if not exists my_csv (
  some_field text, ...
);

/* COPY appends, so truncate the table if loading fresh data again: */
truncate table my_csv;

/* 
you need to be a postgres superuser to use COPY 
use psql \copy if you can't be superuser 
put the csv file in /srv/vendor-name/
*/

copy 
  my_csv 
from 
  '/srv/vendor-name/my.csv'
with (
  format csv
);

这篇关于查看非常大的CSV文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！