DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB
  • What Is SQL Injection and How Can It Be Avoided?
  • Snowflake Data Time Travel
  • SQL Server From Zero To Advanced Level: Leveraging nProbe Data

Trending

  • It’s Not About Control — It’s About Collaboration Between Architecture and Security
  • How Large Tech Companies Architect Resilient Systems for Millions of Users
  • AI’s Role in Everyday Development
  • Performing and Managing Incremental Backups Using pg_basebackup in PostgreSQL 17
  1. DZone
  2. Data Engineering
  3. Databases
  4. Import a Data Hierarchy From Excel Into SQL Server

Import a Data Hierarchy From Excel Into SQL Server

When a client hands you data for a project, it's usually in a database of some kind. Today, learn how to work some Excel magic to import a hierarchy into a database.

By 
Jonathan Danylko user avatar
Jonathan Danylko
·
Oct. 11, 16 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
8.1K Views

Join the DZone community and get the full member experience.

Join For Free
excel spreadsheet of alcohol

lately, i've been taking on a lot of work with a couple of clients. after talking with one client, he mentioned how he was working with his data and provided me an excel spreadsheet. wait a minute?!?! did they just say "excel spreadsheet" for managing his data?

while i can understand excel is what most people have at the time, it's definitely not a database. i know excel has the option to save as csv, but we have a problem with this particular issue. the data looks like this ( note : i'm using mocked up data to protect the project).

initial view of the 'database' i received

as you can see, this really isn't a great csv format to work with when exported. this usually requires some massaging of data before the data can be usable. do everything you can inside of excel to make it a little more usable. why?

because i'm sure in the future, they'll give you an updated excel file and expect you to import it just as quick. to give you an idea of what our final table looks like, it'll look similar to our menu system from earlier this year.

a couple of issues include:

  • i need a parent id for the children so i can identify how deep this hierarchy is.
  • a level indicator would be a great indicator for determining parent-children relationships.
  • we need to flatten the data so we can work with it.
  • finally, the data does need to be in a csv to import into sql server.

so, let's flatten this hierarchy so it's usable!

flatten the data

first, i want to copy over the unique id in a1 to keep the data clean. so it's a simple "=a1" formula in column f.

copy the cell and paste it all the way down to "germany."

your spreadsheet will look like this.

screenshot of the unique ids i copied into a clean column

this next part took a little detective work to find this excel gem.

in this next step, we need to remove all of the columns that have space and place the text in column g.

the index/match duo works absolute wonders for this type of processing.

in g1, type:

=index(b1:d1,match(true,index((b1:d1<>0),0),0))

let's break this down.

the index((b1:d1<>0),0) checks to see if there is anything in cells b1, c1, or d1 and returns the number.

the match looks for the text at that location returned from the index.

finally, the outer index returns the actual value based on the match returning magic kingdom to the cell.

copy that formula and all of your hierarchy will be flattened!

screenshot of flattened names of attractions

sledgehammer it!

to get the level of the hierarchy, i used a simple numbering system for it.

=if(isblank(b1),if(isblank(c1),if(isblank(d1),3,2),1),0)

copy and paste this formula into column h.

we now have our hierarchy levels.

the hard part

determining the parent id was something i spent a loooong time trying to figure out.

how do you get the parent value of a cell when you are sitting at the child level?

the only way i solved this was to use the spaces in the hierarchy to my advantage.

here's the $10,000 line of code to perform the lookup.

=if(h1=0,0,if(h1=1,index($a$1:$a$21,match("*",$b$1:b1,-1)),if(h1=2,index($a$1:$a$21,match("*",$c$1:c1,-1)))))

ok, yes, it's a bit intimidating, but once you break it down, it makes sense.

based on the current row i'm on, i check the level number.

  • if i'm at level 0, ignore it. it's zero, or null or whatever we want to use for our root number.
  • if i'm at level 1, i know that the parent is located in column b in a non-empty cell from my current location (current row/column b) going up the chain until i hit a non-empty cell (magic kingdom).
  • if i'm at level 2, execute the same process, but instead, check column c from the current row on up to a non-empty cell.

the index/match combination provided me with the id once the match found the non-empty cell using the index($a$1:$a$21... range. once it found the match, it placed the unique id into column i.

copy and paste that "down to germany" and your spreadsheet should now look like this.

screenshot of parent ids to finish the csv layout

for more information on the index/match combo, check out ablebits. they have a great write-up on index/match with examples on how to maximize your lookups... without vlookup!

conclusion

you can now take those columns from column f-i (minus the column h/levels) and save the file as a csv file to import into sql server.

you also have a simple way to copy and paste the formulas to flatten your structure for your database of choice.

have you ever dug this deep into excel? did this solve a problem for you? post your comments below and let's discuss.

Data (computing) sql Database

Published at DZone with permission of Jonathan Danylko, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB
  • What Is SQL Injection and How Can It Be Avoided?
  • Snowflake Data Time Travel
  • SQL Server From Zero To Advanced Level: Leveraging nProbe Data

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!