SQL Import table need to split up data then send to another table

• tom.wallis

  SSC Enthusiast

  Points: 121

  More actions

  June 12, 2018 at 3:40 am

  #363074

  Hi,

  New to the site and thought this would be the best place to find out how i can achieve this.

  Im looking to take data from a table which has only one column and send the data to another after it has been split up.

  What do i mean after it has been split?
  Well the data looks like this:

  5342|SMKHAN|First Last|COMM|Technology & Commercial Division|CORPFIN|Corporate Finance Division|2018-03-05 09:27:34.433000000

  Id like to separate this out so that i can send each section to a different column in another table.

  is this possible as all ive seen is a function called split cell which isnt available in 2012?

  Cheers

• DesNorton

  SSC-Insane

  Points: 24284

  More actions

  June 12, 2018 at 5:42 am

  #1993663

  tom.wallis - Tuesday, June 12, 2018 3:40 AM

  Hi,

  New to the site and thought this would be the best place to find out how i can achieve this.

  Im looking to take data from a table which has only one column and send the data to another after it has been split up.

  What do i mean after it has been split?
  Well the data looks like this:

  5342|SMKHAN|First Last|COMM|Technology & Commercial Division|CORPFIN|Corporate Finance Division|2018-03-05 09:27:34.433000000

  Id like to separate this out so that i can send each section to a different column in another table.

  is this possible as all ive seen is a function called split cell which isnt available in 2012?

  Cheers

  This would be 1 way of doing it ...
  INSERT INTO [YourSchema].[YourOtherTable] ( SomeID, SomeCode, SomeName, SomeDept1, SomeDivision1, SomeDept2, SomeDivision2, SomeDate )
SELECT
  SomeID = MAX(CASE WHEN split.ItemNumber = 1 THEN split.Item ELSE NULL END)
, SomeCode = MAX(CASE WHEN split.ItemNumber = 2 THEN split.Item ELSE NULL END)
, SomeName = MAX(CASE WHEN split.ItemNumber = 3 THEN split.Item ELSE NULL END)
, SomeDept1 = MAX(CASE WHEN split.ItemNumber = 4 THEN split.Item ELSE NULL END)
, SomeDivision1 = MAX(CASE WHEN split.ItemNumber = 5 THEN split.Item ELSE NULL END)
, SomeDept2 = MAX(CASE WHEN split.ItemNumber = 6 THEN split.Item ELSE NULL END)
, SomeDivision2 = MAX(CASE WHEN split.ItemNumber = 7 THEN split.Item ELSE NULL END)
, SomeDate = CAST(MAX(CASE WHEN split.ItemNumber = 8 THEN split.Item ELSE NULL END) AS datetime2(7))
FROM [YourSchema].[YourTable] AS src
CROSS APPLY dbo.DelimitedSplit8K(src.[YourSingleColumn], '|') AS split
GROUP BY src.[YourSingleColumn];


  The function DelimitedSplit8K can be found HERE

  ---

  How to post data/code on a forum to get the best help.

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  June 12, 2018 at 7:46 am

  #1993681

  Assuming that the data is "regular" in that it will always have the same "fields" to be converted to columns available, this data should never have made it into a single column table to begin with.  It should have been "split on import" by using the likes of BULK INSERT or BCP or even SSIS.

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

• tom.wallis

  SSC Enthusiast

  Points: 121

  More actions

  June 13, 2018 at 3:25 am

  #1993811

  d

• tom.wallis

  SSC Enthusiast

  Points: 121

  More actions

  June 13, 2018 at 4:09 am

  #1993813

  Jeff Moden - Tuesday, June 12, 2018 7:46 AM

  Assuming that the data is "regular" in that it will always have the same "fields" to be converted to columns available, this data should never have made it into a single column table to begin with.  It should have been "split on import" by using the likes of BULK INSERT or BCP or even SSIS.

  I mean im new to both sql and c# ( which i used to create the import )

  If i show you the c# that i have could you suggest what i can do better based on what you have said?

  Pastebin to the code

  any help will be appreciated

• tom.wallis

  SSC Enthusiast

  Points: 121

  More actions

  June 13, 2018 at 5:41 am

  #1993818

  DesNorton - Tuesday, June 12, 2018 5:42 AM

  tom.wallis - Tuesday, June 12, 2018 3:40 AM

  Hi,

  New to the site and thought this would be the best place to find out how i can achieve this.

  Im looking to take data from a table which has only one column and send the data to another after it has been split up.

  What do i mean after it has been split?
  Well the data looks like this:

  5342|SMKHAN|First Last|COMM|Technology & Commercial Division|CORPFIN|Corporate Finance Division|2018-03-05 09:27:34.433000000

  Id like to separate this out so that i can send each section to a different column in another table.

  is this possible as all ive seen is a function called split cell which isnt available in 2012?

  Cheers

  This would be 1 way of doing it ...
  INSERT INTO [YourSchema].[YourOtherTable] ( SomeID, SomeCode, SomeName, SomeDept1, SomeDivision1, SomeDept2, SomeDivision2, SomeDate )
SELECT
  SomeID = MAX(CASE WHEN split.ItemNumber = 1 THEN split.Item ELSE NULL END)
, SomeCode = MAX(CASE WHEN split.ItemNumber = 2 THEN split.Item ELSE NULL END)
, SomeName = MAX(CASE WHEN split.ItemNumber = 3 THEN split.Item ELSE NULL END)
, SomeDept1 = MAX(CASE WHEN split.ItemNumber = 4 THEN split.Item ELSE NULL END)
, SomeDivision1 = MAX(CASE WHEN split.ItemNumber = 5 THEN split.Item ELSE NULL END)
, SomeDept2 = MAX(CASE WHEN split.ItemNumber = 6 THEN split.Item ELSE NULL END)
, SomeDivision2 = MAX(CASE WHEN split.ItemNumber = 7 THEN split.Item ELSE NULL END)
, SomeDate = CAST(MAX(CASE WHEN split.ItemNumber = 8 THEN split.Item ELSE NULL END) AS datetime2(7))
FROM [YourSchema].[YourTable] AS src
CROSS APPLY dbo.DelimitedSplit8K(src.[YourSingleColumn], '|') AS split
GROUP BY src.[YourSingleColumn];


  The function DelimitedSplit8K can be found HERE

  Hi with this ive gotten a bit stuck and i dont know what to do from here
  imgur:
  https://imgur.com/a/Mdo2a6u

  just a bit confused as to what to do from here?

  Thanks

• DesNorton

  SSC-Insane

  Points: 24284

  More actions

  June 13, 2018 at 6:08 am

  #1993821

  tom.wallis - Wednesday, June 13, 2018 5:41 AM

  DesNorton - Tuesday, June 12, 2018 5:42 AM

  tom.wallis - Tuesday, June 12, 2018 3:40 AM

  Hi,

  New to the site and thought this would be the best place to find out how i can achieve this.

  Im looking to take data from a table which has only one column and send the data to another after it has been split up.

  What do i mean after it has been split?
  Well the data looks like this:

  5342|SMKHAN|First Last|COMM|Technology & Commercial Division|CORPFIN|Corporate Finance Division|2018-03-05 09:27:34.433000000

  Id like to separate this out so that i can send each section to a different column in another table.

  is this possible as all ive seen is a function called split cell which isnt available in 2012?

  Cheers

  This would be 1 way of doing it ...
  INSERT INTO [YourSchema].[YourOtherTable] ( SomeID, SomeCode, SomeName, SomeDept1, SomeDivision1, SomeDept2, SomeDivision2, SomeDate )
SELECT
  SomeID = MAX(CASE WHEN split.ItemNumber = 1 THEN split.Item ELSE NULL END)
, SomeCode = MAX(CASE WHEN split.ItemNumber = 2 THEN split.Item ELSE NULL END)
, SomeName = MAX(CASE WHEN split.ItemNumber = 3 THEN split.Item ELSE NULL END)
, SomeDept1 = MAX(CASE WHEN split.ItemNumber = 4 THEN split.Item ELSE NULL END)
, SomeDivision1 = MAX(CASE WHEN split.ItemNumber = 5 THEN split.Item ELSE NULL END)
, SomeDept2 = MAX(CASE WHEN split.ItemNumber = 6 THEN split.Item ELSE NULL END)
, SomeDivision2 = MAX(CASE WHEN split.ItemNumber = 7 THEN split.Item ELSE NULL END)
, SomeDate = CAST(MAX(CASE WHEN split.ItemNumber = 8 THEN split.Item ELSE NULL END) AS datetime2(7))
FROM [YourSchema].[YourTable] AS src
CROSS APPLY dbo.DelimitedSplit8K(src.[YourSingleColumn], '|') AS split
GROUP BY src.[YourSingleColumn];


  The function DelimitedSplit8K can be found HERE

  Hi with this ive gotten a bit stuck and i dont know what to do from here
  imgur:
  https://imgur.com/a/Mdo2a6u

  just a bit confused as to what to do from here?

  Thanks

  That is because you do not have the function DelimitedSplit8K in your DB.

  1. Click on the link that I supplied
  2. Scroll to the end of the article
  3. Download and unzip the code file
  4. Install the function in your DB.
  5. Re-Run your code.

  ---

  How to post data/code on a forum to get the best help.

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  June 13, 2018 at 7:49 am

  #1993848

  tom.wallis - Wednesday, June 13, 2018 4:09 AM

  Jeff Moden - Tuesday, June 12, 2018 7:46 AM

  Assuming that the data is "regular" in that it will always have the same "fields" to be converted to columns available, this data should never have made it into a single column table to begin with.  It should have been "split on import" by using the likes of BULK INSERT or BCP or even SSIS.

  I mean im new to both sql and c# ( which i used to create the import )

  If i show you the c# that i have could you suggest what i can do better based on what you have said?
  https://pastebin.com/cujQmDJ0

  Pastebin to the code

  any help will be appreciated

  You need to take that post down right away.  You've embedded the login and password of the "SA" user in clear text in your code for the connection string.  At least remove the servername, username, and password.

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

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  June 13, 2018 at 8:07 am

  #1993857

  tom.wallis - Wednesday, June 13, 2018 4:09 AM

  Jeff Moden - Tuesday, June 12, 2018 7:46 AM

  Assuming that the data is "regular" in that it will always have the same "fields" to be converted to columns available, this data should never have made it into a single column table to begin with.  It should have been "split on import" by using the likes of BULK INSERT or BCP or even SSIS.

  I mean im new to both sql and c# ( which i used to create the import )

  If i show you the c# that i have could you suggest what i can do better based on what you have said?
  https://pastebin.com/cujQmDJ0

  Pastebin to the code

  any help will be appreciated

  Getting back to the original problem, I quit using things like C# and VB back in 2003 and I've never actually written code for C# and so don't know how to tell you how to fix it. 

  I do know, however, that everything that you're trying to do in your C# code can all be done using T-SQL.  The trouble is, you'd need to install the ACE drivers that you're using in C# on your SQL Server and configure that.  Then you can use OPENROWSET to open the spreadsheet and actually use it as if it were a table.

  If that's an option (it does require some elevated privs but, what the hell, you were using the SA login), post back.

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

• tom.wallis

  SSC Enthusiast

  Points: 121

  More actions

  June 14, 2018 at 3:34 am

  #1993957

  Jeff Moden - Wednesday, June 13, 2018 8:07 AM

  tom.wallis - Wednesday, June 13, 2018 4:09 AM

  Jeff Moden - Tuesday, June 12, 2018 7:46 AM

  Assuming that the data is "regular" in that it will always have the same "fields" to be converted to columns available, this data should never have made it into a single column table to begin with.  It should have been "split on import" by using the likes of BULK INSERT or BCP or even SSIS.

  I mean im new to both sql and c# ( which i used to create the import )

  If i show you the c# that i have could you suggest what i can do better based on what you have said?
  https://pastebin.com/cujQmDJ0

  Pastebin to the code

  any help will be appreciated

  Getting back to the original problem, I quit using things like C# and VB back in 2003 and I've never actually written code for C# and so don't know how to tell you how to fix it. 

  I do know, however, that everything that you're trying to do in your C# code can all be done using T-SQL.  The trouble is, you'd need to install the ACE drivers that you're using in C# on your SQL Server and configure that.  Then you can use OPENROWSET to open the spreadsheet and actually use it as if it were a table.

  If that's an option (it does require some elevated privs but, what the hell, you were using the SA login), post back.

  Hi thanks for your help so far

  With regards to the username this is just a local sql server and doesnt actually matter as that password is only used for sql on my machine so ehhh.

  I used the other commenters help and i have gotten it to work.

  I would however like to learn more about SQL and TSQL and was wondering if you had any good reading material that i could look up and learn something from

  cheers