Write to fixed length text file from dataset

Need help.  Beginner with Visual Studio - I have a dataset that contains four fields.

Loan Number
Borr Last Name
Employee Name
Close Date

How do I write in VB to create a fixed length text file from this data set? I need to create c:\test.txt  in this format:

LoanNumber(10 position with padded zeros to left)  
Borr Last Name(40 position)
Employee Name (40 position)
Closed Date (MMDDYYYY)

I need one row per record.  

This is all I have.  Where do I start?

Module Module1

    Sub Main()
       

    End Sub

End Module
LVL 2
asmyattAsked:
Who is Participating?
 
CodeCruiserCommented:
I would do something like below

Dim dbadp As New SqlDataAdapter("Select * From ...", "Your connection string")
Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose

For i as integer = 0 to dTable.Rows.Count - 1
...
0
 
CodeCruiserCommented:
Try following

Dim sb As New IO.Text.StringBuilder
For i As Integer = 0 to dTable.Rows.Count - 1 'dTable is a datatable or use dataset1.tables(0)
  sb.WriteLine(dTable.Rows(i).Item(0).ToString().PadLeft("0", 10) & dTable.Rows(i).Item(1).PadRight(" ", 40) & dTable.Rows(i).Item(2).PadRight(" ", 40) & dTable.Rows(i).Item(3).ToString("MMddyyyy"))
Next

IO.File.WriteAllText("C:\test.txt", sb.tostring())
0
 
VBClassicGuyCommented:
Dim h as Integer
Dim txt As String

h = FreeFile
Open "C:\test.txt" For Append As h
txt = Right(String(10, "0") & LoanNumber, 10)
txt = txt & Right(Space(40) & BorrLastName, 40)
txt = txt & Right(Space(40) & EmployeeName, 40)
txt = txt & Right(Space(10) & CloseDate, 10)
Print #h, txt
Close #h

That's if the fields are strings. If they are the results of a database recordset, use:

Dim h as Integer
Dim txt As String

h = FreeFile
Open "C:\test.txt" For Append As h
txt = Right(String(10, "0") & CStr(rs!LoanNumber), 10)
txt = txt & Right(Space(40) & CStr(rs!BorrLastName), 40)
txt = txt & Right(Space(40) & CStr(rs!EmployeeName), 40)
txt = txt & Format(CDate(rs!CloseDate), "mm/dd/yyyy"), 10)
Print #h, txt
Close #h
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
VBClassicGuyCommented:
Opps. Didn't see how you were formatting the date. Also had an error when formatting the date if a recordset. Use this instead:

Dim h as Integer
Dim txt As String

h = FreeFile
Open "C:\test.txt" For Append As h
txt = Right(String(10, "0") & LoanNumber, 10)
txt = txt & Right(Space(40) & BorrLastName, 40)
txt = txt & Right(Space(40) & EmployeeName, 40)
txt = txt & Format(CloseDate, "mmddyyyy")
Print #h, txt
Close #h

That's if the fields are strings. If they are the results of a database recordset, use:

Dim h as Integer
Dim txt As String

h = FreeFile
Open "C:\test.txt" For Append As h
txt = Right(String(10, "0") & CStr(rs!LoanNumber), 10)
txt = txt & Right(Space(40) & CStr(rs!BorrLastName), 40)
txt = txt & Right(Space(40) & CStr(rs!EmployeeName), 40)
txt = txt & Format(CDate(rs!CloseDate), "mmddyyyy")
Print #h, txt
Close #h
0
 
VBClassicGuyCommented:
Ut oh, too late on the Submit button. CodeCruiser's answer is better anyway...
0
 
asmyattAuthor Commented:
Getting close. I have two errors:

1)  IO.Text.StringBuilder is not defined

2)  OnbaseScheduledClosingsDataTable is a type in 'ConsoleApplication1.InterlinqE3DataSet' and cannot be used as an expression.

Here is the syntax for 2)  INTERLINQE3DataSet.OnbaseScheduledClosingsDataTable(0).Rows.Count

My dataset name is INTERLINQE3DataSet and the table(actually view) is OnbaseScheduledClosings
0
 
CodeCruiserCommented:
For 1) change to System.Text.StringBuilder (my mistake)

For 2) change to INTERLINQE3DataSet.OnbaseScheduledClosingsDataTable.Rows.Count
0
 
asmyattAuthor Commented:
Well, solved them but created two more errors

1) Reference to a non-shared member requires an object reference

syntax:    INTERLINQE3DataSet.OnbaseScheduledClosingsDataTable.Rows

2) 'WriteLine' is not a member of 'System.Text.StringBuilder'

0
 
CodeCruiserCommented:
Oops. Problems as I wrote the code without intellisense.

1) You need to instantiate and fill a datatable. Do you really need to use typed datasets?
2) Change writeline to AppendLine
0
 
asmyattAuthor Commented:
No, I don't have to use typed datasets. That is just the only way I know how. I'm not sure what "need to instantiate and fill a datatable" means.

The AppendLine worked.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.