Export Microsoft Access Database to Text File

[Cass]

Hi, I'm using Visual Basic Express 2008. I've created a project which
connect to a Microsoft Access Database & displays the records in the
app with the Default Navigation.

I need to add a button which exports the database to a fixed length
text file. Does anyone have the code for this?

So far this is the forms code:

Public Class Customers

Private Sub CustdataBindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
CustdataBindingNavigatorSaveItem.Click
Me.Validate()
Me.CustdataBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.CustdbDataSet)

End Sub

Private Sub Customers_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the
'CustdbDataSet.custdata' table. You can move, or remove it, as needed.
Me.CustdataTableAdapter.Fill(Me.CustdbDataSet.custdata)

End Sub
End Class

When I look at the app.config file it says I am using a OLEDB:
<connectionStrings>
<add name="Custs.My.MySettings.custdbConnectionString"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=|DataDirectory|\custdb.mdb"
providerName="System.Data.OleDb" />
</connectionStrings>

Thanks in advance for the help!

 

[Gregory A. Beamer]

Hi, I'm using Visual Basic Express 2008. I've created a project which
connect to a Microsoft Access Database & displays the records in the
app with the Default Navigation.

I need to add a button which exports the database to a fixed length
text file. Does anyone have the code for this?

SNIPPED CODE

There is no easy code for fixed width.

In general, what you will want to do, for each line, is use a
StringBuilder

Dim builder As new StringBuilder()

you then pad the individual bits, after converting to string:

builder.Append(int1.ToString().PadLeft('0', 9))
builder.Append(str1.PadRight(' ', 25))

You just need to go field by field to create the padded strings.

NOTE: if any item can be too large, you will have to check for this and
truncate.

Example:
In datbase address = 40 in length
In file address = 35 in length


If address1.Length > 35 Then
builder.Append(address1.Substring(0,35))
Else
builder.Apppend(address1.PadRight(' ', 35))
End If


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Cass]

There is no easy code for fixed width.

In general, what you will want to do, for each line, is use a
StringBuilder

Dim builder As new StringBuilder()

you then pad the individual bits, after converting to string:

builder.Append(int1.ToString().PadLeft('0', 9))
builder.Append(str1.PadRight(' ', 25))

You just need to go field by field to create the padded strings.

NOTE: if any item can be too large, you will have to check for this and
truncate.

Example:
In datbase address = 40 in length
In file address = 35 in length

If address1.Length > 35 Then
   builder.Append(address1.Substring(0,35))
Else
   builder.Apppend(address1.PadRight(' ', 35))
End If

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog:http://gregorybeamer.spaces.live.com

*******************************************
|      Think outside the box!             |
*******************************************

Ok so i'm confused... Excuse my ignorance but i'm new with this...
lets say for example I have a table with 3 fixed length fields.
Field 1 = 30 bytes
Field 2 = 60 bytes
Field 3 = 25 bytes

Would the code for the button look something like this?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim builder As new StringBuilder()
builder.Append(int1.ToString().PadLeft('0', 30))
builder.Append(str1.PadRight(' ', 60))
builder.Append(str2.PadRight(' ', 25))
End Sub

 

[Gregory A. Beamer]

Ok so i'm confused... Excuse my ignorance but i'm new with this...
lets say for example I have a table with 3 fixed length fields.
Field 1 = 30 bytes
Field 2 = 60 bytes
Field 3 = 25 bytes

Would the code for the button look something like this?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim builder As new StringBuilder()
builder.Append(int1.ToString().PadLeft('0', 30))
builder.Append(str1.PadRight(' ', 60))
builder.Append(str2.PadRight(' ', 25))
End Sub

You then have to take the output of the builder into a string and write
it out.

builder.Append(vbCrLf) 'this is a shortcut for char(13) and char(10)
string outputFileString = builder.ToString()

The comment is important, as some file formats only want a linefeed or
carriage return, and not both.

Then the output will be something like (note the hardcoded portions
please):

Dim writer as New StreamWriter("c:\file.txt:")
writer.Write(outputFileString)
writer.Close()

This should write out your fixed width.

If the file is huge, you will end up having to create a line at a time:

Dim writer as New StreamWriter("c:\file.txt:")

For Each row as DataRow in MyDataTable
Dim builder As new StringBuilder()
builder.Append(int1.ToString().PadLeft('0', 30))
builder.Append(str1.PadRight(' ', 60))
builder.Append(str2.PadRight(' ', 25))

writer.WriteLine(builder.ToString())

Next

writer.Close()

For a "how to do it write", encapsulate the code in its own method (or
library) and call it from the button event. Event handlers should be
very light when codeed correctly. you can get hte code working and then
select it and choose right click >> Refactor >> Extract method to
refactor out the method from the event handler. leave any code that
might dynamically create a file path in most cases.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************