Solved

Import from text file into database via vb.net

Posted on 2009-05-11
14
1,288 Views
Last Modified: 2013-12-25
Hello,

I am attemtping to load data from a text file, that I downloaded from the internet via vb.net code, inot an a field in an access database.  I've included the text file and access database for perusal.  First of all, I need to skip to line 9 to begin with the line that starts with ABI.  I need to add a K to the beginning of this string (so it matches the stationID's in the access DB) and then import that entire line into the windsaloft field in the access DB.

Thanks,
Cheese
FBIN.txt
weather.mdb
0
Comment
Question by:cheesebugah
  • 7
  • 7
14 Comments
 
LVL 3

Expert Comment

by:Kevin Robinson
ID: 24358576
Am at home at the moment so cant post exaxt code.  But use the code below to loop through the excel file.  Just stop/start  at the line you want using the count and indexof for the "ABI" bit.

    Public Sub ReadFile(ByVal fileName As String)
            Using reader As New TextFieldParser(fileName)
            reader.TextFieldType = FieldType.Delimited
 
            ' Set the delimiters for comma separated text
            reader.Delimiters = New String() {","}
 
            Dim currentRow As String()
 
            While Not reader.EndOfData
                Try
                    currentRow = reader.ReadFields()
                End Try
            End While
        End Using
 
    End Sub

Open in new window

0
 

Author Comment

by:cheesebugah
ID: 24359186
VDA,

Hmmm. First of all the file I'm trying to get the data from is not a csv file, it is a text file with blank spaces and  other various crap that get in the way of just copying it into the access database.

Thanks,
Cheese
0
 
LVL 3

Expert Comment

by:Kevin Robinson
ID: 24362386
Can you post the file
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:cheesebugah
ID: 24364751
VDA,

I did!  The file to import from (FBIN.txt  and the access DB (weather.mdb).

Thanks,
Cheese
0
 
LVL 3

Expert Comment

by:Kevin Robinson
ID: 24364835
Open the text file in Excel then save it as a CSV.  
0
 
LVL 3

Expert Comment

by:Kevin Robinson
ID: 24364839
Give me a moment and Ill try it this end and send you some code.
0
 

Author Comment

by:cheesebugah
ID: 24364870
VDA,

Hmmm.  Please notice that I only want the data from that text file starting with the row that starts with ABI.  Of course, all of this will be seamless to the user, the downloading of the information from a website, the parsing to a text file, then importing into the database.  

Thanks,
Cheese
0
 
LVL 3

Expert Comment

by:Kevin Robinson
ID: 24365124
Try This after you open in Excel and save as CSV.  Works for me.



 Public Sub ReadFile()
        Using reader As New TextFieldParser("c:\FBIN.csv")
            reader.TextFieldType = FieldType.Delimited
 
            ' Set the delimiters for comma separated text
            reader.Delimiters = New String() {","}
 
            Dim currentRow As String()
            Dim Windsaloft As String = ""
            Dim StationID As String = ""
            While Not reader.EndOfData
                If reader.LineNumber > 7 Then
 
                    currentRow = reader.ReadFields()
 
                    'Get the Station ID
                    StationID = "K" & currentRow(0).ToString()
 
                    Windsaloft = currentRow(1).ToString()
                    Windsaloft += currentRow(2).ToString()
                    Windsaloft += currentRow(3).ToString()
                    Windsaloft += currentRow(4).ToString()
                    Windsaloft += currentRow(5).ToString()
                    Windsaloft += currentRow(6).ToString()
                    Windsaloft += currentRow(7).ToString()
                    Windsaloft += currentRow(8).ToString()
 
                    '''''''''''''''''''''''''''''''''''''''''''''
                    'Add you insert to databse code here
                    '''''''''''''''''''''''''''''''''''''''''''''
                End If
                currentRow = reader.ReadFields()
            End While
        End Using
 
    End Sub

Open in new window

0
 

Author Comment

by:cheesebugah
ID: 24365245
VDA,

Hmmmm.  Is there a way convert or save the text file to a csv in code?  This has to be seamless to the user.  Also, couldn't I loop through the array instead of the individual statements:

Windaloft += currentRow(x).ToString()

Thanks,
Cheese
0
 
LVL 3

Expert Comment

by:Kevin Robinson
ID: 24365392
Change the Delimiter to a space then read in the original text file.


 reader.Delimiters = New String() {" "}
0
 

Author Comment

by:cheesebugah
ID: 24365759
VDA,

I greatly appreciate the help.  Are you aware of the haphazard spacing (blank spaces) in the text file?  Do you think the blank spaces are going to cause a problem?

Thanks
0
 
LVL 3

Accepted Solution

by:
Kevin Robinson earned 500 total points
ID: 24366356


Try this code.  seems to work fine regardless of the row layout.

 Public Sub ReadFile()
        Using reader As New TextFieldParser("c:\FBIN.txt")
            reader.TextFieldType = FieldType.Delimited
 
            ' Set the delimiters for comma separated text
            reader.Delimiters = New String() {" "}
 
 
            Dim currentRow As String()
            Dim Windsaloft As String = ""
            Dim StationID As String = ""
            While Not reader.EndOfData
                currentRow = reader.ReadFields()
                If reader.LineNumber > 8 Then
                    Windsaloft = ""
                    Try
                        'Get the Station ID
                        StationID = "K" & currentRow(0).ToString()
 
                        For i As Integer = 1 To currentRow.Length - 1
                            Windsaloft += currentRow(i).ToString()
                        Next
                    Catch ex As Exception
                        MsgBox(ex.Message)
                    End Try
 
                    '''''''''''''''''''''''''''''''''''''''''''''
                    'Add you insert to databse code here
                    '''''''''''''''''''''''''''''''''''''''''''''
                    MsgBox(StationID)
                    MsgBox(Windsaloft)
 
                End If
            End While
        End Using
 
    End Sub

Open in new window

0
 

Author Comment

by:cheesebugah
ID: 24371259
VDA,

I am using Visual Studio 2008 and I do not see the TextFieldParser method in intellisense.  I am using the StreamReader method to do this.  Could you incorporate the StreamReader method somehow?  Here is a routine from a previous assignment that populates the same database from a csv file with the data that is already present in the access DB.  I need to just add the windsaloft data.

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

      Dim stationFile As New StreamReader(_strCsvPath) ' read csv file
      Dim stationRecord As String ' record variable
      Dim arrayFields() As String ' array variable
      Dim strIdent As String ' string ID variable

      ' Constructor for dbUpdt class
      Dim database As New dbUpdt(_strSource)

      ' clear the listbox
      lstStations.Items.Clear()

      ' loop through the database and load the listbox
      Do Until stationFile.EndOfStream
         stationRecord = stationFile.ReadLine
         arrayFields = Split(stationRecord, ",")

         strIdent = arrayFields(0)

         database.AddRecord("stations", "stationID", strIdent)

         database.setDBField("stations", "stationID", strIdent, _
            "stationName", arrayFields(1))

         database.setDBField("stations", "stationID", strIdent, _
            "state", arrayFields(2))

         lstStations.Items.Add(arrayFields(1))

      Loop

      ' close the database
      database.close()
   End Sub ' btnLoadDB_Click

Thanks,
Cheese

0
 

Author Comment

by:cheesebugah
ID: 24381634
VDA,

Okay, this is what I was looking for:

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

      Dim database As New dbUpdt(_strSource)

      ' set the status message
      StatusMsg.Text = "Updating the Database"

      ' Using reader As New Microsoft.VisualBasic.FileIO.TextFieldParser("c:\data\FBIN.txt")
      Dim reader As New StreamReader("c:\data\FBIN.txt")


      Dim currentRow As String()
      Dim currentLine As String
      Dim Windsaloft As String = ""
      Dim StationID As String = ""
      Dim StationIDOriginal As String = ""
      Dim lineNumber As Integer = 0


      While Not reader.EndOfStream
         lineNumber += 1

         currentLine = reader.ReadLine
         currentRow = Split(currentLine, " ")

         If lineNumber > 8 Then
            Windsaloft = ""
            Try
               StationID = "K" & currentRow(0).ToString()
               StationIDOriginal = currentRow(0).ToString()
               For i As Integer = 1 To currentRow.Length - 1
                  Windsaloft += " " & currentRow(i).ToString().Trim()
               Next
            Catch ex As Exception
               MsgBox(ex.ToString())
            End Try

            database.setDBField("stations", "stationID", StationID, "Windsaloft", Windsaloft)

         End If
      End While

      reader.Close()

      database.close()

      btnPopulateComboBoxes.Enabled = True

      StatusMsg.Text = "Ready"

   End Sub ' btnUpdateDatabase_Click

You definitely steered me down the right path.

Thanks,

Cheese
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Message not shown 5 67
Set email body to html using vbscript 6 49
which modules are active in VB6 project? 6 43
VB6 common control 6 sp6 object library not registerd 3 23
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question