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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.