December 18, 2008 at 1:54 pm
Hi. I have a table (dbo.ProductCosts) that contains 315000 records.
It was created from excel extract based on a table from an old flat file system.
It contains Products, Orders on which the Product has been used and a rolled up Cost for the Product.
A ProductId may appear on hundreds of Orders. I need to calculate the average build cost of a Product for the last 10 Orders on which it has appeared.
Where do I begin?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 18, 2008 at 2:43 pm
Can you supply a create statement for the table dbo.productscost, some sample data which can be fictional - that is not reveal a real product identification, or reveal any other sensitive cost data or company identification. (In other words read the article whose link is in my signature block). In addition some information as to the sequence of rows in the Excel spreadsheet, that is are they in ascending date order or in a random sequence.
With this information it will be more likely that someone will be able to assist you.
December 18, 2008 at 3:23 pm
Thanks for the reply. I will haved a crack at the code tomorrow and post back if I hit a wall (table structure etc..)..
One quick question. As this is a temporary table should I index to improve performance when querying it? Main columns ProductId, OrderId, Cost. I kind of guess I will be doing a nested query..........will be clearer once I get the books out 🙂
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 18, 2008 at 5:00 pm
Temporary meaning you will only perform this task one time?
It has been my experience that with good results you will be asked to do it again, and then again and eventually it will be a request for a weekly / monthly report.
I would suggest reading this article before you get started it has a good discussion and three code samples that may assist you.
http://www.sqlteam.com/article/joining-to-the-next-sequential-row
December 19, 2008 at 12:16 am
Thanks for the link. The table is being used for migration purposes so once the data is migrated to the new system it will no longer be required.
I worked on the code last night. The TotalCost field is nvarchar(255). I wanted to average this using AVG(TotalCost) AS AverageCost. This returned a conversion error. I then tried CONVERT (money,TotalCost). The query ran, returned values for 23000 records then returned error 'could not convert char'.
I will plough on and post some sensible data to assist if I am unable to resolve.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 19, 2008 at 8:32 am
Hi. I managed to resolve the error SQL returned on CONVERT.
I used WHERE ISNUMERIC(TotalCost) <> 1
I deleted - from end of value and then ran:
SELECT ProductId, AVG(CONVERT(money,TotalCost)) AS 'Total'
FROM dbo.Cost1
GROUP BY ProductId
ORDER BY ProductId
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 19, 2008 at 8:38 am
Philip
This site has a large number of forum postings concerning the transfer of Excel data to SQL and its attendant problems . I would respectfully request that when you solve the problem (In particular that conversion error) that you post your solution so that it would be available to the community and we can all benefit from your labor.
Thanks in advance for considering the above request
Seeing the time of our posts - you must type faster than I do.
Thanks
December 21, 2008 at 3:09 am
To follow on. I have since remigrated the data. I cleaned up in excel first (reduced to 164000 records). I found that the TotalCost column had a - symbol (negative) value on the end of some values. I added this as a prefix then converted from nvarchar(255) to money by editing the mapping on import.
I needed to split across 3 excel worksheets due to the row limitation .xls as the import wizard did not recognise .xlsx (excel 2007 file extension).
I then ran:
INSERT INTO [MyDb].[dbo].[Temp1]
([Field1]
,[Field2]
,[etc...])
SELECT
Value1,
Value2,
etc
FROM [MyDb].[dbo].[Temp2]]
If anyone knows of a way to import +65000 rows I would be interested to know?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 21, 2008 at 11:15 am
Phikkip Horan
needed to split across 3 excel worksheets due to the row limitation .xls as the import wizard did not recognise .xlsx (excel 2007 file extension
If anyone knows of a way to import +65000 rows I would be interested to know?
First of all let me clearly state that I have NOT attempted to import data from Excel 2007 nor have I ever imported a Excel spread sheet with anything close to 65,000+ rows. That being said and your replies got my curousity up and so I did a little surfing of Microsoft sites. Here is the link I think might be useful to you
http://support.microsoft.com/ph/2523
Here is an extract from the above link.
Microsoft Office 2007 files have a new format and therefore are not supported by the Jet 4.0 OLE DB provider. Specifically, the Microsoft Excel data source and data destination, which uses the underlying Excel connetion manager based on JET, cannot be used with Microsoft Excel 2007 files. Microsoft Office 2007 shipped with a new OLE DB provider, the Office 12 Microsoft Access Engine OLE DB Provider (aka ACE provider). The ACE provider must be used with Microsoft Excel 2007 and Microsoft Access 2007 data files, but ACE supports only Microsoft Office 2007 data. SSIS has not been specifically tested with other data sources that is compatible with ACE, such as Microsoft Sharepoint files, though it is possible to use them with ACE.
To be able to use the ACE provider, one has to pick the ACE OleDB provider, just like any other OleDB provider in SSIS, and not use the availble Excel & Access options.
ACE provider is supported in SQL Server 2005 SP2 and later.
Emphasis added to above quotation.
Again from above article:
Bob Beauschemin provided us with a nice Excel12 sample package that uses the new ACE OleDB provider to connect to Excel12 files.
Clicking on the link will allow you to down load the Sample package as a zip file.
Now here is the gotcha. You have a ready made Excel 2007 spread sheet with more than 65000 rows. Would you care to give it a try with SSIS and report back or even prepare a short article on the steps required to import data from Excel 2007. I am sure it would be one heck of a great help to the members of this community.
December 21, 2008 at 11:20 am
Would be happy to. I will give it a go at the earliest opportunity and post back.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 21, 2008 at 12:09 pm
December 22, 2008 at 3:50 pm
I have successfully imported 164000 rows from .xlsx file (Office 2007) into SQL 2005 database (SP2).
The full article I followed can be read by clicking the link provided below:
The key points for this exercise are summarised as follows:
In the SQL Server Import and Export Wizard, select "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as the data source or destination. Type or paste the full path and filename as the value of Data Source on the Connection" tab of the Data Link Properties dialog box (i.e. C:\MyDataSource.xlsx). Then be sure to add "Excel 12.0" as the value of Extended Properties on the All tab.
I hope this helps others.
Kind Regards,
Philip Horan.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 22, 2008 at 4:43 pm
Terrific and thanks I am sure many many users of this site will benefit from your work.
Again THANKS
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply