Bulk insert a flat file from Linux to SQL Server

This blog will demonstrate how to port a flat file data retrieved from Unix environment to SQL Server.


First, create a test table with 2 columns where I will use to store my data.

USE [blockchain]

GO

/****** Object:  Table [dbo].[blockchain]    Script Date: 3/30/2018 1:12:30 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[blockchain](

       [akey] [ntext] NULL,

       [bvalue] [ntext] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Verify if the blockchain table gets created correctly. 
select * from blockchain;

Performing the data porting. To let you know ahead of time, this method will fail. I will explain later on.

BULK INSERT blockchain.dbo.blockchain FROM 'C:\Users\Administrator\Desktop\blockchain\data_output2.txt' WITH (  DATAFILETYPE = 'char', FIELDTERMINATOR =':');

It would import one row because the database all look like one row in notepad even though it looks like many rows in notepad++

From Windows Notepad, the data file looks like the following. That explains it why only 1 row gets imported. The second column stores everything else. 

The flat file was ported out a Linux environment where hex requires. The ROWTERMINATOR can't be set to "\r\n" but should be a hex value of "0x0a".

After changing the parameter to the hex value. The following would works perfectly fine.

BULK INSERT blockchain.dbo.blockchain FROM 'C:\Users\Administrator\Desktop\blockchain\data_output2.txt' WITH (  DATAFILETYPE = 'char', FIELDTERMINATOR =':', FIRSTROW=1,ROWTERMINATOR = '0x0a')

What I learned, The data being ported from look like 2 column from the Notepad++ but on a notepad, all the data presented in only one whole line.  The data will get ported over but only in 1 row and the second column filled up the rest of the data.