cheesebugah
asked on
Import from text file into database via vb.net
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
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
ASKER
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
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
Can you post the file
ASKER
VDA,
I did! The file to import from (FBIN.txt and the access DB (weather.mdb).
Thanks,
Cheese
I did! The file to import from (FBIN.txt and the access DB (weather.mdb).
Thanks,
Cheese
Open the text file in Excel then save it as a CSV.
Give me a moment and Ill try it this end and send you some code.
ASKER
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
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
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
ASKER
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
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
Change the Delimiter to a space then read in the original text file.
reader.Delimiters = New String() {" "}
reader.Delimiters = New String() {" "}
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("statio ns", "stationID", strIdent)
database.setDBField("stati ons", "stationID", strIdent, _
"stationName", arrayFields(1))
database.setDBField("stati ons", "stationID", strIdent, _
"state", arrayFields(2))
lstStations.Items.Add(arra yFields(1) )
Loop
' close the database
database.close()
End Sub ' btnLoadDB_Click
Thanks,
Cheese
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("statio
database.setDBField("stati
"stationName", arrayFields(1))
database.setDBField("stati
"state", arrayFields(2))
lstStations.Items.Add(arra
Loop
' close the database
database.close()
End Sub ' btnLoadDB_Click
Thanks,
Cheese
ASKER
VDA,
Okay, this is what I was looking for:
Private Sub btnUpdateDatabase_Click(By Val 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.File IO.TextFie ldParser(" c:\data\FB IN.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().T rim()
Next
Catch ex As Exception
MsgBox(ex.ToString())
End Try
database.setDBField("stati ons", "stationID", StationID, "Windsaloft", Windsaloft)
End If
End While
reader.Close()
database.close()
btnPopulateComboBoxes.Enab led = True
StatusMsg.Text = "Ready"
End Sub ' btnUpdateDatabase_Click
You definitely steered me down the right path.
Thanks,
Cheese
Okay, this is what I was looking for:
Private Sub btnUpdateDatabase_Click(By
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.File
Dim reader As New StreamReader("c:\data\FBIN
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().T
Next
Catch ex As Exception
MsgBox(ex.ToString())
End Try
database.setDBField("stati
End If
End While
reader.Close()
database.close()
btnPopulateComboBoxes.Enab
StatusMsg.Text = "Ready"
End Sub ' btnUpdateDatabase_Click
You definitely steered me down the right path.
Thanks,
Cheese
Open in new window