0

I am in urgent need of importing huge data from excel to SQL.

one of the excel sheet contains data as follows.

ID  Name    Category    Section
1   Books   Stationery  KG1,KG2,1
2   Pencils Stationery  KG1,KG2,1,2,3

Which I would like to convert into this:

ID  Name    Category    Section
1   Books   Stationery  KG1
1   Books   Stationery  KG2
1   Books   Stationery  1
2   Pencils Stationery  KG1
2   Pencils Stationery  KG2
2   Pencils Stationery  1
2   Pencils Stationery  2
2   Pencils Stationery  3

What is the easiest way to do this?

1

I would use the Power Query Add-In for this. Power Query has a Split command which can split the Section values into separate columns:

http://office.microsoft.com/en-in/excel-help/split-a-column-of-text-HA103994003.aspx

Then the Unpivot command can turn those columns into rows:

http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx

| improve this answer | |
1

I have used a cool little gadget add in called SSMSBoost - it can dump the data in Excel nicely without messing up the formatting.

http://www.ssmsboost.com

Essentially it extends SSMS so that it has the ability to dump the current result grid into an Excel file and then open that excel file. Because it can do this, it can deal with whitespace and commas etc nicely in the data, so the data in excel matches the data on your screen correctly. (better than copy paste)

| improve this answer | |
  • 1
    Yeah this is not my product, and it dos have it's annoying qualities, but it is pretty good at getting sql results into excel. I use it on a daily basis myself. – user230910 Sep 24 '14 at 9:12
  • My bad, I misread it and didn't see the word "used" in the first sentence. My bad! – Raystafarian Sep 24 '14 at 9:23
  • user230910, SSMSBoost developer is here. Please write me an e-mail about "annoying qualities" and I will try to improve them. I need such e-mails. If no one tells me what I annoying, I can not improve it ;) – Andrei Rantsevich Sep 25 '14 at 12:58
  • how does it help me in this situation??? – Random Thoughts Sep 26 '14 at 10:12
  • @AndreiRantsevich ive been all over your website and your G+ page, and cannot find the email address for feedback. – user230910 Sep 28 '14 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.