[RESOLVED] save access table to text file

[bezaman]

i have a comma delimitted text file that i need to add three fields and update the contents of those three fields and then save the text file. what would be the best way to do this?

right now i bring the file into ms access and alter the table to add the fields and then update them but i don't know how to export the access table back into a comma delimitted text file. anybody know how to do this?

[bezaman]

this is how i import the text file into an access db:

'import text file into access db
sql.CommandText = "SELECT * INTO [tblMailList] FROM [Text;DATABASE=" & FilePath & ";HDR=NO].[" & FileName & "]"
sql.ExecuteNonQuery()

can i do something like this to put it back into a text file?

[mbarnett]

well I'm not sure how you are adding the values once you add the fields. I think the faster way would be to use Excel and save as CSV. here is a program that does it too

Public Sub loadcsv()

        Dim holdingclass As holdingclass = New holdingclass
        Dim holdingclasslist As ArrayList = New ArrayList
        ' since i dont know how many values you have in your csv I'll assume at least two 
        ' i built a class that has 4 values
        Dim filename As String = "C:\Test.csv"
        Dim fields As String()
        Dim delimiter As String = ","
        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData
                'instantiate a new instance of your holding class
                holdingclass = New holdingclass

                ' Read in the fields for the current line
                fields = parser.ReadFields()

                holdingclass.Class_id = fields(0)
                holdingclass.value_2 = fields(1)
                holdingclass.value_3 = fields(2)

                ' now that you have saved the data you have read in for your class stor it in an arraylist 
                ' so later you can iterate through the list adding your other values.
                holdingclasslist.Add(holdingclass)

            End While
        End Using

        ' now I'm assuming you have a way to identify each line you read in by an Id 
        ' lets say class_ID is your ID andit is the first value in  your CSV
        ' you did not specify how you wanted to add the values to the first csv so if you had them
        ' in another file you could just loop through  your list looking for the matching ID.
        ' while reading in the second file.
        filename = "C:\Test_2.csv"
        Dim fields2() As String
        delimiter = ","
        Using parser As New TextFieldParser(filename)
            parser.SetDelimiters(delimiter)
            While Not parser.EndOfData
                ' Read in the fields for the current line
                fields2 = parser.ReadFields()
                For Each record As holdingclass In holdingclasslist
                    If record.Class_id = fields2(0) Then
                        record.value_4 = fields2(1)
                        record.value_5 = fields2(2)
                        record.value_6 = fields2(3)
                        Exit For
                    End If
                Next
            End While
        End Using
        'now that you have your original file and your added fields in the same class you can iterate through the classlist 
        ' printing the CSV 
        For Each H_class As holdingclass In holdingclasslist
            Dim writeline As String = H_class.Class_id & "," & H_class.value_2 & "," & H_class.value_3 & "," & H_class.value_4 & "," & H_class.value_5 & "," & H_class.value_6 & vbCrLf
            My.Computer.FileSystem.WriteAllText("C:\Combined.csv", writeline, True)
        Next

    End Sub

here is the holder class

Public Class holdingclass
    Private ClassId, value2, value3, value4, value5, value6 As String

    Public Property Class_id() As String
        Get
            Return ClassId
        End Get
        Set(ByVal value As String)
            ClassId = value
        End Set
    End Property
    Public Property value_2() As String
        Get
            Return value2
        End Get
        Set(ByVal value As String)
            value2 = value
        End Set
    End Property
    Public Property value_3() As String
        Get
            Return value3
        End Get
        Set(ByVal value As String)
            value3 = value
        End Set
    End Property
    Public Property value_4() As String
        Get
            Return value4
        End Get
        Set(ByVal value As String)
            value4 = value
        End Set
    End Property

    Public Property value_5() As String
        Get
            Return value5
        End Get
        Set(ByVal value As String)
            value5 = value
        End Set
    End Property
    Public Property value_6() As String
        Get
            Return value6
        End Get
        Set(ByVal value As String)
            value6 = value
        End Set
    End Property
End Class

hope that gives you an idea

[bezaman]

i already know how to add the fields. i just need to know how to export the data into a comma delimitted text file. this is my code:

txtDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [tblMailList]", cnxn)
 
TextFileCommandBuilder = New System.Data.OleDb.OleDbCommandBuilder(txtDataAdapter)
 
'clean up old data if necessary
cnxn.Open()
sql.Connection = cnxn
 
Dim SchemaTable = cnxn.GetSchema("TABLES", New String() {Nothing, Nothing, "tblMailList"})
     If SchemaTable.Rows.Count <> 0 Then
          sql.CommandText = "DROP TABLE [tblMailList]"
          sql.ExecuteNonQuery()
     End If
 
'import text file into access db
sql.CommandText = "SELECT * INTO [tblMailList] FROM [Text;DATABASE=" & FilePath & ";HDR=NO].[" & FileName & "]"
sql.ExecuteNonQuery()
 
'add fields
sql.CommandText = "ALTER TABLE [tblMailList] ADD COLUMN [ID] AUTOINCREMENT;"
sql.ExecuteNonQuery()
sql.CommandText = "ALTER TABLE [tblMailList] ADD COLUMN [CLUBADDR] CHAR(100);"
sql.ExecuteNonQuery()
sql.CommandText = "ALTER TABLE [tblMailList] ADD COLUMN [CLUBCSZ] CHAR(100);"
sql.ExecuteNonQuery()
sql.CommandText = "ALTER TABLE [tblMailList] ADD COLUMN [CLUBPHONE] CHAR(100);"
sql.ExecuteNonQuery()
sql.CommandText = "ALTER TABLE [tblMailList] ADD PRIMARY KEY (ID);"
sql.ExecuteNonQuery()
 
'fill data tables
txtDataAdapter.Fill(tblTextFile)
 
For i = 0 To tblTextFile.Rows.Count - 1
     Dim ModifyRow As DataRow = tblTextFile.Rows(i)
     ModifyRow("CLUBADDR") = TextBoxClubAddress.Text
     ModifyRow("CLUBCSZ") = TextBoxClubCSZ.Text
     ModifyRow("CLUBPHONE") = TextBoxClubPhone.Text
Next i
txtDataAdapter.Update(tblTextFile)

[mbarnett]

if your dead set on using access check this out
http://www.mredkj.com/netconversions/dth.html

I think you could do it with excel with a lot less effort.

[bezaman]

i just think access provides an easy way to add 3 fields and then update those 3 fields with data that was input from the user. i don't have an id field so i add the autonumber field and set it as the primary key so i can do my updates.

1. open comma delimitted text file (fields are enclosed by quotes)
2. add three fields to the end of each line of data
3. update each of those three fields with the data the user enters in textbox
4. save text file in original location

[bezaman]

This is how i ended up doing it. There is no need for access. This way is short, simple and fast.

Dim txtString() As String = IO.File.ReadAllLines(ofd.FileName)
For i = 0 To txtString.Length - 1
     txtString(i) = txtString(i) & ",""" & TextBoxClubAddress.Text & """,""" & TextBoxClubCSZ.Text & """,""" & TextBoxClubPhone.Text & """"
Next
IO.File.WriteAllLines(ofd.FileName, txtString)