Solved

Can someone show me or tell me how to convert this to a simple TABLE with this spreadsheet. Please advise.

Posted on 2013-11-01
7
149 Views
Last Modified: 2016-02-11
The attached date ranges sheet is data that needs to be created as a table I would assume. The data needs to be pulled into a report for user purposes. Does anyone know the best way to do this.
DateRangesTable.xlsx
0
Comment
Question by:ruavol2
7 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 400 total points
Comment Utility
You can use SQL SSMS Import Wizzard or a query like:


--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 XLImport2 FROM OPENQUERY(EXCELLINK,'SELECT * FROM [Customers$]')

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$]')




ALL IN ONE!!!

How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686

How to use Excel with SQL Server linked servers and distributed queries
http://support.microsoft.com/kb/306397

openrowset excel 2007
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/23e7b80d-c7ec-4875-bd3a-aa137fd95dfb
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 50 total points
Comment Utility
2012 was not a "53-week year". The 30th and 31st are in week 1 of 2013. Ref here
0
 

Author Comment

by:ruavol2
Comment Utility
How could this be maintained easily through SQL Server 2008. I was wondering if SQL Server does not have a Form fill interface. Never understood why they did not create that early on in the process in its developement like Access does. Then I found out that Access could interface with SQL Server which is cool. Is that about the only way to do that or is there another way to get the table built and the interface created.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 50 total points
Comment Utility
Building the table is easy.  You can write a SQL script to do it or build it manually.

The difficult part is to get the table populated with data.

I believe SSIS could populate the table from a spreadsheet or you could write an application to allow the use r to populate or update the table.

I assume the user has specified what the weeks are for their own purposes. They want each year to have 53 weeks so they can just use those weeks to get reports for a given year.

An application could be written to populate the data based on a good set of user rules.

mlmcc
0
 

Author Closing Comment

by:ruavol2
Comment Utility
Now I am trying to find out if I have SSIS. I am not sure it was installed on my client machine. Do you know what it was called.
0
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
You're running SQL Server 2008? Look for Business Intelligence  Development System (BIDS) under the menu entry "tree" for SQL Server.
hth

Mike
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
What's the difference between these two "qualifiers?" 3 29
SQL Server memory Issue 7 73
Mssql SQL query 14 24
Azure SQL DB? 3 11
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

13 Experts available now in Live!

Get 1:1 Help Now