Friday, November 16, 2018

Porting flat file from Linux to SQL Server


Default Bulk Insert do not work until I noticed the file wasn't what I saw from Notepad++. Upon, retrieving the file with Notepad, the data are all in 1 single line.


Create a test table 

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


select * from blockchain;

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 explain it why only 1 row get 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 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')










No comments:

Post a Comment