Import Excel into multiple tables

Question

Import Excel into multiple tables

steven.palmer

SSChasing Mays

Points: 646
More actions
June 16, 2008 at 12:55 pm

#121973

I'm trying to import an excel spread sheet into a relational db. How would I import values into multiple tables while keeping referential integrity? I thought of creating a view and importing the data into the view but this doesn't seem like the correct solution. Any ideas would be appreciated.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You will have to insert parent records before child records. This will typically force you into a multi-step process.

One of the easiest ways to manage this is to import the data into a staging table and then add parent records, add child records, and finally commit. You could also do all of this work in SSIS, but this can become tricky - especially if you are using IDENTITY columns for referential integrity.

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 2

And assuming that you have referential integrity switched on at the RDBMS level, SSIS will fail unless you keep the parent/child relationships intact

~PD

steven.palmer SSChasing Mays Points: 646 More actions · Answer 3

Are you suggesting that I import the spread sheet into a flat table then use an insert trigger to redistribute the data appropriatly within the trigger. Is so can you provide me with a quick example.

Thanks

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 4

That is one of many ways in which you can do this. But I think you are missing the point....

Possible suggestion

a) Stage the data (have a staging database). In other words, load the file prior to generating surrogates to here.

b) Load the parent records to your table, and join back to your staged data

c) TADA, you have the children records ready for loading

~PD

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 5

I agree with the idea of the staging area as the following can be done:

* Load data to tables & refresh these daily (overwrite if required)

* Choose the order of the loading after Excel imports to ensure referential integrity

* Run a query to determine the data that sits outside of the referential integrity - to deal with these exceptions

Another option is the SSIS route - but there would be reasonable amount of logic that would need to be built into these packages.

Do you have any more details for us - with regards to your exposure to SSIS and resources for writing SQL scripts / SPs, Views etc

Thanks

Kevin

steven.palmer SSChasing Mays Points: 646 More actions · Answer 6

I'm a total newbie. I just found out what SSIS was yesterday and my SQL writting ability's would be at a novice level. Here is my situation, I have a user who wants to import a spreadsheet that he get from the lab instead typing in the data thru the interface. So I found a program online that can import spread sheets into a flat tables and now I need redistribute the data into the correct tables while keeping referential integrity. For example I have an identification table as the main table

RefNo int Unchecked

PetroNo nvarchar(8) Checked

FieldNo nvarchar(15) Checked

SampleNo nvarchar(12) Checked

DateSubmitteddatetime Checked

DateExamineddatetime Checked

ReportTo nvarchar(30) Checked

Investigator nvarchar(30) Checked

MEPropID nvarchar(3) Checked

And a location table(FK Refno, FK PropID)

RefNoint Unchecked

PropIDint Checked

AreaZone nvarchar(50) Checked

GridName nvarchar(30) Checked

CoordCodenvarchar(3)Checked

MineLevelnvarchar(8)Checked

GridEW float Checked

GridNS float Checked

GridElevationfloat Checked

Units nvarchar(10)Checked

BHNo nvarchar(12)Checked

BHDepthofSamplefloat Checked

UTMZone nvarchar(2)Checked

UTMX float Checked

UTMY float Checked

Altitude float Checked

AltitudeUnitsnvarchar(10)Checked

NTS nvarchar(12)Checked

lstProperty Table(PK PropID)

PropID int Unchecked

PropertyName nvarchar(70)Checked

Township nvarchar(20)Checked

ProvState nvarchar(20)Checked

Country nvarchar(50)Checked

My second problem is the spread sheet contains the Property Name in the lstProerty table which needs to be converted to the propid so that it can get inserted into the location table. Any ideas would be great. Examples are welcome.

Thnxs

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 7

Welcome to the SQL community, and a hearty welcome to SSIS.

First off, there is no need for a custom program to import the excel, thats just overkill, SSIS will import for you.

Simply, define an excel connection, and a OLEDB connection (happy googling), and drag your source to destination, filling out what you need.

In your case, definitely stage your data (load to a flat structure first).

Now, from what I can see, you will need to first load tables such as your property table with missing rows.

This you can do by either using a lookup transform which works with the error rows, or a merge join.

Use the same OLEDB connection, and drag a merge join onto your palette (once more happy googling). Then load your data to a OLEDB destination.

You will need to load all parent tables like that one.

Next you will need to get all surrogates (IDs), because for your child tables this is what you will be loading.

Exactly the same as the above, you can use lookups or merge joins to do this.

My one and only recommendation is for you to actually buy a book, as well as to doing the examples that you will find with the samples.

This will at least give you a fair idea of the look and feel, and what to watch out for.

~PD

steven.palmer SSChasing Mays Points: 646 More actions · Answer 8

Thanks for the advice. I got a book called (SQL server 2005 T-SQL recipes) great book with good examples, just doesn't have what I am looking for in this instances.

Cheers,

Steve

darmstrong SSC Journeyman Points: 75 More actions · Answer 9

Sorry to bring this thread back from the dead but I'm in the exact same situation.

I realize that this is the SQL 2005 forum so please excuse the off topic nature of this query. Is there a way to import Excel data into multiple tables using a DTS package on SQL 2000? I do have SQL 2005 but the target server is running SQL 2000. Can I use SSIS to attach to an SQL 2000 server?

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 10

Yes - the SQL 2000 data source is the same as any other.

Thanks

Kevin

Mukti SSCrazy Points: 2826 More actions · Answer 11

Yes you can connect to SQL Server 2000 using SSIS. In you dataflow use OLEDB Destination or SQL Server Destination.

And for connectiing to Excel you can use the Excel Connection.

This article might help you. You need to connected to SQL Server 2000 instead of 2005 explained in the article.

http://blogs.techrepublic.com.com/datacenter/?p=205%5B/url%5D

HTH

~Mukti

darmstrong SSC Journeyman Points: 75 More actions · Answer 12

Thanks for the informative reply. Would you suggest using OLE DB or SQL Server as the data flow destination when connecting to SQL 2000?

Crispin Proctor SSCoach Points: 18161 More actions · Answer 13

Read up on them in BOL and the answer will become evident.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

darmstrong SSC Journeyman Points: 75 More actions · Answer 14

I had to setup the OLE DB provider for the SQL 2000 server. It seems that the SQL Connector is only for SQL 2005.

I have connected the OLE DB destination to a view that I created. I'm worried that importing the data into the view won't update the proper primary/foreign key pairs to retain referential integrity. What is the correct method for importing data from Excel into multiple tables in order to retain the integrity?