Post reply

Tally OH! An Improved SQL 8K “CSV Splitter” Function

  • September 7, 2017 at 8:09 am

    #1958569

    rinzana - Thursday, September 7, 2017 8:00 AM

    Thank you for you quick replies:
    I am using the Test 1 script provided 

    -- TEST 1:
    -- This tests for various possible conditions in a string using a comma as the delimiter. The expected results are
    -- laid out in the comments
    --=====================================================================================================================
    --===== Conditionally drop the test tables to make reruns easier for testing.
      -- (this is NOT a part of the solution)
      IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest
    ;
    --===== Create and populate a test table on the fly (this is NOT a part of the solution).
      -- In the following comments, "b" is a blank and "E" is an element in the left to right order.
      -- Double Quotes are used to encapsulate the output of "Item" so that you can see that all blanks
      -- are preserved no matter where they may appear.
    SELECT *
     INTO #JBMTest
     FROM (                --# & type of Return Row(s)
       SELECT 0, NULL        UNION ALL --1 NULL
       SELECT 1, SPACE(0)      UNION ALL --1 b (Empty String)
       SELECT 2, SPACE(1)      UNION ALL --1 b (1 space)
       SELECT 3, SPACE(5)      UNION ALL --1 b (5 spaces)
       SELECT 4, ','        UNION ALL --2 b b (both are empty strings)
       SELECT 5, '55555'       UNION ALL --1 E
       SELECT 6, ',55555'      UNION ALL --2 b E
       SELECT 7, ',55555,'      UNION ALL --3 b E b
       SELECT 8, '55555,'      UNION ALL --2 b B
       SELECT 9, '55555,1'      UNION ALL --2 E E
       SELECT 10, '1,55555'      UNION ALL --2 E E
       SELECT 11, '55555,4444,333,22,1'  UNION ALL --5 E E E E E
       SELECT 12, '55555,4444,,333,22,1'  UNION ALL --6 E E b E E E
       SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b
       SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b
       SELECT 15, ' 4444,55555 '    UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)
       SELECT 16, 'This,is,a,test.'       --E E E E
       ) d (SomeID, SomeValue)
    ;
    --===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
    SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
     FROM #JBMTest test
    CROSS APPLY dbo.DelimitedSplit8K (test.SomeValue, ',') split
    ;

    Rich

    Using the code you posted this runs just fine. What version of sql server are you running?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 7, 2017 at 9:22 am

    #1958589

    I am running MS SQL 2008 R2.

    I'm kind on concerned if the test provided with the function script do not run correctly how will I know if it will run via SSRS sending the string to my Stored Procedure?

    I am listed as DBO on the database I am running the function and script on, could there some other kind of permissions issue?

    Rich

  • September 7, 2017 at 9:39 am

    #1958592

    rinzana - Thursday, September 7, 2017 9:22 AM

    I am running MS SQL 2008 R2.

    I'm kind on concerned if the test provided with the function script do not run correctly how will I know if it will run via SSRS sending the string to my Stored Procedure?

    I am listed as DBO on the database I am running the function and script on, could there some other kind of permissions issue?

    Rich

    Simplify the problem domain. Try this:

    SELECT * FROM dbo.DelimitedSplit8K ('one,two,three,four,five',',') split

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 7, 2017 at 9:48 am

    #1958595

    rinzana - Thursday, September 7, 2017 8:00 AM

    Thank you for you quick replies:
    I am using the Test 1 script provided 

    -- TEST 1:
    -- This tests for various possible conditions in a string using a comma as the delimiter. The expected results are
    -- laid out in the comments
    --=====================================================================================================================
    --===== Conditionally drop the test tables to make reruns easier for testing.
      -- (this is NOT a part of the solution)
      IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest
    ;
    --===== Create and populate a test table on the fly (this is NOT a part of the solution).
      -- In the following comments, "b" is a blank and "E" is an element in the left to right order.
      -- Double Quotes are used to encapsulate the output of "Item" so that you can see that all blanks
      -- are preserved no matter where they may appear.
    SELECT *
     INTO #JBMTest
     FROM (                --# & type of Return Row(s)
       SELECT 0, NULL        UNION ALL --1 NULL
       SELECT 1, SPACE(0)      UNION ALL --1 b (Empty String)
       SELECT 2, SPACE(1)      UNION ALL --1 b (1 space)
       SELECT 3, SPACE(5)      UNION ALL --1 b (5 spaces)
       SELECT 4, ','        UNION ALL --2 b b (both are empty strings)
       SELECT 5, '55555'       UNION ALL --1 E
       SELECT 6, ',55555'      UNION ALL --2 b E
       SELECT 7, ',55555,'      UNION ALL --3 b E b
       SELECT 8, '55555,'      UNION ALL --2 b B
       SELECT 9, '55555,1'      UNION ALL --2 E E
       SELECT 10, '1,55555'      UNION ALL --2 E E
       SELECT 11, '55555,4444,333,22,1'  UNION ALL --5 E E E E E
       SELECT 12, '55555,4444,,333,22,1'  UNION ALL --6 E E b E E E
       SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b
       SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b
       SELECT 15, ' 4444,55555 '    UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)
       SELECT 16, 'This,is,a,test.'       --E E E E
       ) d (SomeID, SomeValue)
    ;
    --===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
    SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
     FROM #JBMTest test
    CROSS APPLY dbo.DelimitedSplit8K (test.SomeValue, ',') split
    ;

    Rich

    This code works fine in SQL management studio. for SSRS, you may need to use global temp table or permanent table

  • September 7, 2017 at 10:01 am

    #1958596

    ChrisM@Work - Thursday, September 7, 2017 9:39 AM

    rinzana - Thursday, September 7, 2017 9:22 AM

    I am running MS SQL 2008 R2.

    I'm kind on concerned if the test provided with the function script do not run correctly how will I know if it will run via SSRS sending the string to my Stored Procedure?

    I am listed as DBO on the database I am running the function and script on, could there some other kind of permissions issue?

    Rich

    Simplify the problem domain. Try this:

    SELECT * FROM dbo.DelimitedSplit8K ('one,two,three,four,five',',') split

    @ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl13_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork" style="text-decoration: none; color: rgb(34, 85, 136); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@Work
    This script looks to run fine:

  • September 7, 2017 at 10:11 am

    #1958603

    Avi1 - Thursday, September 7, 2017 9:48 AM

    rinzana - Thursday, September 7, 2017 8:00 AM

    Thank you for you quick replies:
    I am using the Test 1 script provided 

    -- TEST 1:
    -- This tests for various possible conditions in a string using a comma as the delimiter. The expected results are
    -- laid out in the comments
    --=====================================================================================================================
    --===== Conditionally drop the test tables to make reruns easier for testing.
      -- (this is NOT a part of the solution)
      IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest
    ;
    --===== Create and populate a test table on the fly (this is NOT a part of the solution).
      -- In the following comments, "b" is a blank and "E" is an element in the left to right order.
      -- Double Quotes are used to encapsulate the output of "Item" so that you can see that all blanks
      -- are preserved no matter where they may appear.
    SELECT *
     INTO #JBMTest
     FROM (                --# & type of Return Row(s)
       SELECT 0, NULL        UNION ALL --1 NULL
       SELECT 1, SPACE(0)      UNION ALL --1 b (Empty String)
       SELECT 2, SPACE(1)      UNION ALL --1 b (1 space)
       SELECT 3, SPACE(5)      UNION ALL --1 b (5 spaces)
       SELECT 4, ','        UNION ALL --2 b b (both are empty strings)
       SELECT 5, '55555'       UNION ALL --1 E
       SELECT 6, ',55555'      UNION ALL --2 b E
       SELECT 7, ',55555,'      UNION ALL --3 b E b
       SELECT 8, '55555,'      UNION ALL --2 b B
       SELECT 9, '55555,1'      UNION ALL --2 E E
       SELECT 10, '1,55555'      UNION ALL --2 E E
       SELECT 11, '55555,4444,333,22,1'  UNION ALL --5 E E E E E
       SELECT 12, '55555,4444,,333,22,1'  UNION ALL --6 E E b E E E
       SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b
       SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b
       SELECT 15, ' 4444,55555 '    UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)
       SELECT 16, 'This,is,a,test.'       --E E E E
       ) d (SomeID, SomeValue)
    ;
    --===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
    SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
     FROM #JBMTest test
    CROSS APPLY dbo.DelimitedSplit8K (test.SomeValue, ',') split
    ;

    Rich

    This code works fine in SQL management studio. for SSRS, you may need to use global temp table or permanent table

    Understood, hence the reason for my question - not running via SSRS, trying to run in SQL Management studio:

  • September 7, 2017 at 10:15 am

    #1958604

    rinzana - Thursday, September 7, 2017 10:11 AM

    Avi1 - Thursday, September 7, 2017 9:48 AM

    rinzana - Thursday, September 7, 2017 8:00 AM

    Thank you for you quick replies:
    I am using the Test 1 script provided 

    -- TEST 1:
    -- This tests for various possible conditions in a string using a comma as the delimiter. The expected results are
    -- laid out in the comments
    --=====================================================================================================================
    --===== Conditionally drop the test tables to make reruns easier for testing.
      -- (this is NOT a part of the solution)
      IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest
    ;
    --===== Create and populate a test table on the fly (this is NOT a part of the solution).
      -- In the following comments, "b" is a blank and "E" is an element in the left to right order.
      -- Double Quotes are used to encapsulate the output of "Item" so that you can see that all blanks
      -- are preserved no matter where they may appear.
    SELECT *
     INTO #JBMTest
     FROM (                --# & type of Return Row(s)
       SELECT 0, NULL        UNION ALL --1 NULL
       SELECT 1, SPACE(0)      UNION ALL --1 b (Empty String)
       SELECT 2, SPACE(1)      UNION ALL --1 b (1 space)
       SELECT 3, SPACE(5)      UNION ALL --1 b (5 spaces)
       SELECT 4, ','        UNION ALL --2 b b (both are empty strings)
       SELECT 5, '55555'       UNION ALL --1 E
       SELECT 6, ',55555'      UNION ALL --2 b E
       SELECT 7, ',55555,'      UNION ALL --3 b E b
       SELECT 8, '55555,'      UNION ALL --2 b B
       SELECT 9, '55555,1'      UNION ALL --2 E E
       SELECT 10, '1,55555'      UNION ALL --2 E E
       SELECT 11, '55555,4444,333,22,1'  UNION ALL --5 E E E E E
       SELECT 12, '55555,4444,,333,22,1'  UNION ALL --6 E E b E E E
       SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b
       SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b
       SELECT 15, ' 4444,55555 '    UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)
       SELECT 16, 'This,is,a,test.'       --E E E E
       ) d (SomeID, SomeValue)
    ;
    --===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
    SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
     FROM #JBMTest test
    CROSS APPLY dbo.DelimitedSplit8K (test.SomeValue, ',') split
    ;

    Rich

    This code works fine in SQL management studio. for SSRS, you may need to use global temp table or permanent table

    Understood, hence the reason for my question - not running via SSRS, trying to run in SQL Management studio:

    Again...the code you posted here runs perfectly. You either have something different in your version of DelimitedSplit8K or the code you are running is not the same as you posted.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • September 7, 2017 at 10:24 am

    #1958605

    rinzana - Thursday, September 7, 2017 10:11 AM

    Avi1 - Thursday, September 7, 2017 9:48 AM

    rinzana - Thursday, September 7, 2017 8:00 AM

    Thank you for you quick replies:
    I am using the Test 1 script provided 

    -- TEST 1:
    -- This tests for various possible conditions in a string using a comma as the delimiter. The expected results are
    -- laid out in the comments
    --=====================================================================================================================
    --===== Conditionally drop the test tables to make reruns easier for testing.
      -- (this is NOT a part of the solution)
      IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest
    ;
    --===== Create and populate a test table on the fly (this is NOT a part of the solution).
      -- In the following comments, "b" is a blank and "E" is an element in the left to right order.
      -- Double Quotes are used to encapsulate the output of "Item" so that you can see that all blanks
      -- are preserved no matter where they may appear.
    SELECT *
     INTO #JBMTest
     FROM (                --# & type of Return Row(s)
       SELECT 0, NULL        UNION ALL --1 NULL
       SELECT 1, SPACE(0)      UNION ALL --1 b (Empty String)
       SELECT 2, SPACE(1)      UNION ALL --1 b (1 space)
       SELECT 3, SPACE(5)      UNION ALL --1 b (5 spaces)
       SELECT 4, ','        UNION ALL --2 b b (both are empty strings)
       SELECT 5, '55555'       UNION ALL --1 E
       SELECT 6, ',55555'      UNION ALL --2 b E
       SELECT 7, ',55555,'      UNION ALL --3 b E b
       SELECT 8, '55555,'      UNION ALL --2 b B
       SELECT 9, '55555,1'      UNION ALL --2 E E
       SELECT 10, '1,55555'      UNION ALL --2 E E
       SELECT 11, '55555,4444,333,22,1'  UNION ALL --5 E E E E E
       SELECT 12, '55555,4444,,333,22,1'  UNION ALL --6 E E b E E E
       SELECT 13, ',55555,4444,,333,22,1,' UNION ALL --8 b E E b E E E b
       SELECT 14, ',55555,4444,,,333,22,1,' UNION ALL --9 b E E b b E E E b
       SELECT 15, ' 4444,55555 '    UNION ALL --2 E (w/Leading Space) E (w/Trailing Space)
       SELECT 16, 'This,is,a,test.'       --E E E E
       ) d (SomeID, SomeValue)
    ;
    --===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)
    SELECT test.SomeID, test.SomeValue, split.ItemNumber, Item = QUOTENAME(split.Item,'"')
     FROM #JBMTest test
    CROSS APPLY dbo.DelimitedSplit8K (test.SomeValue, ',') split
    ;

    Rich

    This code works fine in SQL management studio. for SSRS, you may need to use global temp table or permanent table

    Understood, hence the reason for my question - not running via SSRS, trying to run in SQL Management studio:

    Why don't you hover the mouse cursor over the 2 items in the FROM clause that are indicating problems via the red squiggly underline: #JBMTest and dbo.DelimitedSplit8K. A tool-tip should display a more specific error than what SQL Server is reporting. Maybe you are not in the same DB that the TVF exists in? Or perhaps something else that will become clearer once you see why SSMS decided to flag those two items.

    Take care, Solomon.....

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • September 7, 2017 at 2:09 pm

    #1958637

    Thank You all for you assistance - I am not able to get the test query to run in my environment, but when I add the function into the WHERE clause in my SP it is working fine.

    Rich

  • September 7, 2017 at 2:28 pm

    #1958639

    rinzana - Thursday, September 7, 2017 2:09 PM

    Thank You all for you assistance - I am not able to get the test query to run in my environment, but when I add the function into the WHERE clause in my SP it is working fine.

    Rich

    Ok. Did you do what I suggested above and hover the mouse cursor over those two object names (the temp table and the TVF) that are underlined in red by SSMS for some reason? There is a lot of guess-work being done here that doesn't need to be done since SSMS is trying to give you more info. And if that info doesn't make sense to you, then you can post that info back here for us to help with. But as of right now it makes no sense for anyone here to attempt reproducing this until you provide the info that SSMS is providing if you hover over the red-underlined items (specifically those two items).

    Take care, Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • September 7, 2017 at 3:26 pm

    #1958646

    rinzana - Thursday, September 7, 2017 2:09 PM

    Thank You all for you assistance - I am not able to get the test query to run in my environment, but when I add the function into the WHERE clause in my SP it is working fine.

    Rich

    Check your database compatibility level

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • March 19, 2019 at 4:31 am

    #2024875

    Hi Jeff,

    I've just tried out to split a CSV file content using the CSV Splitter using following code.
    I experience an issue to make it work as I expect.
    The point is that, for a field enclosed with double quotes and containing a comma, DelimitedSplit8K function will split the value instead of keeping it as one.

    Here is the code I used. Could you give a try and check if there is something that can be done?
    Thanks in advance.

    DECLARE @CsvFileContent NVARCHAR(MAX);

    SET @CsvFileContent = '1,"Eldon Base for stackable storage shelf, platinum",Muhammed MacIntyre,3,-213.25,38.94,35,Nunavut,Storage & Organization,0.8
    2,"1.7 Cubic Foot Compact ""Cube"" Office Refrigerators",Barry French,293,457.81,208.16,68.02,Nunavut,Appliances,0.58
    3,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl",Barry French,293,46.71,8.69,2.99,Nunavut,Binders and Binder Accessories,0.39
    4,R380,Clay Rozendal,483,1198.97,195.99,3.99,Nunavut,Telephones and Communication,0.58
    5,Holmes HEPA Air Purifier,Carlos Soltero,515,30.94,21.78,5.94,Nunavut,Appliances,0.5
    6,G.E. Longer-Life Indoor Recessed Floodlight Bulbs,Carlos Soltero,515,4.43,6.64,4.95,Nunavut,Office Furnishings,0.37
    7,"Angle-D Binders with Locking Rings, Label Holders",Carl Jackson,613,-54.04,7.3,7.72,Nunavut,Binders and Binder Accessories,0.38
    8,"SAFCO Mobile Desk Side File, Wire Frame",Carl Jackson,613,127.70,42.76,6.22,Nunavut,Storage & Organization,
    9,"SAFCO Commercial Wire Shelving, Black",Monica Federle,643,-695.26,138.14,35,Nunavut,Storage & Organization,
    10,Xerox 198,Dorothy Badders,678,-226.36,4.98,8.33,Nunavut,Paper,0.38
    11,Xerox 1980,Neola Schneider,807,-166.85,4.28,6.18,Nunavut,Paper,0.4
    12,Advantus Map Pennant Flags and Round Head Tacks,Neola Schneider,807,-14.33,3.95,2,Nunavut,Rubber Bands,0.53
    13,Holmes HEPA Air Purifier,Carlos Daly,868,134.72,21.78,5.94,Nunavut,Appliances,0.5
    14,"DS/HD IBM Formatted Diskettes, 200/Pack - Staples",Carlos Daly,868,114.46,47.98,3.61,Nunavut,Computer Peripherals,0.71
    15,"Wilson Jones 1"" Hanging DublLock® Ring Binders",Claudia Miner,933,-4.72,5.28,2.99,Nunavut,Binders and Binder Accessories,0.37
    16,Ultra Commercial Grade Dual Valve Door Closer,Neola Schneider,995,782.91,39.89,3.04,Nunavut,Office Furnishings,0.53
    17,"#10-4 1/8"" x 9 1/2"" Premium Diagonal Seam Envelopes",Allen Rosenblatt,998,93.80,15.74,1.39,Nunavut,Envelopes,0.4
    18,Hon 4-Shelf Metal Bookcases,Sylvia Foulston,1154,440.72,100.98,26.22,Nunavut,Bookcases,0.6
    19,"Lesro Sheffield Collection Coffee Table, End Table, Center Table, Corner Table",Sylvia Foulston,1154,-481.04,71.37,69,Nunavut,Tables,0.68
    20,g520,Jim Radford,1344,-11.68,65.99,5.26,Nunavut,Telephones and Communication,0.59
    21,LX 788,Jim Radford,1344,313.58,155.99,8.99,Nunavut,Telephones and Communication,0.58
    22,Avery 52,Carlos Soltero,1412,26.92,3.69,0.5,Nunavut,Labels,0.38
    23,Plymouth Boxed Rubber Bands by Plymouth,Carlos Soltero,1412,-5.77,4.71,0.7,Nunavut,Rubber Bands,0.8
    24,"GBC Pre-Punched Binding Paper, Plastic, White, 8-1/2"" x 11""",Carl Ludwig,1539,-172.88,15.99,13.18,Nunavut,Binders and Binder Accessories,0.37
    25,"Maxell 3.5"" DS/HD IBM-Formatted Diskettes, 10/Pack",Carl Ludwig,1539,-144.55,4.89,4.93,Nunavut,Computer Peripherals,0.66
    26,Newell 335,Don Miller,1540,5.76,2.88,0.7,Nunavut,Pens & Art Supplies,0.56
    27,SANFORD Liquid Accent Tank-Style Highlighters,Annie Cyprus,1702,4.90,2.84,0.93,Nunavut,Pens & Art Supplies,0.54
    28,Canon PC940 Copier,Carl Ludwig,1761,-547.61,449.99,49,Nunavut,Copiers and Fax,0.38
    29,"Tenex Personal Project File with Scoop Front Design, Black",Carlos Soltero,1792,-5.45,13.48,4.51,Nunavut,Storage & Organization,0.59
    30,Col-Erase® Pencils with Erasers,Grant Carroll,2275,41.67,6.08,1.17,Nunavut,Pens & Art Supplies,0.56
    31,"Imation 3.5"" DS/HD IBM Formatted Diskettes, 10/Pack",Don Miller,2277,-46.03,5.98,4.38,Nunavut,Computer Peripherals,0.75
    32,"White Dual Perf Computer Printout Paper, 2700 Sheets, 1 Part, Heavyweight, 20 lbs., 14 7/8 x 11",Don Miller,2277,33.67,40.99,19.99,Nunavut,Paper,0.36
    33,Self-Adhesive Address Labels for Typewriters by Universal,Alan Barnes,2532,140.01,7.31,0.49,Nunavut,Labels,0.38
    34,Accessory37,Alan Barnes,2532,-78.96,20.99,2.5,Nunavut,Telephones and Communication,0.81
    35,Fuji 5.2GB DVD-RAM,Jack Garza,2631,252.66,40.96,1.99,Nunavut,Computer Peripherals,0.55
    36,Bevis Steel Folding Chairs,Julia West,2757,-1766.01,95.95,74.35,Nunavut,Chairs & Chairmats,0.57
    37,Avery Binder Labels,Eugene Barchas,2791,-236.27,3.89,7.01,Nunavut,Binders and Binder Accessories,0.37
    38,Hon Every-Day® Chair Series Swivel Task Chairs,Eugene Barchas,2791,80.44,120.98,30,Nunavut,Chairs & Chairmats,0.64
    39,"IBM Multi-Purpose Copy Paper, 8 1/2 x 11"", Case",Eugene Barchas,2791,118.94,30.98,5.76,Nunavut,Paper,0.4
    40,Global Troy Executive Leather Low-Back Tilter,Edward Hooks,2976,3424.22,500.98,26,Nunavut,Chairs & Chairmats,0.6
    41,XtraLife® ClearVue Slant-D® Ring Binders by Cardinal,Brad Eason,3232,-11.83,7.84,4.71,Nunavut,Binders and Binder Accessories,0.35
    42,Computer Printout Paper with Letter-Trim Perforations,Nicole Hansen,3524,52.35,18.97,9.03,Nunavut,Paper,0.37
    43,6160,Dorothy Wardle,3908,-180.20,115.99,2.5,Nunavut,Telephones and Communication,0.57
    44,Avery 49,Aaron Bergman,4132,1.32,2.88,0.5,Nunavut,Labels,0.36
    45,Hoover Portapower Portable Vacuum,Jim Radford,4612,-375.64,4.48,49,Nunavut,Appliances,0.6
    46,Timeport L7089,Annie Cyprus,4676,-104.25,125.99,7.69,Nunavut,Telephones and Communication,0.58
    47,Avery 510,Annie Cyprus,4676,85.96,3.75,0.5,Nunavut,Labels,0.37
    48,Xerox 1881,Annie Cyprus,4676,-8.38,12.28,6.47,Nunavut,Paper,0.38
    49,LX 788,Annie Cyprus,4676,1115.69,155.99,8.99,Nunavut,Telephones and Communication,0.58
    50,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl",Annie Cyprus,5284,-3.05,8.69,2.99,Nunavut,Binders and Binder Accessories,0.39
    51,"Memorex 4.7GB DVD-RAM, 3/Pack",Clay Rozendal,5316,514.07,31.78,1.99,Nunavut,Computer Peripherals,0.42
    52,Unpadded Memo Slips,Don Jones,5409,-7.04,3.98,2.97,Nunavut,Paper,0.35
    53,"Adams Telephone Message Book W/Dividers/Space For Phone Numbers, 5 1/4""X8 1/2"", 300/Messages",Beth Thompson,5506,4.41,5.88,3.04,Nunavut,Paper,0.36
    54,"Eldon Expressions Desk Accessory, Wood Pencil Holder, Oak",Frank Price,5569,-0.06,9.65,6.22,Nunavut,Office Furnishings,0.55
    55,Bell Sonecor JB700 Caller ID,Michelle Lonsdale,5607,-50.33,7.99,5.03,Nunavut,Telephones and Communication,0.6
    56,Avery Arch Ring Binders,Ann Chong,5894,87.68,58.1,1.49,Nunavut,Binders and Binder Accessories,0.38
    57,APC 7 Outlet Network SurgeArrest Surge Protector,Ann Chong,5894,-68.22,80.48,4.5,Nunavut,Appliances,0.55
    58,"Deflect-o RollaMat Studded, Beveled Mat for Medium Pile Carpeting",Joy Bell,5925,-354.90,92.23,39.61,Nunavut,Office Furnishings,0.67
    59,Accessory4,Joy Bell,5925,-267.01,85.99,0.99,Nunavut,Telephones and Communication,0.85
    60,Personal Creations Ink Jet Cards and Labels,Skye Norling,6016,3.63,11.48,5.43,Nunavut,Paper,0.36
    61,High Speed Automatic Electric Letter Opener,Barry Weirich,6116,-1759.58,1637.53,24.49,Nunavut,"Scissors, Rulers and Trimmers",0.81
    62,Xerox 1966,Grant Carroll,6182,-116.79,6.48,6.65,Nunavut,Paper,0.36
    63,Xerox 213,Grant Carroll,6182,-67.28,6.48,7.86,Nunavut,Paper,0.37
    64,"Boston Electric Pencil Sharpener, Model 1818, Charcoal Black",Adrian Hane,6535,-19.33,28.15,8.99,Nunavut,Pens & Art Supplies,0.57
    65,Hammermill CopyPlus Copy Paper (20Lb. and 84 Bright),Skye Norling,6884,-61.21,4.98,4.75,Nunavut,Paper,0.36
    66,"Telephone Message Books with Fax/Mobile Section, 5 1/2"" x 3 3/16""",Skye Norling,6884,119.09,6.35,1.02,Nunavut,Paper,0.39
    67,Crate-A-Files ,Andrew Gjertsen,6916,-141.27,10.9,7.46,Nunavut,Storage & Organization,0.59
    68,"Angle-D Binders with Locking Rings, Label Holders",Ralph Knight,6980,-77.28,7.3,7.72,Nunavut,Binders and Binder Accessories,0.38
    69,"80 Minute CD-R Spindle, 100/Pack - Staples",Dorothy Wardle,6982,407.44,39.48,1.99,Nunavut,Computer Peripherals,0.54
    70,"Bush Westfield Collection Bookcases, Dark Cherry Finish, Fully Assembled",Dorothy Wardle,6982,-338.27,100.98,57.38,Nunavut,Bookcases,0.78
    71,12-1/2 Diameter Round Wall Clock,Dorothy Wardle,6982,52.56,19.98,10.49,Nunavut,Office Furnishings,0.49
    72,SAFCO Arco Folding Chair,Grant Carroll,7110,1902.24,276.2,24.49,Nunavut,Chairs & Chairmats,
    73,"#10 White Business Envelopes,4 1/8 x 9 1/2",Barry Weirich,7430,353.20,15.67,1.39,Nunavut,Envelopes,0.38
    74,3M Office Air Cleaner,Beth Paige,7906,271.78,25.98,5.37,Nunavut,Appliances,0.5
    75,"Global Leather and Oak Executive Chair, Black",Sylvia Foulston,8391,-268.36,300.98,64.73,Nunavut,Chairs & Chairmats,0.56
    76,Xerox 1936,Nicole Hansen,8419,70.39,19.98,5.97,Nunavut,Paper,0.38
    77,Xerox 214,Nicole Hansen,8419,-86.62,6.48,7.03,Nunavut,Paper,0.37
    78,Carina Double Wide Media Storage Towers in Natural & Black,Nicole Hansen,8833,-846.73,80.98,35,Nunavut,Storage & Organization,0.81
    79,Staples® General Use 3-Ring Binders,Beth Paige,8995,8.05,1.88,1.49,Nunavut,Binders and Binder Accessories,0.37
    80,Xerox 1904,Beth Paige,8995,-78.02,6.48,5.86,Northwest Territories,Paper,0.36
    81,Luxo Professional Combination Clamp-On Lamps,Beth Paige,8995,737.94,102.3,21.26,Northwest Territories,Office Furnishings,0.59
    82,Xerox 217,Beth Paige,8995,-191.28,6.48,8.19,Northwest Territories,Paper,0.37
    83,Revere Boxed Rubber Bands by Revere,Beth Paige,8995,-21.49,1.89,0.76,Northwest Territories,Rubber Bands,0.83
    84,"Acco Smartsocket Table Surge Protector, 6 Color-Coded Adapter Outlets",Sylvia Foulston,9126,884.08,62.05,3.99,Northwest Territories,Appliances,0.55
    85,"Tennsco Snap-Together Open Shelving Units, Starter Sets and Add-On Units",Bryan Davis,9127,-329.49,279.48,35,Northwest Territories,Storage & Organization,0.8
    86,Hon 4070 Series Pagoda Round Back Stacking Chairs,Joy Bell,9509,2825.15,320.98,58.95,Northwest Territories,Chairs & Chairmats,0.57
    87,Xerox 1887,Joy Bell,9509,2.13,18.97,5.21,Northwest Territories,Paper,0.37
    88,Xerox 1891,Joy Bell,9509,707.15,48.91,5.81,Northwest Territories,Paper,0.38
    89,Avery 506,Alan Barnes,9763,75.13,4.13,0.5,Northwest Territories,Labels,0.39
    90,"Bush Heritage Pine Collection 5-Shelf Bookcase, Albany Pine Finish, *Special Order",Grant Carroll,9927,-270.63,140.98,53.48,Northwest Territories,Bookcases,0.65
    91,"Lifetime Advantage Folding Chairs, 4/Carton",Grant Carroll,9927,3387.35,218.08,18.06,Northwest Territories,Chairs & Chairmats,0.57
    92,Microsoft Natural Multimedia Keyboard,Grant Carroll,9927,-82.16,50.98,6.5,Northwest Territories,Computer Peripherals,0.73
    93,"Staples Wirebound Steno Books, 6"" x 9"", 12/Pack",Delfina Latchford,10022,-3.88,10.14,2.27,Northwest Territories,Paper,0.36
    94,"GBC Pre-Punched Binding Paper, Plastic, White, 8-1/2"" x 11""",Don Jones,10437,-191.22,15.99,13.18,Northwest Territories,Binders and Binder Accessories,0.37
    95,Bevis Boat-Shaped Conference Table,Doug Bickford,10499,31.21,262.11,62.74,Northwest Territories,Tables,0.75
    96,"Linden® 12"" Wall Clock With Oak Frame",Doug Bickford,10535,-44.14,33.98,19.99,Northwest Territories,Office Furnishings,0.55
    97,Newell 326,Doug Bickford,10535,-0.79,1.76,0.7,Northwest Territories,Pens & Art Supplies,0.56
    98,Prismacolor Color Pencil Set,Jamie Kunitz,10789,76.42,19.84,4.1,Northwest Territories,Pens & Art Supplies,0.44
    99,Xerox Blank Computer Paper,Anthony Johnson,10791,93.36,19.98,5.77,Northwest Territories,Paper,0.38
    100,600 Series Flip,Ralph Knight,10945,4.22,95.99,8.99,Northwest Territories,Telephones and Communication,0.57'
    ;

    With SplittedRows
    AS (
      SELECT ItemNumber,Item
      FROM Utils.DelimitedSplit8K(@CsvFileContent ,CHAR(10))
    )
    SELECT sr.ItemNumber as RowNumber, sr.Item as RowContent, ctnt.ItemNumber as ColumnNumber, ctnt.Item as ColumnValue
    FROM SplittedRows sr CROSS APPLY Utils.DelimitedSplit8K(Item ,',') ctnt
    ;

  • March 19, 2019 at 6:20 am

    #2024881

    Jefferson Elias - Tuesday, March 19, 2019 4:31 AM

    Hi Jeff,

    I've just tried out to split a CSV file content using the CSV Splitter using following code.
    I experience an issue to make it work as I expect.
    The point is that, for a field enclosed with double quotes and containing a comma, DelimitedSplit8K function will split the value instead of keeping it as one.

    Here is the code I used. Could you give a try and check if there is something that can be done?
    Thanks in advance.

    DECLARE @CsvFileContent NVARCHAR(MAX);

    SET @CsvFileContent = '1,"Eldon Base for stackable storage shelf, platinum",Muhammed MacIntyre,3,-213.25,38.94,35,Nunavut,Storage & Organization,0.8
    2,"1.7 Cubic Foot Compact ""Cube"" Office Refrigerators",Barry French,293,457.81,208.16,68.02,Nunavut,Appliances,0.58
    3,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl",Barry French,293,46.71,8.69,2.99,Nunavut,Binders and Binder Accessories,0.39
    4,R380,Clay Rozendal,483,1198.97,195.99,3.99,Nunavut,Telephones and Communication,0.58
    5,Holmes HEPA Air Purifier,Carlos Soltero,515,30.94,21.78,5.94,Nunavut,Appliances,0.5
    6,G.E. Longer-Life Indoor Recessed Floodlight Bulbs,Carlos Soltero,515,4.43,6.64,4.95,Nunavut,Office Furnishings,0.37
    7,"Angle-D Binders with Locking Rings, Label Holders",Carl Jackson,613,-54.04,7.3,7.72,Nunavut,Binders and Binder Accessories,0.38
    8,"SAFCO Mobile Desk Side File, Wire Frame",Carl Jackson,613,127.70,42.76,6.22,Nunavut,Storage & Organization,
    9,"SAFCO Commercial Wire Shelving, Black",Monica Federle,643,-695.26,138.14,35,Nunavut,Storage & Organization,
    10,Xerox 198,Dorothy Badders,678,-226.36,4.98,8.33,Nunavut,Paper,0.38
    11,Xerox 1980,Neola Schneider,807,-166.85,4.28,6.18,Nunavut,Paper,0.4
    12,Advantus Map Pennant Flags and Round Head Tacks,Neola Schneider,807,-14.33,3.95,2,Nunavut,Rubber Bands,0.53
    13,Holmes HEPA Air Purifier,Carlos Daly,868,134.72,21.78,5.94,Nunavut,Appliances,0.5
    14,"DS/HD IBM Formatted Diskettes, 200/Pack - Staples",Carlos Daly,868,114.46,47.98,3.61,Nunavut,Computer Peripherals,0.71
    15,"Wilson Jones 1"" Hanging DublLock® Ring Binders",Claudia Miner,933,-4.72,5.28,2.99,Nunavut,Binders and Binder Accessories,0.37
    16,Ultra Commercial Grade Dual Valve Door Closer,Neola Schneider,995,782.91,39.89,3.04,Nunavut,Office Furnishings,0.53
    17,"#10-4 1/8"" x 9 1/2"" Premium Diagonal Seam Envelopes",Allen Rosenblatt,998,93.80,15.74,1.39,Nunavut,Envelopes,0.4
    18,Hon 4-Shelf Metal Bookcases,Sylvia Foulston,1154,440.72,100.98,26.22,Nunavut,Bookcases,0.6
    19,"Lesro Sheffield Collection Coffee Table, End Table, Center Table, Corner Table",Sylvia Foulston,1154,-481.04,71.37,69,Nunavut,Tables,0.68
    20,g520,Jim Radford,1344,-11.68,65.99,5.26,Nunavut,Telephones and Communication,0.59
    21,LX 788,Jim Radford,1344,313.58,155.99,8.99,Nunavut,Telephones and Communication,0.58
    22,Avery 52,Carlos Soltero,1412,26.92,3.69,0.5,Nunavut,Labels,0.38
    23,Plymouth Boxed Rubber Bands by Plymouth,Carlos Soltero,1412,-5.77,4.71,0.7,Nunavut,Rubber Bands,0.8
    24,"GBC Pre-Punched Binding Paper, Plastic, White, 8-1/2"" x 11""",Carl Ludwig,1539,-172.88,15.99,13.18,Nunavut,Binders and Binder Accessories,0.37
    25,"Maxell 3.5"" DS/HD IBM-Formatted Diskettes, 10/Pack",Carl Ludwig,1539,-144.55,4.89,4.93,Nunavut,Computer Peripherals,0.66
    26,Newell 335,Don Miller,1540,5.76,2.88,0.7,Nunavut,Pens & Art Supplies,0.56
    27,SANFORD Liquid Accent Tank-Style Highlighters,Annie Cyprus,1702,4.90,2.84,0.93,Nunavut,Pens & Art Supplies,0.54
    28,Canon PC940 Copier,Carl Ludwig,1761,-547.61,449.99,49,Nunavut,Copiers and Fax,0.38
    29,"Tenex Personal Project File with Scoop Front Design, Black",Carlos Soltero,1792,-5.45,13.48,4.51,Nunavut,Storage & Organization,0.59
    30,Col-Erase® Pencils with Erasers,Grant Carroll,2275,41.67,6.08,1.17,Nunavut,Pens & Art Supplies,0.56
    31,"Imation 3.5"" DS/HD IBM Formatted Diskettes, 10/Pack",Don Miller,2277,-46.03,5.98,4.38,Nunavut,Computer Peripherals,0.75
    32,"White Dual Perf Computer Printout Paper, 2700 Sheets, 1 Part, Heavyweight, 20 lbs., 14 7/8 x 11",Don Miller,2277,33.67,40.99,19.99,Nunavut,Paper,0.36
    33,Self-Adhesive Address Labels for Typewriters by Universal,Alan Barnes,2532,140.01,7.31,0.49,Nunavut,Labels,0.38
    34,Accessory37,Alan Barnes,2532,-78.96,20.99,2.5,Nunavut,Telephones and Communication,0.81
    35,Fuji 5.2GB DVD-RAM,Jack Garza,2631,252.66,40.96,1.99,Nunavut,Computer Peripherals,0.55
    36,Bevis Steel Folding Chairs,Julia West,2757,-1766.01,95.95,74.35,Nunavut,Chairs & Chairmats,0.57
    37,Avery Binder Labels,Eugene Barchas,2791,-236.27,3.89,7.01,Nunavut,Binders and Binder Accessories,0.37
    38,Hon Every-Day® Chair Series Swivel Task Chairs,Eugene Barchas,2791,80.44,120.98,30,Nunavut,Chairs & Chairmats,0.64
    39,"IBM Multi-Purpose Copy Paper, 8 1/2 x 11"", Case",Eugene Barchas,2791,118.94,30.98,5.76,Nunavut,Paper,0.4
    40,Global Troy Executive Leather Low-Back Tilter,Edward Hooks,2976,3424.22,500.98,26,Nunavut,Chairs & Chairmats,0.6
    41,XtraLife® ClearVue Slant-D® Ring Binders by Cardinal,Brad Eason,3232,-11.83,7.84,4.71,Nunavut,Binders and Binder Accessories,0.35
    42,Computer Printout Paper with Letter-Trim Perforations,Nicole Hansen,3524,52.35,18.97,9.03,Nunavut,Paper,0.37
    43,6160,Dorothy Wardle,3908,-180.20,115.99,2.5,Nunavut,Telephones and Communication,0.57
    44,Avery 49,Aaron Bergman,4132,1.32,2.88,0.5,Nunavut,Labels,0.36
    45,Hoover Portapower Portable Vacuum,Jim Radford,4612,-375.64,4.48,49,Nunavut,Appliances,0.6
    46,Timeport L7089,Annie Cyprus,4676,-104.25,125.99,7.69,Nunavut,Telephones and Communication,0.58
    47,Avery 510,Annie Cyprus,4676,85.96,3.75,0.5,Nunavut,Labels,0.37
    48,Xerox 1881,Annie Cyprus,4676,-8.38,12.28,6.47,Nunavut,Paper,0.38
    49,LX 788,Annie Cyprus,4676,1115.69,155.99,8.99,Nunavut,Telephones and Communication,0.58
    50,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl",Annie Cyprus,5284,-3.05,8.69,2.99,Nunavut,Binders and Binder Accessories,0.39
    51,"Memorex 4.7GB DVD-RAM, 3/Pack",Clay Rozendal,5316,514.07,31.78,1.99,Nunavut,Computer Peripherals,0.42
    52,Unpadded Memo Slips,Don Jones,5409,-7.04,3.98,2.97,Nunavut,Paper,0.35
    53,"Adams Telephone Message Book W/Dividers/Space For Phone Numbers, 5 1/4""X8 1/2"", 300/Messages",Beth Thompson,5506,4.41,5.88,3.04,Nunavut,Paper,0.36
    54,"Eldon Expressions Desk Accessory, Wood Pencil Holder, Oak",Frank Price,5569,-0.06,9.65,6.22,Nunavut,Office Furnishings,0.55
    55,Bell Sonecor JB700 Caller ID,Michelle Lonsdale,5607,-50.33,7.99,5.03,Nunavut,Telephones and Communication,0.6
    56,Avery Arch Ring Binders,Ann Chong,5894,87.68,58.1,1.49,Nunavut,Binders and Binder Accessories,0.38
    57,APC 7 Outlet Network SurgeArrest Surge Protector,Ann Chong,5894,-68.22,80.48,4.5,Nunavut,Appliances,0.55
    58,"Deflect-o RollaMat Studded, Beveled Mat for Medium Pile Carpeting",Joy Bell,5925,-354.90,92.23,39.61,Nunavut,Office Furnishings,0.67
    59,Accessory4,Joy Bell,5925,-267.01,85.99,0.99,Nunavut,Telephones and Communication,0.85
    60,Personal Creations Ink Jet Cards and Labels,Skye Norling,6016,3.63,11.48,5.43,Nunavut,Paper,0.36
    61,High Speed Automatic Electric Letter Opener,Barry Weirich,6116,-1759.58,1637.53,24.49,Nunavut,"Scissors, Rulers and Trimmers",0.81
    62,Xerox 1966,Grant Carroll,6182,-116.79,6.48,6.65,Nunavut,Paper,0.36
    63,Xerox 213,Grant Carroll,6182,-67.28,6.48,7.86,Nunavut,Paper,0.37
    64,"Boston Electric Pencil Sharpener, Model 1818, Charcoal Black",Adrian Hane,6535,-19.33,28.15,8.99,Nunavut,Pens & Art Supplies,0.57
    65,Hammermill CopyPlus Copy Paper (20Lb. and 84 Bright),Skye Norling,6884,-61.21,4.98,4.75,Nunavut,Paper,0.36
    66,"Telephone Message Books with Fax/Mobile Section, 5 1/2"" x 3 3/16""",Skye Norling,6884,119.09,6.35,1.02,Nunavut,Paper,0.39
    67,Crate-A-Files ,Andrew Gjertsen,6916,-141.27,10.9,7.46,Nunavut,Storage & Organization,0.59
    68,"Angle-D Binders with Locking Rings, Label Holders",Ralph Knight,6980,-77.28,7.3,7.72,Nunavut,Binders and Binder Accessories,0.38
    69,"80 Minute CD-R Spindle, 100/Pack - Staples",Dorothy Wardle,6982,407.44,39.48,1.99,Nunavut,Computer Peripherals,0.54
    70,"Bush Westfield Collection Bookcases, Dark Cherry Finish, Fully Assembled",Dorothy Wardle,6982,-338.27,100.98,57.38,Nunavut,Bookcases,0.78
    71,12-1/2 Diameter Round Wall Clock,Dorothy Wardle,6982,52.56,19.98,10.49,Nunavut,Office Furnishings,0.49
    72,SAFCO Arco Folding Chair,Grant Carroll,7110,1902.24,276.2,24.49,Nunavut,Chairs & Chairmats,
    73,"#10 White Business Envelopes,4 1/8 x 9 1/2",Barry Weirich,7430,353.20,15.67,1.39,Nunavut,Envelopes,0.38
    74,3M Office Air Cleaner,Beth Paige,7906,271.78,25.98,5.37,Nunavut,Appliances,0.5
    75,"Global Leather and Oak Executive Chair, Black",Sylvia Foulston,8391,-268.36,300.98,64.73,Nunavut,Chairs & Chairmats,0.56
    76,Xerox 1936,Nicole Hansen,8419,70.39,19.98,5.97,Nunavut,Paper,0.38
    77,Xerox 214,Nicole Hansen,8419,-86.62,6.48,7.03,Nunavut,Paper,0.37
    78,Carina Double Wide Media Storage Towers in Natural & Black,Nicole Hansen,8833,-846.73,80.98,35,Nunavut,Storage & Organization,0.81
    79,Staples® General Use 3-Ring Binders,Beth Paige,8995,8.05,1.88,1.49,Nunavut,Binders and Binder Accessories,0.37
    80,Xerox 1904,Beth Paige,8995,-78.02,6.48,5.86,Northwest Territories,Paper,0.36
    81,Luxo Professional Combination Clamp-On Lamps,Beth Paige,8995,737.94,102.3,21.26,Northwest Territories,Office Furnishings,0.59
    82,Xerox 217,Beth Paige,8995,-191.28,6.48,8.19,Northwest Territories,Paper,0.37
    83,Revere Boxed Rubber Bands by Revere,Beth Paige,8995,-21.49,1.89,0.76,Northwest Territories,Rubber Bands,0.83
    84,"Acco Smartsocket Table Surge Protector, 6 Color-Coded Adapter Outlets",Sylvia Foulston,9126,884.08,62.05,3.99,Northwest Territories,Appliances,0.55
    85,"Tennsco Snap-Together Open Shelving Units, Starter Sets and Add-On Units",Bryan Davis,9127,-329.49,279.48,35,Northwest Territories,Storage & Organization,0.8
    86,Hon 4070 Series Pagoda Round Back Stacking Chairs,Joy Bell,9509,2825.15,320.98,58.95,Northwest Territories,Chairs & Chairmats,0.57
    87,Xerox 1887,Joy Bell,9509,2.13,18.97,5.21,Northwest Territories,Paper,0.37
    88,Xerox 1891,Joy Bell,9509,707.15,48.91,5.81,Northwest Territories,Paper,0.38
    89,Avery 506,Alan Barnes,9763,75.13,4.13,0.5,Northwest Territories,Labels,0.39
    90,"Bush Heritage Pine Collection 5-Shelf Bookcase, Albany Pine Finish, *Special Order",Grant Carroll,9927,-270.63,140.98,53.48,Northwest Territories,Bookcases,0.65
    91,"Lifetime Advantage Folding Chairs, 4/Carton",Grant Carroll,9927,3387.35,218.08,18.06,Northwest Territories,Chairs & Chairmats,0.57
    92,Microsoft Natural Multimedia Keyboard,Grant Carroll,9927,-82.16,50.98,6.5,Northwest Territories,Computer Peripherals,0.73
    93,"Staples Wirebound Steno Books, 6"" x 9"", 12/Pack",Delfina Latchford,10022,-3.88,10.14,2.27,Northwest Territories,Paper,0.36
    94,"GBC Pre-Punched Binding Paper, Plastic, White, 8-1/2"" x 11""",Don Jones,10437,-191.22,15.99,13.18,Northwest Territories,Binders and Binder Accessories,0.37
    95,Bevis Boat-Shaped Conference Table,Doug Bickford,10499,31.21,262.11,62.74,Northwest Territories,Tables,0.75
    96,"Linden® 12"" Wall Clock With Oak Frame",Doug Bickford,10535,-44.14,33.98,19.99,Northwest Territories,Office Furnishings,0.55
    97,Newell 326,Doug Bickford,10535,-0.79,1.76,0.7,Northwest Territories,Pens & Art Supplies,0.56
    98,Prismacolor Color Pencil Set,Jamie Kunitz,10789,76.42,19.84,4.1,Northwest Territories,Pens & Art Supplies,0.44
    99,Xerox Blank Computer Paper,Anthony Johnson,10791,93.36,19.98,5.77,Northwest Territories,Paper,0.38
    100,600 Series Flip,Ralph Knight,10945,4.22,95.99,8.99,Northwest Territories,Telephones and Communication,0.57'
    ;

    With SplittedRows
    AS (
      SELECT ItemNumber,Item
      FROM Utils.DelimitedSplit8K(@CsvFileContent ,CHAR(10))
    )
    SELECT sr.ItemNumber as RowNumber, sr.Item as RowContent, ctnt.ItemNumber as ColumnNumber, ctnt.Item as ColumnValue
    FROM SplittedRows sr CROSS APPLY Utils.DelimitedSplit8K(Item ,',') ctnt
    ;

    The DelimitedSplit8K/4K functions are not designed to handle text qualified CSVs (RFC-4180) so this will not work.
    😎
    I wrote an article which tackles this problem a while back, you'll find it here.

  • March 19, 2019 at 7:15 am

    #2024888

    Eirikur Eiriksson - Tuesday, March 19, 2019 6:20 AM

    Jefferson Elias - Tuesday, March 19, 2019 4:31 AM

    Hi Jeff,

    I've just tried out to split a CSV file content using the CSV Splitter using following code.
    I experience an issue to make it work as I expect.
    The point is that, for a field enclosed with double quotes and containing a comma, DelimitedSplit8K function will split the value instead of keeping it as one.

    Here is the code I used. Could you give a try and check if there is something that can be done?
    Thanks in advance.

    DECLARE @CsvFileContent NVARCHAR(MAX);

    SET @CsvFileContent = '1,"Eldon Base for stackable storage shelf, platinum",Muhammed MacIntyre,3,-213.25,38.94,35,Nunavut,Storage & Organization,0.8
    2,"1.7 Cubic Foot Compact ""Cube"" Office Refrigerators",Barry French,293,457.81,208.16,68.02,Nunavut,Appliances,0.58
    3,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl",Barry French,293,46.71,8.69,2.99,Nunavut,Binders and Binder Accessories,0.39
    4,R380,Clay Rozendal,483,1198.97,195.99,3.99,Nunavut,Telephones and Communication,0.58
    5,Holmes HEPA Air Purifier,Carlos Soltero,515,30.94,21.78,5.94,Nunavut,Appliances,0.5
    6,G.E. Longer-Life Indoor Recessed Floodlight Bulbs,Carlos Soltero,515,4.43,6.64,4.95,Nunavut,Office Furnishings,0.37
    7,"Angle-D Binders with Locking Rings, Label Holders",Carl Jackson,613,-54.04,7.3,7.72,Nunavut,Binders and Binder Accessories,0.38
    8,"SAFCO Mobile Desk Side File, Wire Frame",Carl Jackson,613,127.70,42.76,6.22,Nunavut,Storage & Organization,
    9,"SAFCO Commercial Wire Shelving, Black",Monica Federle,643,-695.26,138.14,35,Nunavut,Storage & Organization,
    10,Xerox 198,Dorothy Badders,678,-226.36,4.98,8.33,Nunavut,Paper,0.38
    11,Xerox 1980,Neola Schneider,807,-166.85,4.28,6.18,Nunavut,Paper,0.4
    12,Advantus Map Pennant Flags and Round Head Tacks,Neola Schneider,807,-14.33,3.95,2,Nunavut,Rubber Bands,0.53
    13,Holmes HEPA Air Purifier,Carlos Daly,868,134.72,21.78,5.94,Nunavut,Appliances,0.5
    14,"DS/HD IBM Formatted Diskettes, 200/Pack - Staples",Carlos Daly,868,114.46,47.98,3.61,Nunavut,Computer Peripherals,0.71
    15,"Wilson Jones 1"" Hanging DublLock® Ring Binders",Claudia Miner,933,-4.72,5.28,2.99,Nunavut,Binders and Binder Accessories,0.37
    16,Ultra Commercial Grade Dual Valve Door Closer,Neola Schneider,995,782.91,39.89,3.04,Nunavut,Office Furnishings,0.53
    17,"#10-4 1/8"" x 9 1/2"" Premium Diagonal Seam Envelopes",Allen Rosenblatt,998,93.80,15.74,1.39,Nunavut,Envelopes,0.4
    18,Hon 4-Shelf Metal Bookcases,Sylvia Foulston,1154,440.72,100.98,26.22,Nunavut,Bookcases,0.6
    19,"Lesro Sheffield Collection Coffee Table, End Table, Center Table, Corner Table",Sylvia Foulston,1154,-481.04,71.37,69,Nunavut,Tables,0.68
    20,g520,Jim Radford,1344,-11.68,65.99,5.26,Nunavut,Telephones and Communication,0.59
    21,LX 788,Jim Radford,1344,313.58,155.99,8.99,Nunavut,Telephones and Communication,0.58
    22,Avery 52,Carlos Soltero,1412,26.92,3.69,0.5,Nunavut,Labels,0.38
    23,Plymouth Boxed Rubber Bands by Plymouth,Carlos Soltero,1412,-5.77,4.71,0.7,Nunavut,Rubber Bands,0.8
    24,"GBC Pre-Punched Binding Paper, Plastic, White, 8-1/2"" x 11""",Carl Ludwig,1539,-172.88,15.99,13.18,Nunavut,Binders and Binder Accessories,0.37
    25,"Maxell 3.5"" DS/HD IBM-Formatted Diskettes, 10/Pack",Carl Ludwig,1539,-144.55,4.89,4.93,Nunavut,Computer Peripherals,0.66
    26,Newell 335,Don Miller,1540,5.76,2.88,0.7,Nunavut,Pens & Art Supplies,0.56
    27,SANFORD Liquid Accent Tank-Style Highlighters,Annie Cyprus,1702,4.90,2.84,0.93,Nunavut,Pens & Art Supplies,0.54
    28,Canon PC940 Copier,Carl Ludwig,1761,-547.61,449.99,49,Nunavut,Copiers and Fax,0.38
    29,"Tenex Personal Project File with Scoop Front Design, Black",Carlos Soltero,1792,-5.45,13.48,4.51,Nunavut,Storage & Organization,0.59
    30,Col-Erase® Pencils with Erasers,Grant Carroll,2275,41.67,6.08,1.17,Nunavut,Pens & Art Supplies,0.56
    31,"Imation 3.5"" DS/HD IBM Formatted Diskettes, 10/Pack",Don Miller,2277,-46.03,5.98,4.38,Nunavut,Computer Peripherals,0.75
    32,"White Dual Perf Computer Printout Paper, 2700 Sheets, 1 Part, Heavyweight, 20 lbs., 14 7/8 x 11",Don Miller,2277,33.67,40.99,19.99,Nunavut,Paper,0.36
    33,Self-Adhesive Address Labels for Typewriters by Universal,Alan Barnes,2532,140.01,7.31,0.49,Nunavut,Labels,0.38
    34,Accessory37,Alan Barnes,2532,-78.96,20.99,2.5,Nunavut,Telephones and Communication,0.81
    35,Fuji 5.2GB DVD-RAM,Jack Garza,2631,252.66,40.96,1.99,Nunavut,Computer Peripherals,0.55
    36,Bevis Steel Folding Chairs,Julia West,2757,-1766.01,95.95,74.35,Nunavut,Chairs & Chairmats,0.57
    37,Avery Binder Labels,Eugene Barchas,2791,-236.27,3.89,7.01,Nunavut,Binders and Binder Accessories,0.37
    38,Hon Every-Day® Chair Series Swivel Task Chairs,Eugene Barchas,2791,80.44,120.98,30,Nunavut,Chairs & Chairmats,0.64
    39,"IBM Multi-Purpose Copy Paper, 8 1/2 x 11"", Case",Eugene Barchas,2791,118.94,30.98,5.76,Nunavut,Paper,0.4
    40,Global Troy Executive Leather Low-Back Tilter,Edward Hooks,2976,3424.22,500.98,26,Nunavut,Chairs & Chairmats,0.6
    41,XtraLife® ClearVue Slant-D® Ring Binders by Cardinal,Brad Eason,3232,-11.83,7.84,4.71,Nunavut,Binders and Binder Accessories,0.35
    42,Computer Printout Paper with Letter-Trim Perforations,Nicole Hansen,3524,52.35,18.97,9.03,Nunavut,Paper,0.37
    43,6160,Dorothy Wardle,3908,-180.20,115.99,2.5,Nunavut,Telephones and Communication,0.57
    44,Avery 49,Aaron Bergman,4132,1.32,2.88,0.5,Nunavut,Labels,0.36
    45,Hoover Portapower Portable Vacuum,Jim Radford,4612,-375.64,4.48,49,Nunavut,Appliances,0.6
    46,Timeport L7089,Annie Cyprus,4676,-104.25,125.99,7.69,Nunavut,Telephones and Communication,0.58
    47,Avery 510,Annie Cyprus,4676,85.96,3.75,0.5,Nunavut,Labels,0.37
    48,Xerox 1881,Annie Cyprus,4676,-8.38,12.28,6.47,Nunavut,Paper,0.38
    49,LX 788,Annie Cyprus,4676,1115.69,155.99,8.99,Nunavut,Telephones and Communication,0.58
    50,"Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl",Annie Cyprus,5284,-3.05,8.69,2.99,Nunavut,Binders and Binder Accessories,0.39
    51,"Memorex 4.7GB DVD-RAM, 3/Pack",Clay Rozendal,5316,514.07,31.78,1.99,Nunavut,Computer Peripherals,0.42
    52,Unpadded Memo Slips,Don Jones,5409,-7.04,3.98,2.97,Nunavut,Paper,0.35
    53,"Adams Telephone Message Book W/Dividers/Space For Phone Numbers, 5 1/4""X8 1/2"", 300/Messages",Beth Thompson,5506,4.41,5.88,3.04,Nunavut,Paper,0.36
    54,"Eldon Expressions Desk Accessory, Wood Pencil Holder, Oak",Frank Price,5569,-0.06,9.65,6.22,Nunavut,Office Furnishings,0.55
    55,Bell Sonecor JB700 Caller ID,Michelle Lonsdale,5607,-50.33,7.99,5.03,Nunavut,Telephones and Communication,0.6
    56,Avery Arch Ring Binders,Ann Chong,5894,87.68,58.1,1.49,Nunavut,Binders and Binder Accessories,0.38
    57,APC 7 Outlet Network SurgeArrest Surge Protector,Ann Chong,5894,-68.22,80.48,4.5,Nunavut,Appliances,0.55
    58,"Deflect-o RollaMat Studded, Beveled Mat for Medium Pile Carpeting",Joy Bell,5925,-354.90,92.23,39.61,Nunavut,Office Furnishings,0.67
    59,Accessory4,Joy Bell,5925,-267.01,85.99,0.99,Nunavut,Telephones and Communication,0.85
    60,Personal Creations Ink Jet Cards and Labels,Skye Norling,6016,3.63,11.48,5.43,Nunavut,Paper,0.36
    61,High Speed Automatic Electric Letter Opener,Barry Weirich,6116,-1759.58,1637.53,24.49,Nunavut,"Scissors, Rulers and Trimmers",0.81
    62,Xerox 1966,Grant Carroll,6182,-116.79,6.48,6.65,Nunavut,Paper,0.36
    63,Xerox 213,Grant Carroll,6182,-67.28,6.48,7.86,Nunavut,Paper,0.37
    64,"Boston Electric Pencil Sharpener, Model 1818, Charcoal Black",Adrian Hane,6535,-19.33,28.15,8.99,Nunavut,Pens & Art Supplies,0.57
    65,Hammermill CopyPlus Copy Paper (20Lb. and 84 Bright),Skye Norling,6884,-61.21,4.98,4.75,Nunavut,Paper,0.36
    66,"Telephone Message Books with Fax/Mobile Section, 5 1/2"" x 3 3/16""",Skye Norling,6884,119.09,6.35,1.02,Nunavut,Paper,0.39
    67,Crate-A-Files ,Andrew Gjertsen,6916,-141.27,10.9,7.46,Nunavut,Storage & Organization,0.59
    68,"Angle-D Binders with Locking Rings, Label Holders",Ralph Knight,6980,-77.28,7.3,7.72,Nunavut,Binders and Binder Accessories,0.38
    69,"80 Minute CD-R Spindle, 100/Pack - Staples",Dorothy Wardle,6982,407.44,39.48,1.99,Nunavut,Computer Peripherals,0.54
    70,"Bush Westfield Collection Bookcases, Dark Cherry Finish, Fully Assembled",Dorothy Wardle,6982,-338.27,100.98,57.38,Nunavut,Bookcases,0.78
    71,12-1/2 Diameter Round Wall Clock,Dorothy Wardle,6982,52.56,19.98,10.49,Nunavut,Office Furnishings,0.49
    72,SAFCO Arco Folding Chair,Grant Carroll,7110,1902.24,276.2,24.49,Nunavut,Chairs & Chairmats,
    73,"#10 White Business Envelopes,4 1/8 x 9 1/2",Barry Weirich,7430,353.20,15.67,1.39,Nunavut,Envelopes,0.38
    74,3M Office Air Cleaner,Beth Paige,7906,271.78,25.98,5.37,Nunavut,Appliances,0.5
    75,"Global Leather and Oak Executive Chair, Black",Sylvia Foulston,8391,-268.36,300.98,64.73,Nunavut,Chairs & Chairmats,0.56
    76,Xerox 1936,Nicole Hansen,8419,70.39,19.98,5.97,Nunavut,Paper,0.38
    77,Xerox 214,Nicole Hansen,8419,-86.62,6.48,7.03,Nunavut,Paper,0.37
    78,Carina Double Wide Media Storage Towers in Natural & Black,Nicole Hansen,8833,-846.73,80.98,35,Nunavut,Storage & Organization,0.81
    79,Staples® General Use 3-Ring Binders,Beth Paige,8995,8.05,1.88,1.49,Nunavut,Binders and Binder Accessories,0.37
    80,Xerox 1904,Beth Paige,8995,-78.02,6.48,5.86,Northwest Territories,Paper,0.36
    81,Luxo Professional Combination Clamp-On Lamps,Beth Paige,8995,737.94,102.3,21.26,Northwest Territories,Office Furnishings,0.59
    82,Xerox 217,Beth Paige,8995,-191.28,6.48,8.19,Northwest Territories,Paper,0.37
    83,Revere Boxed Rubber Bands by Revere,Beth Paige,8995,-21.49,1.89,0.76,Northwest Territories,Rubber Bands,0.83
    84,"Acco Smartsocket Table Surge Protector, 6 Color-Coded Adapter Outlets",Sylvia Foulston,9126,884.08,62.05,3.99,Northwest Territories,Appliances,0.55
    85,"Tennsco Snap-Together Open Shelving Units, Starter Sets and Add-On Units",Bryan Davis,9127,-329.49,279.48,35,Northwest Territories,Storage & Organization,0.8
    86,Hon 4070 Series Pagoda Round Back Stacking Chairs,Joy Bell,9509,2825.15,320.98,58.95,Northwest Territories,Chairs & Chairmats,0.57
    87,Xerox 1887,Joy Bell,9509,2.13,18.97,5.21,Northwest Territories,Paper,0.37
    88,Xerox 1891,Joy Bell,9509,707.15,48.91,5.81,Northwest Territories,Paper,0.38
    89,Avery 506,Alan Barnes,9763,75.13,4.13,0.5,Northwest Territories,Labels,0.39
    90,"Bush Heritage Pine Collection 5-Shelf Bookcase, Albany Pine Finish, *Special Order",Grant Carroll,9927,-270.63,140.98,53.48,Northwest Territories,Bookcases,0.65
    91,"Lifetime Advantage Folding Chairs, 4/Carton",Grant Carroll,9927,3387.35,218.08,18.06,Northwest Territories,Chairs & Chairmats,0.57
    92,Microsoft Natural Multimedia Keyboard,Grant Carroll,9927,-82.16,50.98,6.5,Northwest Territories,Computer Peripherals,0.73
    93,"Staples Wirebound Steno Books, 6"" x 9"", 12/Pack",Delfina Latchford,10022,-3.88,10.14,2.27,Northwest Territories,Paper,0.36
    94,"GBC Pre-Punched Binding Paper, Plastic, White, 8-1/2"" x 11""",Don Jones,10437,-191.22,15.99,13.18,Northwest Territories,Binders and Binder Accessories,0.37
    95,Bevis Boat-Shaped Conference Table,Doug Bickford,10499,31.21,262.11,62.74,Northwest Territories,Tables,0.75
    96,"Linden® 12"" Wall Clock With Oak Frame",Doug Bickford,10535,-44.14,33.98,19.99,Northwest Territories,Office Furnishings,0.55
    97,Newell 326,Doug Bickford,10535,-0.79,1.76,0.7,Northwest Territories,Pens & Art Supplies,0.56
    98,Prismacolor Color Pencil Set,Jamie Kunitz,10789,76.42,19.84,4.1,Northwest Territories,Pens & Art Supplies,0.44
    99,Xerox Blank Computer Paper,Anthony Johnson,10791,93.36,19.98,5.77,Northwest Territories,Paper,0.38
    100,600 Series Flip,Ralph Knight,10945,4.22,95.99,8.99,Northwest Territories,Telephones and Communication,0.57'
    ;

    With SplittedRows
    AS (
      SELECT ItemNumber,Item
      FROM Utils.DelimitedSplit8K(@CsvFileContent ,CHAR(10))
    )
    SELECT sr.ItemNumber as RowNumber, sr.Item as RowContent, ctnt.ItemNumber as ColumnNumber, ctnt.Item as ColumnValue
    FROM SplittedRows sr CROSS APPLY Utils.DelimitedSplit8K(Item ,',') ctnt
    ;

    The DelimitedSplit8K/4K functions are not designed to handle text qualified CSVs (RFC-4180) so this will not work.
    😎
    I wrote an article which tackles this problem a while back, you'll find it here.

    To add to Eirikur's good and totally correct response, if you run your file through a simple Powershell Import-CSV and then Export-CSV, all columns will be properly encapsulated in double quotes, which makes the file totally consistent in format, which also means that you can easily build a BCP format file to import the file for use with either BCP or BULK INSERT (the fastest).  The advantage to doing this rather than using any using any string splitter in T-SQL is that it's NASTY FAST and allows you to auto-magically take advantage error handling, etc, etc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 19, 2019 at 7:18 am

    #2024889

    Quick question, are there any multi-line entries or can one use the new line bytes as a row delimiter?
    😎

    • Viewing 15 posts - 931 through 945 (of 990 total)

    You must be logged in to reply to this topic. Login to reply