?
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
Medium Priority
?
188 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
6 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 1600 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 200 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 200 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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