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 |
|
nevzab
Starting Member
34 Posts |
 Posted - 2013-07-17 : 18:06:41
|
| Hi,I am using the import wizard in SQL Server 2008 R to import data from an Excel spreadsheet into a table I have created.The spreadsheet contains 3 columns that SQL recognises as DOUBLE and they contain a 1 or 0. What data type do the corresponding fields in SQL table need to be? I have tried BIT, INT and FLOAT but keep getting an error (can't view details of the error because I get chucked out every time the error pops up). I know the problem is with the DOUBLE data because when I 'ignore' those columns the import works fine.Any help would be greatly appreciated. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-17 : 18:48:26
|
It may be that there are characters in the spreadsheet that cannot be converted to any of those. As a test, set the SQL data type to VARCHAR(32) (or something similar) and import into a table that also has VARCHAR(32) columns.Once you have imported successfully into that table, take a look at the data: SELECT * FROM YourTestTable WHERE ISNUMERIC(YourCharacterColum) = 0 |
 |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-07-17 : 19:12:30
|
| Sorry to mislead you James...it wasn't what I thought!! I switched the 'truncation error' to 'ignore' and it imported successfully. I then 'ignored' the 'Name' column which a SQL data type of varchar(max) and it imported successfully.Now I am curious at to what the problem was!! I did =len(namecolumn) in the Excel spreadsheet and the length was never greater than 100.Any ideas? |
|
|
|
|
|
|
Excel to SQL