There is very useful tool in QGIS that can import very large CSV files into PostgreSQL rapidly and reliably. The DB Manager’s “Import Vector Layer” tool. Contrary to its highly misleading title it can import CSV files as well. Open the DB Manager (menu Database – DB Manager). Then select the database where you want to store your table and click the “Import layer/file” icon.
From the Import Vector Layer GUI, locate our CSV file on disk and enter the name of your new table in the Table box and click OK. Yes, it’s that simple. Proceeding this, you may need to select an text encoding scheme, files created on Windows often use ISO-8859-1 (Latin-1) instead of UTF-8 encoding. In my case, I was able to import a large statistical data set describing the energy efficiency of 525,500 Irish homes (432 megabytes) into PostgreSQL in ~15 minutes. After the CSV file is imported, you can optionally add it to your project using the DB Manager, right-click the table and select Add to Canvas. Don’t use the “Add PostGIS Layers” menu, it’s not a PostGIS layer.
And one more useful tip. You can convert Tab delimited text to CSV using QGIS. Load a Tab delimited text file into QGIS using the Add Delimited Text Layer GUI, then right click the imported file in the layer panel and save it as a CSV file.
Pingback: Importing CSV files into PostgreSQL using the DB Manager in QGIS | Geo-How-To News
I used to lean on this tool until I noticed that the column definitions were all wrong, and it seemed that the import tool was giving up after only scanning a few records to determine the data type – usually using character varying for most column types – not fun.
I then discovered CSVKIT, which has an amazing function called CSVSQL which will read your whole CSV, determine the precise column types, and create a CREATE TABLE () definition statement, which you can run in PGAdmin, then use the standard copy or import tools to quickly load that data… I’m not looking back!!! https://source.opennews.org/en-US/articles/eleven-awesome-things-you-can-do-csvkit/
Pingback: Converting xls into csv in QGIS with correct column definitions and exporting the data into postgis ~ Geographic Information Systems ~ AsktoWorld.com