Import data from TXT to SQL Server via command line on MacOS

Want to import data from TXT file to SQL Server via Command Line Interface (CLI) on MacOS?

Using mssql-cmd, a Command Line Interface (CLI) client for SQL Server, you can import data from TXT file to SQL Server easily and fast. Direct Interaction or Scripting and Automation.

• No need to install SQL Server client library.
• Convenient to deploy to cloud. No need to run installation file, just uncompress and run, immediately.
• Commands are entered into a terminal or command prompt, accessed from local or remote (like SSH).
• Support Windows, Linux, and MacOS.

Download mssql-cmd Macos version
mssql-cmd.darwin-amd64.zip
mssql-cmd.darwin-arm64.zip

Import data from TXT file to SQL Server via command line on MacOS

1. In interactive mode:

mssql-cmd> load loadtype=replace
      > table=media_messages 
      > datafile=/data/media_messages.txt 
      > delimiter=, 
      > quote=" 
      > header=y;

2. In batch mode:

mssql-cmd dsn="pan:withdata@192.168.0.102/test" task=load loadtype=replace table=media_messages datafile=/data/media_messages.txt delimiter=, quote=" header=y quit=y

“loadtype=replace” will delete all data in table before inserting.

See parameters detail.

---

More about mssql-cmd – Command Line Interface (CLI) tool for SQL Server

TXT → SQL Server reference

Type mapping

Value kind	in TXT	in SQL Server
Int	1234, or right-aligned in a fixed-width column	int
Bigint	9007199254740993	bigint
Decimal	1234.56	decimal(p,s)
Float	1.234e+10	float
Text	written as-is; padding depends on the chosen layout	nvarchar(n)
Long text	written as-is	nvarchar(max)
Date	whatever the writer chose	date
Datetime	whatever the writer chose	datetime2
Binary	not representable	varbinary(max)
Boolean	Y / N, or 1 / 0, or TRUE / FALSE	bit

Dialect differences

TXT is a file format and SQL Server is a database server, so the target has to be told the types the file never carried - the rows below are where the two sides diverge.

Property	in TXT	in SQL Server
port	—	1433
identifiers	None by convention	[square brackets]
row limit	—	SELECT TOP n
dates	Whatever the writer produced; no convention is guaranteed	datetime, datetime2, date, time
long text	—	nvarchar(max)
binary	Not supported	varbinary(max)
separator	Whatever was chosen - often fixed column widths	—
encoding	No declaration - UTF-8 works only with a BOM	—
typing	None - every field is text	—
shape	Free form; a parser has to be told the layout	Tables of rows and columns
ceiling	No formal limit	—

Command line

Side	Command
SQL Server	bcp
SQL Server (load)	bcp, or BULK INSERT

Convert your data online, free

Free in-browser converters for CSV, Excel, JSON, XML and SQL: Withdata Online Data Conversion. Large files, batch conversion or scheduling? See the offline desktop apps, or browse all data conversion tools.