May 14, 2010 at 9:09 pm
I really need help with this one. I am using a script task w/VB.net to write the data pulled from a MS SQL DB to a fixed width file. The script takes the dataset's table rows adds them to an array and writes the value of the column to the file using a streamwriter. The problem is when any of the DB values are NULL it throws off the fixed width. I'm not sure if I should try to handle this in the Stored Procedures. If so, how should I make sure that if the value is NULL ,an empty string of the correct width is passed? Or should I handle it in the VB script with something like 'If not dbnull' but I'm not sure how to get the length the field is supposed to be from the array item.. If I am able to get the length how can I use that to write spaces or x's in place of actual data. I am new to this so I am stumped. Any help is greatly appreciated!
May 14, 2010 at 10:20 pm
Try this link.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 15, 2010 at 2:31 am
If your script task is doing everything, it must 'know' what the field lengths should be - how else could it write a fixed-width file?
If the field came in as an empty string ("") instead of NULL, would your script work? If so, just a couple of lines of code will do the replace for you.
May 15, 2010 at 7:12 am
Thanks for your response. The script task does do everything and it does know the lengths of the fields except when the DB returns DBNull as a value. Then it writes 1 space instead of the defined length for the field, which then throws off the fixed width of that record. The problem is I don't know in advance which fields will return with DBNulls as values in order to send an empty string instead. Therefore, I was wondering how in the script task could I still print the field with the correct number of empty spaces. I checked the attributes for the itemArray where the value was null and there does not seem to be a length assigned because it is DBNull.
An alternate way I guess would be to write any record containing a null value to a file but since I have to do everything in the script I'm not sure how.
May 15, 2010 at 4:50 pm
apache i don't know SSIS, but i know .NET;
I've already written some code that i use in .NET all the time that takes any dataset and returns the data as a fixed width string, which i can then write to a file.
i've done the same thing to make tab delimited comma delimited,custom delimited as well.
i'm not sure if this will help you at all, but take a look at these functions;
the function "FormatDataSet" returns a long string,with crlf for the end of line, and the rest is fixed width based on the defined max size of the column.
I've added the functions i use to convert nulls to strings/ints etc. as well
Private Function FormatDataSet(ByVal dt As DataTable) As String
Dim results As String = ""
'--initialize the length, stored in the .Tag, to be at least as long as the ColumnName + 2 chars
For Each col As DataColumn In dt.Columns
If GetInteger(col.Caption) < GetString(col.ColumnName).Length + 2 Then
col.Caption = GetString(col.ColumnName).Length + 2
End If
Next
'--no easy way to find the max(len()) of each column, iterate thru every damn row (RBAR!)
For Each dr As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
If GetInteger(col.Caption) < GetString(dr(col)).Length + 2 Then
col.Caption = GetString(dr(col)).Length + 2
End If
Next
Next
'--now build our string
'headers:
For Each col As DataColumn In dt.Columns
results = results & RPad(col.ColumnName, GetInteger(col.Caption))
Next
results = results & vbCrLf
'--now spew the results
For Each dr As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
results = results & RPad(dr(col), GetInteger(col.Caption))
Next
results = results & vbCrLf
Next
Return results
End Function
Public Shared Function GetString(ByVal objValue As Object) As String
If objValue Is Nothing OrElse Convert.IsDBNull(objValue) Then
Return ""
Else
Return Convert.ToString(objValue)
End If
End Function
Public Shared Function GetInteger(ByVal objValue As Object) As Integer
If objValue Is Nothing OrElse Convert.IsDBNull(objValue) Then
Return 0
Else
If IsNumeric(objValue) Then
Return Convert.ToInt32(objValue)
Else
Return 0
End If
End If
End Function
Public Shared Function GetDecimal(ByVal objValue As Object) As Decimal
If objValue Is Nothing OrElse Convert.IsDBNull(objValue) Then
Return 0.0
Else
If IsNumeric(objValue) Then
Return Convert.ToDecimal(objValue)
Else
Return 0.0
End If
End If
End Function
Private Function RPad(ByVal val As String, ByVal maxsize As Integer) As String
Dim x As String
If maxsize >= Len(val) Then
x = val & Space(maxsize - Len(val)) '--value plus a number of spaces
Else
x = val
End If
Return x
End Function
Private Function LPad(ByVal val As String, ByVal maxsize As Integer) As String
Dim x As String
If maxsize >= Len(val) Then
x = Space(maxsize - Len(val)) & val '--value plus a number of spaces
Else
x = val
End If
Return x
End Function
Lowell
May 17, 2010 at 8:09 am
Yes if it came in as a string filled with the number of spaces to match the fixed width for the field that would be fine. Thanks Phil
May 17, 2010 at 8:11 am
This is awesome code! Thanks Lowell. I am trying to get the understanding as to how the caption gives the length of the field. Thanks
May 17, 2010 at 8:18 am
it's an ugly implementation, but it works.
it's based on the actual contents....if something was defined as a varchar(max), how big is it in a fixed with file?
for me,i'm looping thru all the records, and fincing the longest string(i'm sure there is a better way to find it in hte datatable)
so even though something is defined huge, it might only be 517 chars as the longest int he data set....so i use that plus two chars for spacing.
note that if you gota differnet set of data based on a WHERE clause, a field might be 1200 chars for that export of the data...so one exported dataset is not the same as te second exported dataset, even though they are both fixed width.
the only other way to make them all the same width would be to assume a max size for fixed width varchar fields and truncate them if too large, i think.
Lowell
May 17, 2010 at 8:22 am
apache626 (5/17/2010)
Yes if it came in as a string filled with the number of spaces to match the fixed width for the field that would be fine. Thanks Phil
I must be missing something, because this seems trivial to me:
If String.IsNullOrEmpty(X) Then
X = New String(" ", 40)
End If
Or are you saying that the widths are not fixed?
May 17, 2010 at 9:08 am
The widths are fixed but each field has a different width I do not know as I am looping through which field will be null. It could be phone number for (8) or middleName for (25).
May 17, 2010 at 9:15 am
OK - so what happens when there is an X25 field which contains the text "Fred"? At what point is the padding done?
May 17, 2010 at 9:30 am
This is what I am using so far. I hoped to add code that says if this array(i) is = to DBNUll then add the number of needed space. However, in the SP I created temp tables with the proper width. This works so each field when written gets its proper width. Only when the value is null it doesn't write anything. Which throws off the record which should equal out to 400.
Dim sw As New StreamWriter(DynamicFileName.ToString(), True)
For Each dr As DataRow In table.Rows
Dim array() As Object = dr.ItemArray
For i As Integer = 0 To (array.Length - 1)
sw.Write(array(i).ToString())
End If
Next
sw.Write(Environment.NewLine)
May 17, 2010 at 9:39 am
At some point before doing the write you should check array(i) and modify to an empty string if it is null. eg:
For i As Integer = 0 To (Array.Length - 1)
If String.IsNullOrEmpty(Array(i)) Then Array(i) = ""
sw.Write(Array(i).ToString())
etc etc
May 17, 2010 at 1:16 pm
I have added the checking, but if the String is null and I make it's value "" will it be the width of the column defined in the database or a single space?
May 17, 2010 at 1:27 pm
You implied in the last post that anything other than a NULL was being handled correctly - so I gave you a way of making sure you weren't passing a NULL. Simple as that. If this does not work, you have omitted telling us something important.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy