Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

NULLS allowed, but still not accepting no value??

Posted on 2012-03-19
10
Medium Priority
?
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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