Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-12-24 : 01:35:49
|
Hi All,I am uploading the Excel file from Excel 2007 to SQL Server 2005 using OPENROWSET.First "row" of Excel file has 38 columns of which 9 clomuns have Projectdetails and remaning as Date in incremental of 7 days.eg:- 1st row (which would be columns after importing) is like client | projectcode |.. 12/01/2012 | 12/08/2012 | 12/15/2012 |... 16/15/2013The dates in excel are incremented like cell1+7,cell2+7.. So after importing i am getting 9 columns are tablename properly but the dates are imported as F1 | F2 | F3 ....F38 instead of actual dates in SQL Server table.How can I get the dates in table after importingAny suggestion would be helpful. |
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-12-24 : 08:45:14
|
I did it by saving the excel to CSV first ,so now the formulaes in excel are actual dates used in CSV.it worked :)and then used following for importing the CSV.select * into Tablename from openrowset ('Microsoft.JET.OLEDB.4.0','Text;Database=H:\;','select * from [csvfilename.csv]') |
|
|
Vishal_sql
Posting Yak Master
102 Posts |
Posted - 2012-12-28 : 00:59:33
|
quote: I am uploading the Excel file from Excel 2007 to SQL Server 2005 using OPENROWSET.First "row" of Excel file has 38 columns of which 9 clomuns have Projectdetails and remaning as Date in incremental of 7 days.eg:- 1st row (which would be columns after importing) is like client | projectcode |.. 12/01/2012 | 12/08/2012 | 12/15/2012 |... 16/15/2013The dates in excel are incremented like cell1+7,cell2+7.. So after importing i am getting 9 columns are tablename properly but the dates are imported as F1 | F2 | F3 ....F38 instead of actual dates in SQL Server table.How can I get the dates in table after importingAny suggestion would be helpful.
Can any one having other approach As my Excel is having formulaes i cant read it using openrowset,so i converted it in CSV manually first and read the CSV. Can I do it instead of converting it in CSV ? |
|
|
|
|
|