Split CSV file - this is different

• rockmoose

  SSCarpal Tunnel

  Points: 4601

  More actions

  January 3, 2005 at 4:38 pm

  #535681

  Yup, nice and slick.

  And bcp is a great tool.

  
  You must unlearn what You have learnt

• mike-702990

  Grasshopper

  Points: 11

  More actions

  June 13, 2008 at 1:20 pm

  #828761

  I used this procedure fGetToken today and it works great! 🙂

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  June 13, 2008 at 10:41 pm

  #828871

  Like RockMoose did... no cursor... no loop... no RBAR from UDF usage. Slightly different take though... for ,, I return a Null... for , , I return a blank.

  Borrowing heavily on RockMoose's set up code...

  DROP TABLE #SlowThinker

  GO

  CREATE TABLE #SlowThinker

  (

  id int PRIMARY KEY,

  ExData varchar(100)

  )

  SET NOCOUNT ON

  INSERT #SlowThinker (id, ExData) VALUES (1,'Ed,Tom,1/1/2000,,,, ,,,,,,')

  INSERT #SlowThinker (id, ExData) VALUES (2,'Tom,Jones,,dog,,,,,,,,,')

  INSERT #SlowThinker (id, ExData) VALUES (3,'Paul,,,,,,,,,,,red,')

  INSERT #SlowThinker (id, ExData) VALUES (4,'Steve,,,,,,,,,,,,')

  INSERT #SlowThinker (id, ExData) VALUES (5,'1,Ed,Tom,1/1/2000,,,,,,,,,, ')

  INSERT #SlowThinker (id, ExData) VALUES (6,'1,Tom,Jones,,dog,,,,,,,,,')

  INSERT #SlowThinker (id, ExData) VALUES (7,'1,Paul,,, ,,,,,,, ,red,')

  INSERT #SlowThinker (id, ExData) VALUES (8,'1,Steve,,,,,,,,,,,,')

  INSERT #SlowThinker (id, ExData) VALUES (9,'2,big,A,,,')

  INSERT #SlowThinker (id, ExData) VALUES (10,'2,small,A,,,')

  INSERT #SlowThinker (id, ExData) VALUES (11,'2,,,,,')

  --===== Declare a variable to hold the delimiter

  DECLARE @D CHAR(1)

  SET @D = ','

  --===== Do the split with a count

  SELECT ID,

  MAX(CASE WHEN ColNum = 1 THEN Val END) AS N1,

  MAX(CASE WHEN ColNum = 2 THEN Val END) AS N2,

  MAX(CASE WHEN ColNum = 3 THEN Val END) AS N3,

  MAX(CASE WHEN ColNum = 4 THEN Val END) AS N4,

  MAX(CASE WHEN ColNum = 5 THEN Val END) AS N5,

  MAX(CASE WHEN ColNum = 6 THEN Val END) AS N6,

  MAX(CASE WHEN ColNum = 7 THEN Val END) AS N7,

  MAX(CASE WHEN ColNum = 8 THEN Val END) AS N8,

  MAX(CASE WHEN ColNum = 9 THEN Val END) AS N9,

  MAX(CASE WHEN ColNum = 10 THEN Val END) AS N10,

  MAX(CASE WHEN ColNum = 11 THEN Val END) AS N11,

  MAX(CASE WHEN ColNum = 12 THEN Val END) AS N12,

  MAX(CASE WHEN ColNum = 13 THEN Val END) AS N13,

  MAX(CASE WHEN ColNum = 14 THEN Val END) AS N14

  FROM (

  SELECT ID,

  ColNum = t.N-LEN(REPLACE(LEFT(@D+h.ExData+@D,t.N), @D, '')),

  Val = CASE

  WHEN CHARINDEX(@D, h.ExData+@D, t.N)-T.N > 0

  THEN SUBSTRING(@D+h.ExData, t.N+1, CHARINDEX(@D, h.ExData+@D, t.N)-t.N)

  ELSE NULL

  END

  FROM dbo.Tally t

  RIGHT OUTER JOIN --Necessary in case ExData is NULL

  #SlowThinker h

  ON SUBSTRING(@D+h.ExData, t.N, 1) = @D

  AND t.N <= LEN(@D+h.ExData)

  )d

  GROUP BY ID

  If you don't have a Tally table, yet, or don't know how it works to replace certain types of loops, please see the following...

  http://www.sqlservercentral.com/articles/TSQL/62867/

  --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)