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
151 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
ID: 39617891
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
ID: 39618267
2012 was not a "53-week year". The 30th and 31st are in week 1 of 2013. Ref here
0
 

Author Comment

by:ruavol2
ID: 39630249
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 50 total points
ID: 39630275
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
ID: 39656752
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
ID: 39657259
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

24 Experts available now in Live!

Get 1:1 Help Now