Importing from Excel - Nulls for blank varchar fields & Ref Integrity

[LucieLastic]

hi

I'm trying to import a spreadsheet. One of the columns has all text data with the odd blank cell and the appropriate field in the DB has a referential integrity constraint on it. For blank XL cells, I need a NULL to be inserted but sql server, I believe, is trying to import a blank string (ie ''), so the contraint fails. How can I force it to set it to NULL for blank cells?

I've also tried saving as CSV and importing that but I get the same problem.

MAny thanks
Lou

 

[drlex]

Lou,
How are you doing your import?
If you're using OpenRecordset, you can change the behaviour of the import - see

http://support.microsoft.com/kb/194124/en-us

Personally, the combination of user and Excel behaviour causes me to sanitize data imports by way of an all varchar temp. table. Keeps processes running and also acts as a source to advise of data rows that failed to be imported.


soi là, soi carré