Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

NULLS allowed, but still not accepting no value??

Posted on 2012-03-19
10
Medium Priority
?
397 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 375 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
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 375 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

885 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