Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
devisetti
Starting Member
30 Posts |
 Posted - 2008-05-26 : 04:53:10
|
| Guys ,Sorry,I am posting this question again..I have generated a pipe delimited file using bcp option.I am getting the ouput for some columns as some some special characters(boxes). When ,I have checked my source data in few columns the data is stored with '' instead of null.I can see this special character when opening with editplus/wordpad only not with notepad. Purpose of the flat file:This flat file will be used to load into oracle table.because of this reason all the rows are failing.Do ,I need to fix this space issue in my stored procedure or when doing bcp. The data I have problem is like thisSelect * from test_data where test_column=''. These are the columns ,I ma getting boxes.Any suggestions.. to avoid this special boxes when generating the flatfile.ex:I|82779000|0000031166|MR|DAVID|COOTE|1961-03-02 00:00:00.000|0|0|0|0|0|0|????????|thanks in advance |
|
|
mathiyazhagan.sekar@gmail
Starting Member
11 Posts |
Posted - 2008-05-26 : 11:57:42
|
| Hi, If you are sure that '' is replaced by null,then revert '' to NULL with NULLIF function.ie> update test_data set NULLIF (test_column,'')refer link :http://msdn.microsoft.com/en-us/library/ms177562.aspx.Here , if test_column='', then it is replaced by NULL and then generate flat file.Cheers,MathiIndia. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-26 : 14:13:55
|
| Do you mean it's an empty string?There is a problm with csv files - how do you distinguish a null from an empty string? In fact in some languages there's no difference between them.If you know the columns this happens for then you can replace with a null in the export.Either export from an SP or view which formats the table.Replace the columns viacaes when col1 = '' then null else col1 endornullif(col1,'')Another option which will make your export more flexible is to create te lines in an SP and not leave bcp to insert the delimitters. This will give you control over everything that happens.Have a look athttp://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/If this is going to be a long term solution I would consider doing something like that.It isolates the transport from the format and makes it easy to build new ones or change the method globally.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Flat file