Concatenating Text Field

• alex_pixley

  SSC-Addicted

  Points: 436

  More actions

  September 22, 2011 at 10:43 am

  #243620

  I have to import a bunch of spreadsheets (example below) that all have the same format into a SQL 2005 table. Some records have only one SEQ_NUM while others may have as many as twelve; it might be possible to end up with more than that.

  Record SeqNum Text RanData1 RanData2 RanData3

  10548 1 This is a 16548 LR37A 19534.54

  10548 2 screwy way NULL NULL NULL

  10548 3 to mainta NULL NULL NULL

  10548 4 in a recor NULL NULL NULL

  10548 5 d. NULL NULL NULL

  I need the final table to look like this:

  Record NewText RanData1 RanData2 RanData3

  10548 This is a screwy way to maintain a record. 16548 LR37A 19534.54

  I've figured out how to do this manually with a temp table and a cursor, but now I want to automate the process in SSIS and the function won't work the way I'm trying to use it. Anyone out there have an idea how to accomplish this in a single Data Flow Task without cursors or temp tables?

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  September 22, 2011 at 10:54 am

  #1385324

  You'll be able to do it with an asynchronous Script Component - will take a bit of coding but if you're familiar with VB or C# it's nothing too onerous.

  ---

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  September 22, 2011 at 11:01 am

  #1385326

  Another possible approach would be to do this in two steps. First, import all your records into the table exactly as you have them in the files - so you'll have the messed up text / etc.

  Then, once they are all there, you can run a stored proc against that table and output the records into a new table, in the right way.

  The best way to do that would be using the "Quirky" Update approach that Jeff Moden wrote about - see the article here:

  "Quirky" update

  An example, with your code setup, can be seen below:

  CREATE TABLE #Temp

  (

  Record INT,

  SeqNum INT,

  [Text] VARCHAR(20),

  [FullText] VARCHAR(MAX) DEFAULT '',

  RanData1 VARCHAR(20),

  RanData2 VARCHAR(20),

  RanData3 VARCHAR(20)

  )

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10548, 1, 'This is a ', '16548', 'LR37A', '19534.54')

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10548, 2, 'screwy way', NULL, NULL, NULL)

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10548, 3, ' to mainta', NULL, NULL, NULL)

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10548, 4, 'in a recor', NULL, NULL, NULL)

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10548, 5, 'd.', NULL, NULL, NULL)

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10549, 1, 'A New Test ', '11248', 'sdafas', 's23525')

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10549, 2, 'Line As AN', NULL, NULL, NULL)

  INSERT INTO #Temp (Record, SeqNum, [Text], RanData1, RanData2, RanData3)

  VALUES(10549, 3, ' Example.', NULL, NULL, NULL)

  DECLARE @RunningTotalString VARCHAR(MAX)

  SET @RunningTotalString = ''

  DECLARE @Record INT

  SET @Record = 0

  UPDATE #Temp

  SET @RunningTotalString = [FullText] = (CASE WHEN Record = @Record THEN @RunningTotalString + [Text] ELSE [Text] END),

  @Record = Record

  FROM #Temp WITH (TABLOCKX)

  OPTION (MAXDOP 1)

  SELECT Record, MAX([FullText]), MAX(RanData1), MAX(RanData2), MAX(RanData3) FROM #Temp

  GROUP BY Record

  DROP TABLE #Temp

  So, all in all, to do this in SSIS, you'd have a FOREACH loop that would loop through all your spreadsheets, and for each spreadsheet, write the data into a table.

  Then, you'd have an Execute SQL Command, in which you'd run a stored proc with code similar to the above, and at the last step, you would do an INSERT INTO into your final table.