Import CSV file to a Database, quickly

Challenge: you have a csv file and you need to import it into a new table in a database. You have 5 mins to get it done. What tools do you use?

@secretgeek

Below are many different solutions to this common problem, for a variety of databases and operating systems.

Table of contents:🔗

Any database with any OS
- Some brute force approaches (common!)
Any database with Windows
Any Database with MacOS
MS SQL Server Specific
SQLite specific
MySQL specific
Postgresql specific
Oracle specific
Hadoop specific
Alternative: Just treat the CSV as SQL

Any database with any OS🔗

Python with Pandas

  import pandas
  from sqlalchemy import create_engine
  engine = create_engine('connection string')
  df = pandas.read_csv("path/to.csv")
  df.to_sql("table_name", engine)

"R"

Via Scott Stanfield:

Load in R with data.table fread() method. It'll impute the data types...gets you 80% of the way there.
csv2db — "The CSV command line loader."
csvsql from csvkit csvsql --db postgresql:///test --tables dogideas --insert data/pets/dogideas.csv
NimbleText — Generate SQL Insert Statements with NimbleText (web version)
DBeaver — Importing a CSV into a database using DBeaver
Datagrip: import/export — "There is a dedicated UI for importing DSV (CSV and TSV) files to the database."
Navicat ($$$)

Some brute force approaches (common!)🔗

Sublime (editor), use Multiple line edit to turn the data into SQL
vim, "A combination of macros, regex, and standard commands that change each line to an insert statement." (via OJ)
(Any editor), use search/replace (perhaps with regular expressions) to turn the data into SQL
(Any spreadsheet), write formulae that combine the data together to turn the data into SQL, "="('" + A1 + "','" + A2..." etc.`

Automate the search/replace approach with NimbleText

  $ONCE
  CREATE TABLE Contacts
  ("<% $row.replace(/[ ]*,[ ]*/g,'" varchar(50) NULL,\n"') %>" varchar(50) NULL)
  GO

  Insert into Contacts
  ('<% $row.replace(/[ ]*,[ ]*/g,"','") %>')
  Values
  $EACH+
  (<% ("'" + $row + "'").replace(/[ ]*,[ ]*/g,"','").replace(/'NULL'/g,'NULL') %>)<% if ($rowNumOne != $numrows) { ',' } %>
  $ONCE
  GO

Any database with Windows🔗

LinqPad — CSV to IEnumerable (or Array) in Linqpad

Microsoft Log Parser

  Logparser -o sql -server 127.0.0.1 -database -createtable on "select * into newtable from C:\Apps\Logs\Logfile.log"

Alteryx ($$$)
- drag in an Input data, point it at the csv file.
- (optional) drag in a Select, use it to configure the column types
- drag in an Output data, point it at the database (and set the table name)
- Run!
Generate SQL Insert Statements with NimbleText (desktop version)

The desktop version lets you use a "real" CSV parser that handles embedded delimiters/line breaks etc. Just switch on the use qualifier option under tools | options.

Any Database with MacOS🔗

(No examples available, so use solutions for "Any database with any OS")

MS SQL Server Specific🔗

Sql Server Management Studio (SSMS) — right click on Database, Tasks, "Import Flat File..."
BCP (Bulk Copy Program)

TSQL: BULK INSERT

  BULK INSERT Sales.Orders
  FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
  WITH ( FIRSTROW = 2, FORMAT='CSV');

Powershell: Four Easy Ways to Import CSV Files to SQL Server with PowerShell

Powershell: Import-DbaCsv from Chrissy LeMaire

  Import-DbaCsv -Path .\housing.csv -SqlInstance sql001 -Database markets -Table housing -Delimiter "`t" -NoHeaderRow

SQL Down under tools — see ReadCSV demo movie

SQLite specific🔗

Tutorial: Import a CSV File Into an SQLite Table

If there are no header names in the csv, then find the column names and run:
```
  create table mytable (header_a, header_b, header_c)
```
Next step (do this whether you needed the previous step or not)
```
  .mode csv
  .import data.csv mytable
```

MySQL specific🔗

Load Data Infile

  LOAD DATA INFILE 'c:/tmp/discounts.csv'
  INTO TABLE discounts
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 ROWS;

PhpMyAdmin

Postgresql specific🔗

Two techniques: Copy command in sql itself, and \copy command in psql (interactive commandline).

Sql-copy — requires root access

 COPY dog_habits FROM '/home/user521/pets/dog_habits.csv' DELIMITER ',' CSV HEADER;

psql \copy — use interactive psql commandline where file permissions are an issue
```
 psql -c "\copy sample FROM '/home/user521/pets/dog_habits.csv' WITH (FORMAT CSV)"
```

Oracle specific🔗

9 Simple Steps to Import CSV into Oracle Table Using Toad
Oracle APEX

"a couple of clicks and we're done"

@APEX/JeffreyKemp.sql

Hadoop specific🔗

@BenBrandwood gives this Hadoop example...

  hive> CREATE EXTERNAL TABLE IF NOT EXISTS test_ext
  > (ID int,
  > DEPT int,
  > NAME string
  > )
  > ROW FORMAT DELIMITED
  > FIELDS TERMINATED BY ','
  > STORED AS TEXTFILE
  > LOCATION '/test';
  OK
  Time taken: 0.395 seconds

Alternative: Just treat the CSV as SQL🔗

TextQL — Execute SQL against CSV or TSV.
q — Run SQL directly on CSV Files
RBQL — Rainbow Query Language, a SQL-like language with JavaScript or Python backend.
PSKit Query — Powershell module lets you run simple queries over objects, including imported with csv.

♥ Love CSV? Hate CSV? Try NimbleText for your CSV transforming needs.