Need help to extract unstructured excel data to sql server

Question

Post reply

Need help to extract unstructured excel data to sql server

saptek9

Ten Centuries

Points: 1155
More actions
August 2, 2018 at 7:22 am

#355369

I have 12 different Excel files which are having multiple tabs in every file and data in really unstructured. What's the best way to extract data to sql server? Please suggest any ssis add on tool or libraries or any other third party tools which can do easier way?

Viewing 13 posts - 1 through 12 (of 12 total)

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

saptek9 Ten Centuries Points: 1155 More actions · Answer 1

can i Master Data Services to import unstructured data to create as tables?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Unstructured data does not really belong in SQL Server. What are you intending to use the data for once it's inside the database?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

saptek9 - Thursday, August 2, 2018 7:22 AM
I have 12 different Excel files which are having multiple tabs in every file and data in really unstructured. What's the best way to extract data to sql server? Please suggest any ssis add on tool or libraries or any other third party tools which can do easier way?

Can you post an example of what you mean by "unstructured"?

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

David Jackson SSCertifiable Points: 6414 More actions · Answer 4

Jeff,

I was searching just yesterday for your excellent article on importing excel spreadsheets I failed to find it. 🙁

Can you post a link to it?

TIA

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

David Jackson - Saturday, August 4, 2018 9:46 AM
Jeff,
I was searching just yesterday for your excellent article on importing excel spreadsheets I failed to find it. 🙁
Can you post a link to it?
TIA
Dave J

Heh... sure... if I can find it. And thanks for the honorable mention!

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

Jeff Moden - Saturday, August 4, 2018 9:57 AM
David Jackson - Saturday, August 4, 2018 9:46 AM
Jeff,
I was searching just yesterday for your excellent article on importing excel spreadsheets I failed to find it. 🙁
Can you post a link to it?
TIA
Dave J
Heh... sure... if I can find it. And thanks for the honorable mention!

It is here 😉
😎

Still working on my version, work just keep getting in the way 😀

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

saptek9 - Thursday, August 2, 2018 7:22 AM
I have 12 different Excel files which are having multiple tabs in every file and data in really unstructured. What's the best way to extract data to sql server? Please suggest any ssis add on tool or libraries or any other third party tools which can do easier way?

Quick question, do you need to use SSIS or can you use alternatives such as directly from SQL Server?
😎

David Jackson SSCertifiable Points: 6414 More actions · Answer 8

Eirikur Eiriksson - Saturday, August 4, 2018 10:01 AM
Jeff Moden - Saturday, August 4, 2018 9:57 AM
David Jackson - Saturday, August 4, 2018 9:46 AM
Jeff,
I was searching just yesterday for your excellent article on importing excel spreadsheets I failed to find it. 🙁
Can you post a link to it?
TIA
Dave J
Heh... sure... if I can find it. And thanks for the honorable mention!
It is here 😉
😎
Still working on my version, work just keep getting in the way 😀

Fantastic. Thanks very much Eirikur 😉
😀

http://glossopian.co.uk/
"I don't know what I don't know."

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Eirikur Eiriksson - Saturday, August 4, 2018 10:01 AM
Jeff Moden - Saturday, August 4, 2018 9:57 AM
David Jackson - Saturday, August 4, 2018 9:46 AM
Jeff,
I was searching just yesterday for your excellent article on importing excel spreadsheets I failed to find it. 🙁
Can you post a link to it?
TIA
Dave J
Heh... sure... if I can find it. And thanks for the honorable mention!
It is here 😉
😎
Still working on my version, work just keep getting in the way 😀

Yep,.. you beat me to it. That's the one I was thinking about and just found.

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

saptek9 Ten Centuries Points: 1155 More actions · Answer 10

Can i use Microsoft power query or third party tools like Alteryx to extract the unstructured data from excel to sql server? Please advise the best tool i can use?

sgmunson SSC Guru Points: 110640 More actions · Answer 11

saptek9 - Tuesday, August 7, 2018 12:04 PM
Can i use Microsoft power query or third party tools like Alteryx to extract the unstructured data from excel to sql server? Please advise the best tool i can use?

Without any idea of what your data looks like, how could we possibly advise you any better than you can ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

saptek9 - Tuesday, August 7, 2018 12:04 PM
Can i use Microsoft power query or third party tools like Alteryx to extract the unstructured data from excel to sql server? Please advise the best tool i can use?

You need to define what you're talking about when you say "unstructured data" here because it has a ton of definitions. If you're talking about finding the first row header and automatically finding the first data row and then figuring out how to unpivot columns into a normalized database structure, then the link that Eirikur posted will be a good start for you. The question now is, did you even take the time to look at the presentation?

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