• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

destination does not allow NULL on column pair

When trying to import an excel spreadsheet to a table in my database, I get the error "destination does not allow NULL on column pair"

Excel spreasheet only has one column with type text.  Example data:

201-0122353-001
201-1470238-002
201-1479867-001
4556470125041686
D/14852103- 9
7/5297203- 0
4.04971E+15
12402315974
7/6118408- 2
D/39126718- 4
4.55647E+15
D/36843207- 7
7/2727288- 8
4.55647E+15
1.61513E+11
D/11436670- 7
4.55647E+15
4.55647E+15
6.35634E+15
5.4343E+15
4916051207000306


Destination table has one field, type varchar(50)

why is this happening?
0
dba123
Asked:
dba123
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
because of rows like this:
>D/14852103- 9
>D/11436670- 7

what happens is that when reading excel documents, the data type is guessed.
unfortunately, when guessing a numerical data type and then finding a non-numerical value, it sets that value to null.

solution:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21635732.html
0
 
dba123Author Commented:
I just changed the field to accept nulls and it works now.  But why the hell was it having this problem, there are no null cells in that column in the spreasheet, I'm 100% sure on that.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now