Solved

NULLS allowed, but still not accepting no value??

Posted on 2012-03-19
10
381 Views
Last Modified: 2012-06-27
I am trying to import some data from a text file into SQL Server 2008R2, but get an error message stating that it cannot do a type conversion. The error arises in columns where there are no values, even though that column is setup to accept NULL values.

The import works if I have value in all cells.
0
Comment
Question by:mitdaniels
10 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 125 total points
ID: 37736563
How do you import? Pretty crusial to get focused help.

For example with bulk import you can have a format file that gives the import the knowledge of what it can expect on that place.
http://msdn.microsoft.com/en-us/library/ms178129.aspx
0
 
LVL 16

Expert Comment

by:brad2575
ID: 37737375
If you have a blank field, check to verify it is not a " " (space) because if you are trying to import this to an int or a date field it will not like that "can not do a type conversion".
0
 

Author Comment

by:mitdaniels
ID: 37738378
Thanks for your comments.

I retried doing a "Find and Replace" of NULL text with no value or space, but still get this error message when doing the import:

"Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "Column 3" (18) to column "Column 3" (109).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37738941
Were making progress, now if we could just have the source and destination data types as well as some sample data, we may actually be able to give you an answer that is better than a guess.
0
 

Author Comment

by:mitdaniels
ID: 37739346
I am guessing that it has something to do with how a .txt files data is handled by SQL Server, since I could import the same data using an Excel file.

I tried tab and comma separated versions.

I have attached the file I am trying to import. The only difference is with the first row, which I've added so you can see the data type for each column.
DataTest.txt
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37740259
Once I corrected your file by adding the missing CrLf on the last line, it imported fine by accepting all the defaults.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37740266
This is what the destination table looks like:
CREATE TABLE [dbo].[DataTest](
	[ID1] [varchar](50) NULL,
	[ID2] [varchar](50) NULL,
	[ID3] [varchar](50) NULL,
	[ID4] [varchar](50) NULL,
	[ID5] [varchar](50) NULL,
	[ID6] [varchar](50) NULL,
	[ID7] [varchar](50) NULL,
	[Bit1] [varchar](50) NULL,
	[Bit2] [varchar](50) NULL,
	[Bit3] [varchar](50) NULL,
	[ID8] [varchar](50) NULL,
	[ID9] [varchar](50) NULL,
	[ID10] [varchar](50) NULL
)

Open in new window

And here are the contents of the table:
ID1	ID2	ID3	ID4	ID5	ID6	ID7	Bit1	Bit2	Bit3	ID8	ID9	ID10
1	9	18	1	2	5	15		TRUE		1		TRUE
1	17			3								
1	20											
2	15	18	8	6	17	25		TRUE		1		TRUE
2	14		140									
3	21	11	100	12	25	50		TRUE		11		TRUE
3	22			4								
3	23											
3	24											
4	8	9	12	4	3	5			TRUE	2		
4	10	22	114									
5	12	12	12	5	4	7	TRUE			3		TRUE
5	6	6	8	8								
5		5	140									
5			52									
5			10									
6	5	15	140	11	6	30	TRUE			4		TRUE
6	14	18	8									
6		11	100									
6			114									
6			52									
6			10									
6			138									
7	5	15	140	11	5	25	TRUE			4		TRUE
7	14	18	8									
7	3	21										
8	15	13	8	1	5	10			TRUE	5		TRUE
8	16	17		7								
9	2	3	140	11	24	50	TRUE			6		TRUE
9	19	7	8									
10	7	12	8	11	10	30		TRUE	TRUE	6		TRUE
10	20	17	12									
11	1	15	8	11	25	30			TRUE	1		TRUE
11		19	12									
12	4	10	12	11	38	50				8		TRUE
12	13	23	140									
13	2	1	52	10	8	16		TRUE		9		TRUE
13	5	13	12									
14	5	16	8	9	4	12			TRUE	10		TRUE
14	11	24	114									

Open in new window

0
 

Author Comment

by:mitdaniels
ID: 37740399
Thanks acperkins, I do however need the data types to be ints not varchar.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 37740513
That is just it, they can't be.  You need to quit wasting time and import this into a staging table using varchar data type and then converting it to your final production table like this:
INSERT YourProductionTable (ID1, ID2, ID3, ID4, ID5, ID6, ID7, Bit1, Bit2, Bit3, ID8, ID9, ID10)
SELECT	NULLIF(ID1, ''), 
		NULLIF(ID2, ''), 
		NULLIF(ID3, ''),
		NULLIF(ID4, ''), 
		NULLIF(ID5, ''),
		NULLIF(ID6, ''), 
		NULLIF(ID7, ''), 
		NULLIF(Bit1, ''), 
		NULLIF(Bit2, ''), 
		NULLIF(Bit3, ''), 
		NULLIF(ID8, ''), 
		NULLIF(ID9, ''), 
		NULLIF(ID10, '')
FROM YourStagingTable

Open in new window


Alternatively, hire a consultant to create an SSIS package that will use a Derived Column to do the conversion on the fly.  Of course, this assumes you are using something other than the Express Edition.
0
 

Author Closing Comment

by:mitdaniels
ID: 37751069
Despite not solving my problem directly, this advice is sound, thanks.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now