August 12, 2008 at 4:16 am
I am facing a strange problem which i havent experienced before. I am importing a text file using SQL Server Import Export WIzard in Mgt Studio.
The columns in file are pipe (|) delimited and rows are end by {cr}{lf}
I have attached my sample data file. The file is only test data.
I have done few of my testing and its described below:
1. If i import file using bcp command, the values in col3 and col4 remain null which is correct result. the bcp command am using is
bcp devdb.dbo.test in c:\tmp\test.txt -c -T -t "|"
2. if i import file using Import Export Wizard in Mgt studio and have the datatype of col3 and col4 as String (dt_str), the column values are empty (''). These are not null values. Hence wrong result. 🙁
What and where i am doing wrong while using Import Export Wizard in Mgt Studio? My mind is frozen today, need help....
August 12, 2008 at 5:08 pm
any help as not able to sort this matter yet.. 🙁
August 12, 2008 at 7:20 pm
helloanam (8/12/2008)
I am facing a strange problem which i havent experienced before. I am importing a text file using SQL Server Import Export WIzard in Mgt Studio.The columns in file are pipe (|) delimited and rows are end by {cr}{lf}
I have attached my sample data file. The file is only test data.
I have done few of my testing and its described below:
1. If i import file using bcp command, the values in col3 and col4 remain null which is correct result. the bcp command am using is
bcp devdb.dbo.test in c:\tmp\test.txt -c -T -t "|"2. if i import file using Import Export Wizard in Mgt studio and have the datatype of col3 and col4 as String (dt_str), the column values are empty (''). These are not null values. Hence wrong result. 🙁
What and where i am doing wrong while using Import Export Wizard in Mgt Studio? My mind is frozen today, need help....
I don't know enough about the inner workings of the Import Export Wizard to flip a nickel... like BCP and a couple of other import programs, there's gotta be a switch someone that will allow IEW to retain nulls as nulls.
Heh... if nothing else, maybe this will serve as another "bump" for you and someone who knows the answer may see it and reply. I'm curious about it as well.
--Jeff Moden
August 13, 2008 at 2:17 am
i tested your case using import/export wizard and find the same results you find
select * from tt
where tt.[col3] is null
-- returns (0 row(s) affected)
select * from tt
where tt.[col3] =''
--returns (30 row(s) affected)
the only way to correct is:
update tt
set col3 = null
where tt.[col3] =''
or use bcp for handling null string values
August 14, 2008 at 7:30 am
We do something similiar to this with TSQL:
BULK INSERT dbname.dbo.[tablename]
FROM 'C:\FolderName\PipeDelimitedFileName.TXT'
WITH (FieldTerminator = '|',
FirstRow = 2) -- If you have column headers as shown in you sample file
August 14, 2008 at 6:19 pm
I can't spend more time onto this matter. I have tested different scenarios and came to result that there is some sort of bug or missing functionality in Import Export Wizard in SQL Mgt Studio to import files successfully.
Different scenarios is have test are:
1. Import file using wizard and create the table on fly with default settings.
Result: Values get changed to blanks hence mission FAILED
2. Import file using wizard and create the table on fly. Change the table strcuture in edit mappings and add default value to the col3 and col4 as NULL. If there is no value fed, sql server is supposed to have NULL values as col data.
Result: Values get changed to blanks hence mission FAILED
3. Create table before importing file and have default values as NULL. Import the file using wizard and insert records into created table.
Result: Values get changed to blanks hence mission FAILED
4. Create table before importing file and import file using BCP with -k delimeter.
Result: Values don't get changed to blanks hence mission ACCOMPALISHED
I am of the opinion (can't find any reference) that Import Export Wizard also uses BCP or BULK INSERT while importing file to database but really shocked onto the behaviour i have experienced now.
I think from now on, i would use BCP to import and export text files rather than wizard. No doubt i have to spend more time in creating format files and then import data but atleast BCP can gaurantee the data is not changed.
Please let me know if the results of your tests are different than mine.
August 14, 2008 at 6:25 pm
Sorry MOH and RML51, i havent seen your posts before posting my results.
MOH:
What if you are importing a file with more than 50 columns? It would be a painful job writing T-SQL update statements for most of the columns which could have NULL Values. Therefore i would prefer to use BCP with -k switch to get rid of writing update statements.
RML51:
Thanks for sharing knowledge regarding FIRSTROW.
Cheers
Anam
August 14, 2008 at 6:53 pm
I forgot to mention in my earlier post, i also found something strange. A big difference in data when a file is exported from sql server using Import Export Wizard and BCP.
I read many blogs over internet (sorry dont have links now) that NULL character (\0) is not exported while exporting file and it can't be viewed in text file.
I am using NOTEPAD++ text editor. The file which i attached in my first post was the one exported using Import Export Wizard. If i open this file in notepad++ i get result like the one shown in test.jpg
But when i export the data from same table using BCP with following command
bcp testdb.dbo.test out c:\tmp\testnull.txt -c -T -S testsvr -t "|" -r ""
i get results shown in testnull.jpg One thing to notice here is that i haven't used -k switch while exporting data. Therefore i could conclude that it is default setting in BCP to export NULL as NULL values.
Experts, what are your thoughts over this?
August 15, 2008 at 7:12 am
--On a daily basis I need to import data from a text / csv file. The best method thatworked for me was to create a view and then work with that.
Create VIEW TextFileRaw
AS
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\ProdServ\Data\;HDR=YES',
'SELECT * FROM MytextFile.txt')
--To import I use this statement:
Create Procedure ImportTextFileRaw
as
Insert DataTable
Select * from TextFileRaw where [datetimefield]>(Select Max([datetimefield]) from DataTable)
GO
--assumes it has a date time filed that can be queried to determine which rows are newer
--otherwise use a drop table first and import the whole file each time
--I schedule it using the Windows task scheduler and run this command
--"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S ProdServ -d DatabaseName -U User -P Password -Q "exec ImportTextFileRaw"
August 16, 2008 at 2:36 pm
If you simly save the package rather than executing it in the Import Design Wizard, then open the dtsx file with Visual Studio or the Business Intelligence Development Studio, then double click on the data flow task and double click on the Flat File Source (not the connection manager.) There will be a check box to "Retain null values from the source as null values in the dataflow." That should fix your problem. By default the import design wizard sets this value to false.
IMHO the Import/Export design wizard(s) offer very limited access to all the features of the SSIS packages that they create. It's better to use Visual Studio or its SQL Server counterpart to design and edit these packages, if you're concerned with quality rather than quickness.
August 17, 2008 at 10:33 pm
Hi charles
I agree with you that Import/Export design wizard(s) offer very limited access to all the features of the SSIS packages, but i can't use SSIS in my current process. I'll better stick to BCP as it is working fine for me now.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply