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 |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
 Posted - 2007-04-25 : 15:35:25
|
I am importing a flat file with fixed width fields and some of the values may be empty so I wanted to insert a NULL for those columns. I am getting errors in the Data Conversion step when trying to convert to TINYINT for example. Is there an easy way to have the process set the value to NULL if the data is blank? The only way I know how to do it is with a Derived Column and an Expression like: LEN(TRIM(ColumnName)) == 0 ? NULL(DT_UI1) : (DT_UI1)ColumnName Is there a better way? Because I need to do this for a couple hudreded columns.-Ryan |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-25 : 15:50:12
|
in a flat file, NULL is represented by an empty space. for example, if it's csv:1,2,,4in the 3rd column there is a null.How are the nulls represented in your file? if they are represented with the string "NULL" then bcp.exe will choke (here I am assuming you are using bcp for the import).
www.elsasoft.org |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-04-25 : 16:07:34
|
Not BCP I'm using the SSIS Paradigm of a Data Flow (Source -> Transform -> Destination). In my case: Flat File -> Data Conversion -> OLE DB Destination.As for the file format, it is not delimited. The columns are fixed width. So, for example, if we define a file format of FirstName (5), PhoneNumber (4), Age (3) and Address (10). The data would look something like: bob1234 32 1234 1stbobby8797 23456 2nd And that translates to a table like:FisrtName PhoneNumber Age Address-----------------------------------bob 1234 32 1234 1stbobby 8797 NULL 23456 2nd So in this example I want a blank age to be inserted as NULL. Hopefully, that makes more sense. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-25 : 16:19:25
|
i have (happily!) no real experience with DTS/SSIS so can't help you much there. although I have to say I don't understand how one can even represent a NULL in a file with no delimiters. If the column was a text column of width N, how would you distinguish between NULL and N spaces? I don't think you can with such a format. maybe I'm missing something.EDIT: oops, i just realized i was trying to answer a post in the DTS/SSIS forum, an area where i am a know nothing! sorry bout that. 
www.elsasoft.org |
|
|
|
|
|
|
|
|
Flate File NULLs