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 |
|
help_lucky
Starting Member
2 Posts |
 Posted - 2010-07-14 : 03:09:12
|
Hello Everyone,I need to upload excel sheet in to the database. Which i am doing with the query SELECT * INTO temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls', 'SELECT * FROM [qry_BA_Controlling (Report)$]')Here C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls is the path from where the excel file needs to be fetched.qry_BA_Controlling (Report) is the name of the worksheet.So on executing the query, a table with name 'temp' is created. With records that are populated from excel.Now here i have a date field in excel. sometimes the values of this field are not uploaded properly into the temp table. The values for this date field are set to NULL eventhough they have values in EXCEL.I have modified my query so,Insert into temp Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Dokumente und Einstellungen\l.varada\Desktop\BA-Control.xls', HDR=YES', 'SELECT * FROM [qry_BA_Controlling (Report)$]') Here temp is an existing table, i have defined the date type of the field [creation date] to varchar and uploaded the excel. Then i used **convert** to change the datatype to the correct format.. update temp set [Creation date] = CONVERT (varchar,[Creation date],101) Even now it is populating NULL values..Or this conversion needs to be done while uploading. if so, please let me know.I think the reason behind this is, the first few values of this column are spaces and after that it has values..As it determines the datatype by checking first few rows..Then it is assigning NULL..If i set the value of this field in first row to 01.01.9999 and then upload. The excel file is uploaded properly... Please suggest me an alternative. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-14 : 03:25:31
|
| I would suggest changing the date column in your table to varchar first & let all those dates get imported & then convert those fields to datetime.Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
help_lucky
Starting Member
2 Posts |
Posted - 2010-07-14 : 03:28:07
|
| As mentioned above, i have tried that way to, set the datatype of [creation date] to varchar and then uploaded excel sheet into sql server. Even then i am getting null values to this column. convert statement at this stage not helping me to populate the correct dates. |
|
|
|
|
|
|
|
|
Problem with excel sheet upload into sqlserver