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 |
|
detlion1643
Yak Posting Veteran
67 Posts |
 Posted - 2010-06-09 : 14:55:41
|
I'm using the following code in excel vba to input an entire worksheet into sql. It's working and not erroring, however it changes the formats of 2 columns.Insert into parts Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _"'Excel 8.0;Database=\\pathhere\" & name & "1.xls;HDR=YES'," & _"'SELECT [col1],[col2],[col3],[col4],[col5] FROM [Sheet1$]')The format of col1/col2 are numbers (but kept in general format). The datatype in sql that these 2 columns are going into are nchar(255), will be changing to varchar after this problem is solved.if col1 contains 60881614 and col2 contains 10895850, after the query is run, the data inside sql looks like this: col1: 6.08816e+007 and col2: 1.08959e+007. |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2010-06-09 : 14:59:17
|
quote:
Originally posted by detlion1643
I'm using the following code in excel vba to input an entire worksheet into sql. It's working and not erroring, however it changes the formats of 2 columns.Insert into parts Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _"'Excel 8.0;Database=\\pathhere\" & name & "1.xls;HDR=YES'," & _"'SELECT [col1],[col2],[col3],[col4],[col5] FROM [Sheet1$]')The format of col1/col2 are numbers (but kept in general format). The datatype in sql that these 2 columns are going into are nchar(255), will be changing to varchar after this problem is solved.if col1 contains 60881614 and col2 contains 10895850, after the query is run, the data inside sql looks like this: col1: 6.08816e+007 and col2: 1.08959e+007.
Did you try changing the nchar(255) to bigint in the destination table? maybe that will work... |
 |
|
|
detlion1643
Yak Posting Veteran
67 Posts |
Posted - 2010-06-09 : 15:07:54
|
| That does work, but I should have been more clear in the beginning. A lot of my other code relies heavily on matching col2, so changing from char to int (while working) will cause a lot more problems down the road.I just tried changing to varchar(255) and that didn't make a difference. |
|
|
|
|
|
|
|
|
openrowset changes data format