Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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