Solved

loading related tables from a .xls

Posted on 2011-02-17
2
926 Views
Last Modified: 2012-05-11
How would you load related tables from one excel spreadsheet..

Basically this from one .xls


Market                                   phone
Atlanta                                 555-435-6789
Atlanta                                 555-789-3456
Orlando                                444-678-5677
Orlando                                456-678-5432
Miami                                   333-678-9876


Needs to go into two tables obviously linked

Market Table

1      Atlanta
2      Orlando
3      Miami

Phone table
1      555-435-6789
1      555-789-3456
2      444-678-5677
2             456-678-5432
3      333-678-9876


Trying to use two data flows and a lookup I can’t figure out how to load the cities only once coming from a spreadsheet.. If I could do that, I could just use a lookup to load the marketed in the phone table..

Any ideas?
0
Comment
Question by:Gray5452
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 34918553
How to use Excel with SQL Server linked servers and distributed queries
http://support.microsoft.com/kb/306397


--Use a Linked Server
--To simplify queries, you can configure an Excel workbook as a linked server in SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
--Q306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries
--The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
--You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')


--Use Distributed Queries
--If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function.
--The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
0
 

Expert Comment

by:SrinivasRavi
ID: 34931655
Hi Lcohan,
When i am trying to execute the distributed queries i am getting the following error.

"Msg 7403, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered."

Please let me know how to fix it.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Error Log - logging period 1 33
Caste datetime 2 52
Benefits of SMB Fileshare 3 63
Generate Weekly Schedule 15 16
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now