SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Excel to SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 07/17/2013 :  18:06:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 07/17/2013 :  18:48:26  Show Profile  Reply with Quote
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
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 07/17/2013 :  19:12:30  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000