SQL Bulk Insert

I have a csv file that is sent to me. I have put the file into Notepad++ which shows the ROWTERMINATOR as CRLF. This means that I use rn as my ROWTERMINATOR in SQL correct?

This is the file format (CRLF at the end of each row):

2020/02/10 03:00:00,2020/02/11 02:59:59,2
101,1,5,1,7,10950,0,10950,0,1429
101,1,5,2,7,28878,5500,28878,0,0
101,1,5,3,4,9525,1200,9525,0,0
101,1,5,4,1,7686,0,7686,0,0
101,1,5,7,3,9094,1300,9094,0,0

I essentially need to pull the date from the first cell and then ignore the rest of that row, which I have done:

SET @sql =
'BULK INSERT #date  
FROM ' + '''' + @fileName + '''' + '
WITH
(
    FIRSTROW = 1,
    LASTROW = 1,
    ROWTERMINATOR = ' + '''' + ',' + '''' + ',
    FIELDTERMINATOR = ' + '''' + ',' + '''' + ',
    CODEPAGE=' + '''' + '65001' + '''' + ',
    KEEPNULLS
)'

Then pull the remaining 5 rows of data, which isn’t working because it is wrapping all of the data into one row:

SET @sql =
'BULK INSERT #fileColumns  
FROM ' + '''' + @fileName + '''' + '
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ' + '''' + ',' + '''' + ',
    ROWTERMINATOR = ' + '''' + 'rn' + '''' + ',
    CODEPAGE=' + '''' + '65001' + '''' + ',
    KEEPNULLS
)'

I have also tried n as a ROWTERMINATOR which gives me the last 4 rows because the first row of data is being wrapped into the first.

Answer

I don’t really like the way I had to do this, but it works:

SET @sql =
'BULK INSERT #fileColumns  
FROM ' + '''' + @fileName + '''' + '
WITH
(
    FIRSTROW = 2,
    ROWTERMINATOR = ' + '''' + 'n' + '''' + ',
    CODEPAGE=' + '''' + '65001' + '''' + ',
    KEEPNULLS
)'

EXEC (@sql)

WITH Split AS (
SELECT  Column1,
        Split.value,
        ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY (SELECT NULL)) AS RowNumber
    FROM #fileColumns
    CROSS APPLY STRING_SPLIT(Column1, ',') AS Split
)

SELECT  [1] AS ProfitCenterId,
        [2] AS CheckTypeId,
        [3] AS MealPeriodId,
        [4] AS TenderId,
        [5] AS NumberOfTenders,
        [6] AS TenderAmount,
        [7] AS TipAmount,
        [8] AS ReceivedAmount,
        [9] AS BreakageAmount,
        [10] AS ChangeGiven
        FROM Split
        PIVOT
        (
            MAX(value)
            FOR RowNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
        ) AS piv

Definitely not pretty, but it will do.