Importing/Exporting Text File 2

[goolawah]

I have developed a simple application in in Access (and it works fine) but must now convert it to VB .Net so that I can extend its capabilities.

In the process I am learning VB .Net.

I need to import a text file, do some data manipulation and merging, then export the result as a text file in a different format.

I would appreciate any pointers on how to tackle importing and exporting text files.

 

[infinitelo]

Text file processing is kind of a broad subject, if you
file is in csv or sdf format you can use the data functions in vb, (data sets sql ect..)

if your text file is in a report format, then it gets a
little trickier.

This should help you get started with datatable
----------------------------------
Dim z(1)
Dim dt As New DataTable
Dim i As Integer
Dim x As String

dt.Columns.Add(New DataColumn("col1", GetType(String)))
dt.Columns.Add(New DataColumn("col2", GetType(String)))
Me.DataGrid1.DataSource = dt
z(0) = "row1value"
z(1) = "row2value"
dt.Rows.Add(z)
Me.DataGrid1.DataSource.newrow()
-----------------------------


if it is to be it's up to me

 

[goolawah]

Sorry to be a pest with "newbie" questions on this forum. I am working my way through a course on this as time permits but have an immediate short term need to help someone with a (hopefully) simple application.

I have done it before In ACCESS and it works fine.

I am wondering if I break it down to very minor steps it might be easier. Here goes...

The first thing I need to do is click a button to import data from a text file that looks like this -
[Blue]CUSTNO,REQ,ORDER,STORE,SEQ,PRODNO,QTY
9313938000501,14319,9005785,1002,6,SGCLMINI,3
9313938000501,14319,9005785,1002,1,SNMATCH3,3
9313938000501,14319,9005785,1002,5,SABTARU04,6
9313938000501,14319,9005785,1006,1,SUCROSS4,24
9313938000501,14320,9005786,1001,2,SUCROSS4,24
9313938000501,14320,9005786,1001,1,SUWALL5,6
9313938000501,14320,9005786,1005,1,RSB05LUCKY,2
9313938000501,14320,9005786,1009,2,SUCROSS4,24
9313938000501,14320,9005786,1014,1,SUWALL5,6
9313938000501,14320,9005786,1016,1,RSB05TERM,2
9313938000501,14320,9005786,1018,1,RSB05TERM,2
9313938000501,14321,9005787,1013,2,SUCROSS4,24
9313938000501,14321,9005787,1013,4,SGCLJUNIOR,3
9313938000501,14321,9005787,1013,5,SADAPUMP1205,12
9313938000501,14321,9005787,1017,1,SUWALL5,6
9313938000501,14321,9005787,1017,4,SGCLJUNIOR,3
9313938000501,14321,9005787,1025,4,SUMASC03,3
9313938000501,14321,9005787,1027,2,SUCROSS4,24
9313938000501,14321,9005787,1027,6,SGCLMINI,3[/BLUE]
I can ignore the third column "ORDER". The real file has many more fields, most of which are irrelevant, and many more records. But this is the guts of it.

I assume from the response by infinitelo above that I should finish up with a DataTable from which I can read the data for subsequent merging/manipulation.

In the data setion of the VB.Net toolbox I note that there is a DataSet and a DataView but nothing about DataTable.

At this stage I am hoping to start a new blank project that will simply import the data above in a way that I can see it, and subsequently use it. Hopefully, once I have that right I can progress from there and learn on the way.

Any help on this will be greatly appreicated.

 

[ca8msm]

As the data is in csv format, you can simply use the Microsoft Text Driver to load the data into a DataTable:

http://www.dotnetspider.com/technology/kbpages/890.aspx

As for the differences between the DataSet, DataView and DataTable, the main things to note are:

1) A DataTable is a table of data
2) A DataSet is a collection of DataTables (you can also create joins between these tables)
3) A DataView is a view of a DataTable (e.g. you can specify a RowFilter to select certain records)


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[goolawah]

Thanks ca8msm for your help. It's gradually getting clearer. Using ODBC and the Microsoft Text Driver was brilliant and I can now preview the required data from the actual sample data file.

As I am still not comfortable with writing code I am using toolbox wizards so that I can hopefully learn the correct syntax etc. I now have a new VBA .Net project containing a blank form with, at the bottom of the form design view, an ODBC Data Adapter, an ODBC Connection and a DataSet that I have named InputText. These are correctly linked with my imput text file and I can preview the data and check that the schema is correct.

When I view the code for this form I get -

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "


    Private Sub DataView1_ListChanged(ByVal sender As System.Object, ByVal e As System.ComponentModel.ListChangedEventArgs)

    End Sub

    Private Sub OdbcConnection1_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.Odbc.OdbcInfoMessageEventArgs) Handles OdbcConnection1.InfoMessage

    End Sub

End Class

There are also quite a few lines in the " Windows Form Designer generated code " region related to ODBC. But my impression is that these are not directly related to what I am trying to do.

I am not clear where I would add additional code (as in sample from infinitelo above) to actually read and process this data. I already have the code logic from my VBA-Access version and may be able to re-use that if I can get clear how the code hangs together in VB .Net.

I would appreciate any input that will help me continue progressing with this...

Thanks in anticipation...

 

[ca8msm]

The sections of code that are in the "Windows Form Designer generated code" are generated by Visual Studio when you drop controls onto your form (e.g. the Data Adapter that you added). You don't need to worry about this section and should stay clear of it for now.

As for processing the file, you will need to do the same as you would do if you were doing your application in Access. Drop a TextBox onto the form, and the following code will be generated in your code behind file:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
    End Sub

This is where you can start to process your DataSet that you have created and then export the results.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ca8msm]

Drop a TextBox onto the form

That should have said "Drop a [attn]Button[/attn] onto the form"


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[goolawah]

I hope this thread isn't getting too long. I'm not sure of the protocol on this forum. Anyway, I have found that I can access data in a text file by using File Open/Input and a Do Loop (as in simplified example below, which works fine).

Private Sub btnLaod_Click(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles btnLaod.Click

        Dim txtName As String, intScore As Integer
        FileOpen(10, "C:\Golfers.txt", OpenMode.Input)
        FileOpen(1, "C:\Output.txt", OpenMode.Output)
        
        txtName = ""
        intScore = 0
        Do Until EOF(10)
            Input(10, txtName)
            Input(10, intScore)
            lstEntries.Items.Add(txtName & " " & Str(intScore))
            WriteLine(1, intScore, txtName)
        Loop

        FileClose(10)
        FileClose(1)

    End Sub

Alternatively, I can also read the text file using the Microsoft Text Driver and an ODBC Adapter. I think this latter approach would be preferable but I cannot discover the code to make the button-click fill the data set.

Below is the code where I was trying with a simple example form onto which I have placed -
DataAdapter1 which links correctly to my input text file.
DataGrid1 which also links correctly to the DataAdapter as it shows the column headings required.
Button btnLoad.

 Private Sub OdbcDataAdapter1_RowUpdated(ByVal sender As _
    System.Object, ByVal e As _
    System.Data.Odbc.OdbcRowUpdatedEventArgs)

    End Sub

    Private Sub DataGrid1_Navigate(ByVal sender As System.Object, _
    ByVal ne As System.Windows.Forms.NavigateEventArgs) _
    Handles DataGrid1.Navigate

    End Sub

    Private Sub btnLoad_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnLoad.Click

    End Sub

Can anyone help me with the code required to load the DataGrid with the contents of the text file when the button is clicked?

Many thanks for help received so far. We are progessing...

 

[chrissie1]

oh god. fileopen is really old and you should try and forget it.

try using and reading up on the streamreader and streamwriter

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[goolawah]

Ah yes! But as a newbie to this it is the only approach I have been able to get working, and the immediate project I have to deal with is very rudimentary.

Anyway, thanks for the suggestion. I'll check it out.

 

[chrissie1]

it's not as difficult as the fileopen

straight from msdn

streamreader to read from file

Imports System
Imports System.IO

Class Test
    Public Shared Sub Main()
        Try
            ' Create an instance of StreamReader to read from a file.
            Dim sr As StreamReader = New StreamReader("TestFile.txt")
            Dim line As String
            ' Read and display the lines from the file until the end 
            ' of the file is reached.
            Do
                line = sr.ReadLine()
                Console.WriteLine(Line)
            Loop Until line Is Nothing
            sr.Close()
        Catch E As Exception
            ' Let the user know what went wrong.
            Console.WriteLine("The file could not be read:")
            Console.WriteLine(E.Message)
        End Try
    End Sub
End Class

streamwriter to write to file

Imports System
Imports System.IO

Class Test
    Public Shared Sub Main()
        ' Create an instance of StreamWriter to write text to a file.
        Using sw As StreamWriter = New StreamWriter("TestFile.txt")
            ' Add some text to the file.
            sw.Write("This is the ")
            sw.WriteLine("header for the file.")
            sw.WriteLine("-------------------")
            ' Arbitrary objects can also be written to the file.
            sw.Write("The date is: ")
            sw.WriteLine(DateTime.Now)
            sw.Close()
        End Using
    End Sub
End Class

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[infinitelo]

This is getting confusing.

on your form (in forms designer) you will need
1 a button
2 a datagrid

the question to ask is how to load the data grid from a
csv file when the button is clicked.



if it is to be it's up to me