Loading database from flat file using ADO.net

[Flugeldorph]

I am new to Visual Basic, my preferred languages is C or C++. I am trying to load a local database with data that I am reading from a delimited flat file. I have created the database tables and added all primary keys and a set of foreign keys. I have also created a Dataset for same. Now I was expecting that I could load the data set from the data that I am parsing from the flat file. Houever when I attempt to enter data into the dataset I can't seem to access an individual field The code line used looks like
ARDataSet.HDDataTable.?? = GetToken(line, Next, '|')
What I wanted to have in place of the ?? is a field, in this case RecType which is a char(2). The GetToken routine is one that I wrote which works fine, it returns a string. I don't know what's missing, or if I am totally barking up the wrong tree.
Should I be using LINQ? and if so, could someone point me in the direction of an example which loads a table from a delimited flat file?

OldieNubie

[sashi]

HI OldieNubie,

Kindly post your code, will try to debug it. Take care.

[Flugeldorph]

Sashi,

Thank you for your reply, Here;s the detail:

The following code is the tokenizer which works.
the first call passes NewItem as True, followed by the string to be tokenized, followed by the delimiter All repeat calls (until the end of string) pass NewItem as false along with two empty strings. As soon as I learn how to overload, I will not require any parameters after the first call, unless a new string is being sent. I also realize that I will have to handle an empty string exception.

   Private Function GetToken(ByVal NewItem As Boolean, ByVal NewString As String, ByVal Delimeter As Char) As String
      Dim builder As New System.Text.StringBuilder
      Static Dim HoldString As String
      Static Dim LastPos As Integer
      Static Dim Delim As Char
      Dim ThisChar As Char
      Const Lf As String = ControlChars.Lf
      Dim Token As String
 
      If NewItem Then
         HoldString = NewString
         Delim = Delimeter
         LastPos = 0
      End If
 
      If (LastPos > 0) Then
         LastPos = LastPos + 1
      End If
 
      ThisChar = HoldString.Substring(LastPos, 1)
      While (ThisChar <> Delim) And (ThisChar <> Lf)
         builder.Append(HoldString.Substring(LastPos, 1))
         LastPos = LastPos + 1
         ThisChar = HoldString.Substring(LastPos, 1)
      End While
 
      If (ThisChar) = Lf Then
         Token = "GoodBye"
      Else
         Token = builder.ToString
      End If
 
      Return Token
   End Function

I did create a structure to check out the tokenizer as follows; this is essentially what the HD table in the DataSet looks like.

   Public Structure HeadRec
      Public RecType As Char()
      Public SysId As Integer
      Public UlsFileNum As Char()
      Public EbfNum As Char()
      Public CallSign As Char()
      Public LicenseStatus As Char
      Public RadioServiceCd As Char()
      Public GrantDt As Date
      Public ExpiredDt As Date
      Public CancellationDt As Date
      Public EligibilityRuleNum As Date
      Public Reserved1 As Char
      Public Alien As Char
      Public AlienGovernment As Char
      Public AlienCorporation As Char
      Public AlienOfficer As Char
      Public AlienControl As Char
      Public Revoked As Char
      Public Convicted As Char
      Public Adjudged As Char
      Public Reserved2 As Char
      Public CommonCarrier As Char
      Public NonCommonCarrier As Char
      Public PrivateComm As Char
      Public Fixed As Char
      Public Mobile As Char
      Public RadioLocation As Char
      Public Satellite As Char
      Public DevelOrSTAOrDemo As Char
      Public InterconnectedService As Char
      Public CertifierFirstName As Char()
      Public CertifierMi As Char
      Public CertifierLastName As Char()
      Public CertifierSuffux As Char()
      Public CertifierTitle As Char()
      Public Female As Char
      Public AfericanAmerican As Char
      Public NativeAmerican As Char
      Public Hawaiian As Char
      Public Asian As Char
      Public White As Char
      Public Hispanic As Char
      Public EffectiveDt As Date
      Public LastActionDt As Date
      Public AuctionId As Integer
      Public BroadcastSrvcRegulatoryStatus As Char
      Public BandManagerRegulatoyStatus As Char
      Public BroadcastSrvcTypeOfRadioSrvc As Char
      Public AlienRuling As Char
      Public LicenseNameChange As Char
   End Structure
 

The following code is where I used the structure (which I wanted to eliminate). I was expecting that I could insert the data directly into the dataset, then remove the structure from the code altogether. What I am receiving is a Readonly error (Commented out ARDataSet.HD.RecTypeColumn = GetToken(True, line, "|")

   Private Sub LoadHDtable()
      Dim ARDataSet As AmateurRadioDataSet
      Dim HeaderRec As HeadRec
      Dim SourcePos As Integer = 0
      Dim DestPos As Integer = 0
      Dim InFileName As String = DestinationDirectory + "HD.dat"
      Dim temp As String = ""
      Dim Token As String
      Dim line As String
      ' Create an instance of StreamReader to read from the file.
      Dim InFile As System.IO.StreamReader = New System.IO.StreamReader(InFileName)
 
      Try
         ' Read and display the lines from the file until the end 
         ' of the file is reached.
         Do
            line = ""
            line = InFile.ReadLine()
            ' AmateurRadioDataSet.HDDataTable. = GetToken(True, line, "|")
            HeaderRec.RecType = GetToken(True, line, "|")
            ' want to replace above with something like
            ' ARDataSet.HD.RecTypeColumn = GetToken(True, line, "|")
            ' and so on for all columns I get a 'Column is readonly error
            Token = GetToken(False, "", "")
            HeaderRec.SysId = System.Convert.ToInt32(Token)
            HeaderRec.UlsFileNum = GetToken(False, "", "")
            HeaderRec.EbfNum = GetToken(False, "", "")
            HeaderRec.CallSign = GetToken(False, "", "")
            HeaderRec.LicenseStatus = GetToken(False, "", "")
            HeaderRec.RadioServiceCd = GetToken(False, "", "")
            HeaderRec.GrantDt = GetToken(False, "", "")
            HeaderRec.ExpiredDt = GetToken(False, "", "")
            HeaderRec.CancellationDt = GetToken(False, "", "")
            HeaderRec.EligibilityRuleNum = GetToken(False, "", "")
            HeaderRec.Reserved1 = GetToken(False, "", "")
            HeaderRec.Alien = GetToken(False, "", "")
            HeaderRec.AlienGovernment = GetToken(False, "", "")
            HeaderRec.AlienCorporation = GetToken(False, "", "")
            HeaderRec.AlienOfficer = GetToken(False, "", "")
            HeaderRec.AlienControl = GetToken(False, "", "")
            HeaderRec.Revoked = GetToken(False, "", "")
            HeaderRec.Convicted = GetToken(False, "", "")
            HeaderRec.Adjudged = GetToken(False, "", "")
            HeaderRec.Reserved2 = GetToken(False, "", "")
            HeaderRec.CommonCarrier = GetToken(False, "", "")
            HeaderRec.NonCommonCarrier = GetToken(False, "", "")
            HeaderRec.PrivateComm = GetToken(False, "", "")
            HeaderRec.Fixed = GetToken(False, "", "")
            HeaderRec.Mobile = GetToken(False, "", "")
            HeaderRec.RadioLocation = GetToken(False, "", "")
            HeaderRec.Satellite = GetToken(False, "", "")
            HeaderRec.DevelOrSTAOrDemo = GetToken(False, "", "")
            HeaderRec.InterconnectedService = GetToken(False, "", "")
            HeaderRec.CertifierFirstName = GetToken(False, "", "")
            HeaderRec.CertifierMi = GetToken(False, "", "")
            HeaderRec.CertifierLastName = GetToken(False, "", "")
            HeaderRec.CertifierSuffux = GetToken(False, "", "")
            HeaderRec.CertifierTitle = GetToken(False, "", "")
            HeaderRec.Female = GetToken(False, "", "")
            HeaderRec.AfericanAmerican = GetToken(False, "", "")
            HeaderRec.NativeAmerican = GetToken(False, "", "")
            HeaderRec.Hawaiian = GetToken(False, "", "")
            HeaderRec.Asian = GetToken(False, "", "")
            HeaderRec.White = GetToken(False, "", "")
            HeaderRec.Hispanic = GetToken(False, "", "")
            HeaderRec.EffectiveDt = GetToken(False, "", "")
            HeaderRec.LastActionDt = GetToken(False, "", "")
            HeaderRec.AuctionId = GetToken(False, "", "")
            HeaderRec.BroadcastSrvcRegulatoryStatus = GetToken(False, "", "")
            HeaderRec.BandManagerRegulatoyStatus = GetToken(False, "", "")
            HeaderRec.BroadcastSrvcTypeOfRadioSrvc = GetToken(False, "", "")
            HeaderRec.AlienRuling = GetToken(False, "", "")
            HeaderRec.LicenseNameChange = GetToken(False, "", "")
            ' To do Commit dataset into database table
         Loop Until line Is Nothing
         InFile.Close()
      Catch ex As Exception
         ' Let the user know what went wrong.
         Console.WriteLine("The file could not be read:")
         Console.WriteLine(ex.Message)
      End Try
   End Sub
 

If you need to see the database layout, I'll have to figure out which .xml file it's in and post that there are many tables.

Thanks in advance

Larry

[Flugeldorph]

Sashi,

If you need a copy of the the data that I am using, you can download a copy with the following: The HD file is the one used in my example code

   Dim CurrentDirectory As String = Application.StartupPath() + "\"
   Dim DestinationDirectory As String = CurrentDirectory + "DataFiles\"
   Dim FileName As String = "l_amat.zip"
   Private Sub GetFCCDataFile()
      Dim remoteUri As String = "http://wireless.fcc.gov/uls/data/complete/"
      Dim Downloadfile As String = remoteUri + FileName
      Dim myWebClient As New System.Net.WebClient()
 
      Try
         ' Determine whether the directory already exists.
         ' Create the directory.if it's not there
         System.IO.Directory.CreateDirectory(DestinationDirectory)
         ' Move to data directory
         System.IO.Directory.SetCurrentDirectory(DestinationDirectory)
         ' Download the file
         myWebClient.DownloadFile(Downloadfile, FileName)
         ' Restore original directory
         System.IO.Directory.SetCurrentDirectory(CurrentDirectory)
      Catch ex As Exception
         Console.WriteLine("Unable to download file: {0}", ex.ToString())
      End Try
   End Sub

Larry