SQL File Manipulation

Coding

SQL

This page describes various methods for extracting data from a database into a plain text format (e.g. CSV, JSON, XML etc) ingesting data into a database from a plain text format and manipulating data in a plain text file from within the database. The Database technologies where each method applies are shown by the buttons in the right hand column.

UNIX Text Handling

Extract

MySQL

INTO OUTFILE

Tab separated, newline terminated...

SELECT mycol1,mycol2,mycol3

FROM mytable

INTO OUTFILE '/tmp/myfile.txt';

Comma separated, newline terminated, each field enclosed in double quotes...

SELECT mycol1,mycol2,mycol3

FROM mytable

INTO OUTFILE '/tmp/myfile.csv'

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

Note: You cannot overwrite an existing file using "INTO OUTFILE". Delete the file before running the SQL or ensure the SQL generates a unique outfile name. If you write to an existing file you will see:ERROR 1086 (HY000): File '...' already exists.

Note: If secure_file_priv is set then you can only write to the location returned by:SHOW VARIABLES LIKE "secure_file_priv";If you try to write elsewhere you will see: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Oracle

SPOOL

SET MARKUP csv ON DELIMITER ,

SPOOL myfile.csv

SELECT mycol1,mycol2,mycol3

FROM mytable;

SPOOL OFF

Note: This syntax works in 12.2 and above. For older versions use the alternate syntax shown below...

SET COLSEP ,

SET PAGESIZE 0

SET TRIMSPOOL ON

SET HEADSEP OFF

SET LINESIZE 80

SET NUMW 10

SPOOL myfile.csv

SELECT mycol1,mycol2,mycol3

FROM mytable;

SPOOL OFF

UTL_FILE

Microsoft SQL Server

SQL Server

TODO

PostgreSQL

In psql...

SELECT mycol1,mycol2,mycol3

FROM mytable \g /tmp/myfile.txt

To append to an existing file...

SELECT mycol1,mycol2,mycol3

FROM mytable \g | cat >>/tmp/myfile.txt

Alternate method in psql...

\o /tmp/myfile.txt

SELECT mycol1,mycol2,mycol3

FROM mytable;

\o

From shell...

echo 'SELECT mycol1,mycol2,mycol3 FROM mytable' | psql myDB >> /tmp/myfile.txt

psql

Load

MySQL

TODO

Oracle

SQL*Loader

UTL_FILE

SQL Developer

Microsoft SQL Server

SQL Server

BULK INSERT

BULK INSERT dbo.MyTable

FROM 'C:\data\myfile.csv'

WITH (

FIRSTROW = 2, -- skip header

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

TABLOCK,

CODEPAGE = '65001' -- UTF-8

);

The CSV can be on a local disk or network share

MSSQL PROCEDURE

Optionally wrap this in...CREATE PROC dbo.MyLoadProc ASBEGIN...END;

MSSQL Server Agent

CREATE PROC dbo.LoadCsvFile

@FilePath NVARCHAR(4000)

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'

BULK INSERT dbo.MyTable

FROM ''' + REPLACE(@FilePath,'''','''''') + '''

WITH (

FIRSTROW = 2,

FIELDTERMINATOR = '','',

ROWTERMINATOR = ''\n'',

CODEPAGE = ''65001''

);';

EXEC sys.sp_executesql @sql;

END TRY

BEGIN CATCH

THROW;

END CATCH

END;

OPENROWSET

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

INSERT INTO dbo.MyTable

SELECT *

FROM OPENROWSET(

BULK 'C:\data\myfile.csv',

FORMAT = 'CSV',

FIRSTROW = 2

) AS data;

SSIS

TODO

External Tables

SQL Server 2022+

CREATE EXTERNAL TABLE dbo.MyCsv

(

col1 INT,

col2 VARCHAR(50)

)

WITH (

LOCATION = '/myfile.csv',

DATA_SOURCE = MyDataSource,

FILE_FORMAT = MyCsvFormat

);

INSERT INTO dbo.MyTable

SELECT * FROM dbo.MyCsv;

Powershell

CREATE TABLE dbo.ImportTable

(

Col1 INT,

Col2 VARCHAR(50),

Col3 DATETIME

);

This is the table you will import into. The columns should be appropriately named and be suitable for storing the fields from the CSV file.

CREATE TABLE dbo.CsvImportLog

(

LogID INT IDENTITY PRIMARY KEY,

FileName NVARCHAR(255),

LoadStart DATETIME2,

LoadEnd DATETIME2,

RowsInserted INT,

Status VARCHAR(10),

ErrorMessage NVARCHAR(MAX)

);

This table will collect logging information.

CREATE PROC dbo.LoadCsvFile

@FilePath NVARCHAR(4000),

@OriginalFileName NVARCHAR(255)

AS

BEGIN

SET NOCOUNT ON;

DECLARE

@startTime DATETIME2 = SYSDATETIME(),

@rows INT = 0,

@sql NVARCHAR(MAX);

BEGIN TRY

SET @sql = N'

BULK INSERT dbo.ImportTable

FROM ''' + REPLACE(@FilePath,'''','''''') + '''

WITH (

FIRSTROW = 2,

FIELDTERMINATOR = '','',

ROWTERMINATOR = ''\r\n'',

TABLOCK,

ERRORFILE = ''C:\BulkErrors\' + @OriginalFileName + ''',

MAXERRORS = 100

);';

EXEC sys.sp_executesql @sql;

SET @rows = @@ROWCOUNT;

INSERT dbo.CsvImportLog

(

FileName, LoadStart, LoadEnd,

RowsInserted, Status

)

VALUES

(

@OriginalFileName,

@startTime,

SYSDATETIME(),

@rows,

'SUCCESS'

);

END TRY

BEGIN CATCH

INSERT dbo.CsvImportLog

(

FileName, LoadStart, LoadEnd,

RowsInserted, Status, ErrorMessage

)

VALUES

(

@OriginalFileName,

@startTime,

SYSDATETIME(),

0,

'FAILED',

ERROR_MESSAGE()

);

THROW;

END CATCH

END;

Caution: This is currently untested

$SqlServer = "SQL01"

$Database = "MyDB"

$DropFolder = "\\myfile\drop"

$Archive = "\\myfile\archive"

$ErrorFolder = "\\myfile\error"

$files = Get-ChildItem $DropFolder -Filter "*.csv"

foreach ($file in $files) {

try {

Write-Host "Processing $($file.Name)"

# Prevent double-load

$processing = "$($file.FullName).processing"

Rename-Item $file.FullName $processing

$escapedPath = $processing.Replace("'", "''")

$query = @"

EXEC dbo.LoadCsvFile

@FilePath = N'$escapedPath',

@OriginalFileName = N'$($file.Name)'

"@

Invoke-Sqlcmd `

-ServerInstance $SqlServer `

-Database $Database `

-Query $query `

-QueryTimeout 0

Move-Item $processing (Join-Path $Archive $file.Name)

Write-Host "SUCCESS: $($file.Name)"

}

catch {

Write-Error "FAILED: $($file.Name) - $_"

if (Test-Path $processing) {

Move-Item $processing (Join-Path $ErrorFolder $file.Name)

}

Caution: This is currently untested

Powershell SQL Agent Job

SQL Server Agent service account must have:

Read/write on

\\myfile\drop
\\myfile\archive
\\myfile\error

Write on

C:\BulkErrors

Use the Stored Procedure from above (dbo.LoadCsvFile)

Create Job Shell...

USE msdb;

GO

EXEC sp_add_job

@job_name = N'CSV Import – ImportTable',

@enabled = 1,

@description = N'Automatically imports CSV files into dbo.ImportTable';

GO

Add PowerShell Job Step...

EXEC sp_add_jobstep

@job_name = N'CSV Import – ImportTable',

@step_name = N'Process CSV Files',

@subsystem = N'PowerShell',

@command = N'

$SqlServer = "SQL01"$Database = "MyDB"
$DropFolder = "\\myfile\drop"$Archive = "\\myfile\archive"$ErrorFolder = "\\myfile\error"
$files = Get-ChildItem $DropFolder -Filter "*.csv"
foreach ($file in $files) {    try {        Write-Host "Processing $($file.Name)"
        # Prevent double-load        $processing = "$($file.FullName).processing"        Rename-Item $file.FullName $processing
        $escapedPath = $processing.Replace("'", "''")        $query = @"EXEC dbo.LoadCsvFile    @FilePath = N'$escapedPath',    @OriginalFileName = N'$($file.Name)'"@
        Invoke-Sqlcmd `            -ServerInstance $SqlServer `            -Database $Database `            -Query $query `            -QueryTimeout 0
        Move-Item $processing (Join-Path $Archive $file.Name)        Write-Host "SUCCESS: $($file.Name)"    }    catch {        Write-Error "FAILED: $($file.Name) - $_"
        if (Test-Path $processing) {            Move-Item $processing (Join-Path $ErrorFolder $file.Name)        }    }}

',

@retry_attempts = 0,

@on_success_action = 1, -- Quit with success

@on_fail_action = 2; -- Quit with failure

GO

Add cleanup step...

EXEC sp_add_jobstep

@job_name = N'CSV Import – ImportTable',

@step_name = N'Cleanup Old Error Files',

@subsystem = N'PowerShell',

@command = N'

Get-ChildItem "C:\BulkErrors" -File |

Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-30) } |

Remove-Item -Force

',

@on_success_action = 1;

GO

Create schedule...

EXEC sp_add_schedule

@schedule_name = N'CSV Import – Every 5 Minutes',

@freq_type = 4, -- Daily

@freq_interval = 1,

@freq_subday_type = 4, -- Minutes

@freq_subday_interval = 5,

@active_start_time = 000000;

GO

EXEC sp_attach_schedule

@job_name = N'CSV Import – ImportTable',

@schedule_name = N'CSV Import – Every 5 Minutes';

GO

Attach Job to Server...

EXEC sp_add_jobserver

@job_name = N'CSV Import – ImportTable';

GO

PostgreSQL

TODO

Transform

MySQL

TODO

Oracle

TODO

Microsoft SQL Server

SQL Server

TODO

PostgreSQL

TODO

Bibliography

INTO OUTFILEhttps://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/https://joshuaotwell.com/select-into-outfile-examples-in-mysql/ https://dev.mysql.com/doc/refman/8.0/en/select-into.htmlhttps://stackoverflow.com/questions/9408989/is-there-any-way-to-eliminate-error-1086-hy000-in-mysql/9409069https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql
PostgreSQLhttps://www.postgresql.org/message-id/15392.46537.511719.871128%40elsick.csl.co.ukhttps://stackoverflow.com/questions/5331320/psql-save-results-of-command-to-a-filehttps://dba.stackexchange.com/questions/251507/is-it-possible-to-write-a-function-in-postgresql-which-directly-writes-its-param
RDS PostgreSQL to S3https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html
Oraclehttps://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus