CREATE TABLE BULKACT(RAWDATA VARCHAR (50),MoreData varchar(50))
BULK INSERT BULKACT FROM 'c:\Export_o.txt'
           WITH ( 
               DATAFILETYPE = 'char', 
               FIELDTERMINATOR = ' \t', 
               ROWTERMINATOR = '\n', --might be \r if this file came from a unix system!
               FIRSTROW = 1 
            ) 

row1	row1
row2	row2
row3	row3
	row4
roow5	row5
row6	NULL
NULL	row7

GO
-- Minimally-logged bulk load with on-the-fly transformations
INSERT  BULKACT
        WITH (TABLOCK)
        (
        RAWDATA,
        MoreData
        )
SELECT  CASE WHEN TAB.pos > 0 THEN LEFT(Src.Data, TAB.pos - 1) ELSE NULL END,
        CASE WHEN TAB.pos < DATALENGTH(Src.Data) THEN SUBSTRING(Src.Data, TAB.pos + 1, 4000) ELSE NULL END
FROM    OPENROWSET
            (
            BULK 'C:\Users\Paul White\Downloads\Export.txt',            -- Input file
            FORMATFILE = 'C:\Users\Paul White\Downloads\Format.xml',    -- XML Format file
            CODEPAGE = 'RAW',
            FIRSTROW = 0,
            LASTROW = 0,
            MAXERRORS = 0,
            ROWS_PER_BATCH = 0
            ) AS Src
CROSS
APPLY   -- Or use a string-splitting routine
        (SELECT  CHARINDEX(NCHAR(9), Src.Data)) AS TAB (pos)
;

/*
====================================
XML FORMAT FILE CONTENT - Format.XML
====================================

<?xml version="1.0" ?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="01" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="100"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="01" NAME="data" xsi:type="SQLVARYCHAR" />
  </ROW>
</BCPFORMAT>
*/