未加星标

Import a Data Hierarchy from Excel into SQL Server

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

Import a Data Hierarchy from Excel into SQL Server

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


Import a Data Hierarchy from Excel into SQL Server

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 ourmenu systemfrom earlier this year.

A couple of issues include:

I need a parent ID for the children so I can identify how deep is this hierarchy. 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.


Import a Data Hierarchy from Excel into SQL Server

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!


Import a Data Hierarchy from Excel into SQL Server
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-emptycell 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.


Import a Data Hierarchy from Excel into SQL Server

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.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLExcelSQL ServerISB
分页:12
转载请注明
本文标题:Import a Data Hierarchy from Excel into SQL Server
本站链接:http://www.codesec.net/view/481550.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(40)