• Here is a quick solution, more generic than using a specific format file for the import.

    The first part is a CSV splitter function which is based on Jeff Moden's DelimitedSplit8K, then a simple format file for importing the file line by line. The last bit is the actual import.

    😎

    dbo.DELIMITED_CSV_SPLIT

    CREATE FUNCTION dbo.DELIMITED_CSV_SPLIT

    (

    /*********************************************************************

    Using Jeff Moden's DelimitedSplit8K as a base with the addition

    of a Text Qualifier parameter, @pTxtQualifier CHAR(1) = '"'

    *********************************************************************/

    @pString VARCHAR(8000)

    ,@pDelimiter CHAR(1)

    ,@pTxtQualifier CHAR(1)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    /*********************************************************************

    cteTally, inline Tally table returning a number sequence equivalent

    to the length of the input string.

    *********************************************************************/

    WITH E1(N) AS

    (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ),

    E2(N) AS (SELECT 1 FROM E1 a, E1 b),

    E4(N) AS (SELECT 1 FROM E2 a, E2 b),

    cteTally(N) AS

    (

    SELECT

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E4

    ORDER BY 1 OFFSET 0 ROWS

    FETCH FIRST CAST(LEN(@pString) AS BIGINT) ROWS ONLY

    )

    /********************************************************************

    Retrieve the position (N) and the character code (chrCode)

    for all delimiters (@pDelimiter) and text qualifiers

    (@pTxtQualifier)

    ********************************************************************/

    ,ctePrimer(N,chrCode) AS

    (

    SELECT

    t.N

    ,UNICODE(SUBSTRING(@pString,t.N,1)) AS chrCode

    FROM cteTally t

    WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN

    = @pDelimiter COLLATE Latin1_General_BIN

    OR SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN

    = @pTxtQualifier COLLATE Latin1_General_BIN

    )

    /********************************************************************

    The cteStart encloses the string in virtual delimiters using

    Union All at the beginning and the end. The main body sets the

    IsDelim and IsTxQf flags.

    ********************************************************************/

    ,cteStart(N,IsDelim,IsTQA) AS

    (

    SELECT

    0 AS N

    ,1 AS IsDelim

    ,0 AS IsTxQf

    UNION ALL

    SELECT

    t.N

    ,(1 - SIGN(ABS(t.chrCode - UNICODE(@pDelimiter)))) AS IsDelim

    ,(1 - SIGN(ABS(t.chrCode - UNICODE(@pTxtQualifier)))) AS IsTxQf

    FROM ctePrimer t

    UNION ALL

    SELECT

    LEN(@pString) + 1 AS N

    ,1 AS IsDelim

    ,0 AS IsTxQf

    )

    /********************************************************************

    cteWorkSet:

    Position (N), Delimiter flag (IsDelim), Text Qualifier flag

    (IsTQA) and the running total of the number of appearances of

    Text Qualifiers. The delimiters which are inside Text Qualifiers

    are cancelled out by multiplying the IsDelim flag with the result

    of ( 1 + the running total of IsTQA ) mod 2.

    ********************************************************************/

    ,cteWorkSet(N,IsDelim,IsTQA) AS

    (

    SELECT

    cST.N

    ,cST.IsDelim * ((1+ SUM(cST.IsTQA) OVER

    (PARTITION BY (SELECT NULL) ORDER BY cST.N

    ROWS UNBOUNDED PRECEDING)) % 2) AS IsDelim

    ,((SUM(cST.IsTQA) OVER (PARTITION BY (SELECT NULL)

    ORDER BY cST.N ROWS UNBOUNDED PRECEDING)) % 2) AS IsTQA

    FROM cteStart cST

    ),

    /********************************************************************

    cteWSTQ:

    Using LEAD and LAG to retrieve the offsets for the Text Qualifiers

    and filtering the results by IsDelim = 1 or IsTQA = 1. The set now

    holds all the information needed for correctly splitting the text.

    ********************************************************************/

    cteWSTQ(P_START,IsDelim,NEXT_IsTQA,LAG_IsTQA) AS

    (

    SELECT

    cWS.N AS P_START

    ,cWS.IsDelim AS IsDelim

    ,LEAD(cWS.IsTQA,1,0) OVER (ORDER BY cWS.N) AS NEXT_IsTQA

    ,LAG(cWS.IsTQA,1,0) OVER (ORDER BY cWS.N) AS LAG_IsTQA

    FROM cteWorkSet cWS

    WHERE cWS.IsDelim = 1

    OR cWS.IsTQA = 1

    )

    /********************************************************************

    cteWSLEN:

    Calculate the start and the length of each field

    ********************************************************************/

    ,cteWSLEN(P_START,P_LEN) AS

    (

    SELECT

    (X.P_START + X.NEXT_IsTQA + SIGN(X.P_START)) AS P_START

    ,(LEAD(X.P_START,1,0) OVER (ORDER BY X.P_START) -

    ((X.P_START + X.NEXT_IsTQA) + SIGN(X.P_START) +

    LEAD(X.LAG_IsTQA,1,0) OVER (ORDER BY X.P_START))) AS P_LEN

    FROM cteWSTQ X WHERE X.IsDelim = 1

    )

    /********************************************************************

    Splitting the string using the output of the cteWSLEN, filtering

    it by the length being non-negative value. The NULLIF returns NULL

    if the field is empty.

    ********************************************************************/

    SELECT

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber

    ,NULLIF(REPLACE(SUBSTRING(@pString,cWL.P_START,cWL.P_LEN),@pTxtQualifier+@pTxtQualifier,@pTxtQualifier),'') AS Item

    FROM cteWSLEN cWL

    WHERE cWL.P_LEN > -1

    GO

    IMPORT_BY_LINE_FMT.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="1" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="8000"/>

    </RECORD>

    <ROW>

    <COLUMN SOURCE="1" NAME="RAW_LINE" xsi:type="SQLVARYCHAR"/>

    </ROW>

    </BCPFORMAT>

    Finally the import code

    DECLARE @FILE_PATH_NAME NVARCHAR(1024) = N'C:\ImportCSV\CRCorpDailyReport.txt';

    DECLARE @FORMAT_FILE NVARCHAR(1024) = N'C:\ImportCSV\IMPORT_BY_LINE_FMT.xml';

    DECLARE @pDelimiter CHAR(1) = ',';

    DECLARE @pTxtQualifier CHAR(1) = '"';

    DECLARE @IMPORT_LINES TABLE

    (

    IL_ID INT NOT NULL PRIMARY KEY CLUSTERED

    ,IL_LINE VARCHAR(8000) NOT NULL

    );

    DECLARE @SQL_STR NVARCHAR(MAX) = N'

    ;WITH HEADER AS

    (

    SELECT

    X.RAW_LINE

    FROM OPENROWSET

    (

    BULK ' + NCHAR(39) + @FILE_PATH_NAME + NCHAR(39) + N'

    ,FORMATFILE= ' + NCHAR(39) + @FORMAT_FILE + NCHAR(39) + N'

    ) AS X

    )

    SELECT

    ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS C_NO

    ,X.RAW_LINE

    FROM HEADER X

    ';

    INSERT INTO @IMPORT_LINES (IL_ID,IL_LINE)

    EXEC SP_EXECUTESQL @SQL_STR

    ;WITH BASE_DATA AS

    (

    SELECT

    IL.IL_ID

    ,C.ItemNumber

    ,C.Item

    FROM @IMPORT_LINES IL

    OUTER APPLY dbo.DELIMITED_CSV_SPLIT (IL.IL_LINE,@pDelimiter,@pTxtQualifier) AS C

    )

    SELECT

    BD.IL_ID

    ,MAX(CASE WHEN BD.ItemNumber = 1 THEN BD.Item END) AS COL_01

    ,MAX(CASE WHEN BD.ItemNumber = 2 THEN BD.Item END) AS COL_02

    ,MAX(CASE WHEN BD.ItemNumber = 3 THEN BD.Item END) AS COL_03

    ,MAX(CASE WHEN BD.ItemNumber = 4 THEN BD.Item END) AS COL_04

    ,MAX(CASE WHEN BD.ItemNumber = 5 THEN BD.Item END) AS COL_05

    ,MAX(CASE WHEN BD.ItemNumber = 6 THEN BD.Item END) AS COL_06

    ,MAX(CASE WHEN BD.ItemNumber = 7 THEN BD.Item END) AS COL_07

    FROM BASE_DATA BD

    GROUP BY BD.IL_ID

    Results

    IL_ID COL_01 COL_02 COL_03 COL_04 COL_05 COL_06 COL_07

    ----------- --------------------- ------------------------- ------------------- -------------------------------- -------------- -------------- ----------------

    1 CRCorp Daily Report NULL NULL NULL NULL NULL NULL

    2 Facility Location Purchase Order # Vendor Inventory # Date Ordered Extended Cost

    3 09-Mowtown 495 CRST 09-402A Women's Imaging 327937 BARD PERIPHERAL VASCULAR, INC. 113989 7/25/2014 650

    4 09-Mowtown 495 CRST 09-402A Women's Imaging 327936 WB MASON CO., INC. 112664 7/25/2014 8.64

    5 01-Mowtown 499 CRST 01-302B Oncology 327947 McKesson General Medical n/a 7/25/2014 129.02

    6 04-Rocky Hillside 04-301A OB/GYN(Closets) 327931 CARDINAL 200 (MED/SURG) LLC 115098 7/25/2014 13.23

    7 04-Rocky Hillside 04-301A OB/GYN(Closets) 327931 CARDINAL 200 (MED/SURG) LLC 111690 7/25/2014 11.79

    8 04-Rocky Hillside 04-301A OB/GYN(Closets) 327932 McKesson General Medical 111399 7/25/2014 18.79

    9 04-Rocky Hillside 04-301A OB/GYN(Closets) 327932 McKesson General Medical 111459 7/25/2014 16.8

    10 04-Rocky Hillside 04-301A OB/GYN(Closets) 327932 McKesson General Medical 103946 7/25/2014 74.76

    11 04-Rocky Hillside 04-301A OB/GYN(Closets) 327931 CARDINAL 200 (MED/SURG) LLC 110637 7/25/2014 41.44

    12 04-Rocky Hillside 04-301A OB/GYN(Closets) 327931 CARDINAL 200 (MED/SURG) LLC 110818 7/25/2014 24.01

    13 14-Wartown RRB 14-102A OB/GYN 327933 CARDINAL 200 (MED/SURG) LLC 111160 7/25/2014 14.64

    14 14-Wartown RRB 14-102A OB/GYN 327933 CARDINAL 200 (MED/SURG) LLC 112804 7/25/2014 27.26

    15 14-Wartown RRB 14-102A OB/GYN 327933 CARDINAL 200 (MED/SURG) LLC 110361 7/25/2014 45.96

    16 14-Wartown RRB 14-102A OB/GYN 327934 McKesson General Medical 103946 7/25/2014 74.76

    17 14-Wartown RRB 14-102A OB/GYN 327934 McKesson General Medical 111820 7/25/2014 981.6

    18 14-Wartown RRB 14-102A OB/GYN 327933 CARDINAL 200 (MED/SURG) LLC 110610 7/25/2014 16.11

    19 14-Wartown RRB 14-102A OB/GYN 327934 McKesson General Medical 111553 7/25/2014 19.74

    20 14-Wartown RRB 14-102A OB/GYN 327933 CARDINAL 200 (MED/SURG) LLC 104769 7/25/2014 20.04

    21 14-Wartown RRB 14-102A OB/GYN 327933 CARDINAL 200 (MED/SURG) LLC 115098 7/25/2014 13.23

    22 14-Wartown RRB 14-102A OB/GYN 327934 McKesson General Medical 105923 7/25/2014 90.6

    23 14-Wartown RRB 14-102A OB/GYN 327934 McKesson General Medical 111425 7/25/2014 23.62