469,579 Members | 1,051 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Loading database from flat file using ADO.net

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
Oct 1 '08 #1
3 2935
sashi
1,754 Expert 1GB
HI OldieNubie,

Kindly post your code, will try to debug it. Take care.
Oct 1 '08 #2
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.

Expand|Select|Wrap|Line Numbers
  1.    Private Function GetToken(ByVal NewItem As Boolean, ByVal NewString As String, ByVal Delimeter As Char) As String
  2.       Dim builder As New System.Text.StringBuilder
  3.       Static Dim HoldString As String
  4.       Static Dim LastPos As Integer
  5.       Static Dim Delim As Char
  6.       Dim ThisChar As Char
  7.       Const Lf As String = ControlChars.Lf
  8.       Dim Token As String
  9.  
  10.       If NewItem Then
  11.          HoldString = NewString
  12.          Delim = Delimeter
  13.          LastPos = 0
  14.       End If
  15.  
  16.       If (LastPos > 0) Then
  17.          LastPos = LastPos + 1
  18.       End If
  19.  
  20.       ThisChar = HoldString.Substring(LastPos, 1)
  21.       While (ThisChar <> Delim) And (ThisChar <> Lf)
  22.          builder.Append(HoldString.Substring(LastPos, 1))
  23.          LastPos = LastPos + 1
  24.          ThisChar = HoldString.Substring(LastPos, 1)
  25.       End While
  26.  
  27.       If (ThisChar) = Lf Then
  28.          Token = "GoodBye"
  29.       Else
  30.          Token = builder.ToString
  31.       End If
  32.  
  33.       Return Token
  34.    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.

Expand|Select|Wrap|Line Numbers
  1.    Public Structure HeadRec
  2.       Public RecType As Char()
  3.       Public SysId As Integer
  4.       Public UlsFileNum As Char()
  5.       Public EbfNum As Char()
  6.       Public CallSign As Char()
  7.       Public LicenseStatus As Char
  8.       Public RadioServiceCd As Char()
  9.       Public GrantDt As Date
  10.       Public ExpiredDt As Date
  11.       Public CancellationDt As Date
  12.       Public EligibilityRuleNum As Date
  13.       Public Reserved1 As Char
  14.       Public Alien As Char
  15.       Public AlienGovernment As Char
  16.       Public AlienCorporation As Char
  17.       Public AlienOfficer As Char
  18.       Public AlienControl As Char
  19.       Public Revoked As Char
  20.       Public Convicted As Char
  21.       Public Adjudged As Char
  22.       Public Reserved2 As Char
  23.       Public CommonCarrier As Char
  24.       Public NonCommonCarrier As Char
  25.       Public PrivateComm As Char
  26.       Public Fixed As Char
  27.       Public Mobile As Char
  28.       Public RadioLocation As Char
  29.       Public Satellite As Char
  30.       Public DevelOrSTAOrDemo As Char
  31.       Public InterconnectedService As Char
  32.       Public CertifierFirstName As Char()
  33.       Public CertifierMi As Char
  34.       Public CertifierLastName As Char()
  35.       Public CertifierSuffux As Char()
  36.       Public CertifierTitle As Char()
  37.       Public Female As Char
  38.       Public AfericanAmerican As Char
  39.       Public NativeAmerican As Char
  40.       Public Hawaiian As Char
  41.       Public Asian As Char
  42.       Public White As Char
  43.       Public Hispanic As Char
  44.       Public EffectiveDt As Date
  45.       Public LastActionDt As Date
  46.       Public AuctionId As Integer
  47.       Public BroadcastSrvcRegulatoryStatus As Char
  48.       Public BandManagerRegulatoyStatus As Char
  49.       Public BroadcastSrvcTypeOfRadioSrvc As Char
  50.       Public AlienRuling As Char
  51.       Public LicenseNameChange As Char
  52.    End Structure
  53.  
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, "|")

Expand|Select|Wrap|Line Numbers
  1.    Private Sub LoadHDtable()
  2.       Dim ARDataSet As AmateurRadioDataSet
  3.       Dim HeaderRec As HeadRec
  4.       Dim SourcePos As Integer = 0
  5.       Dim DestPos As Integer = 0
  6.       Dim InFileName As String = DestinationDirectory + "HD.dat"
  7.       Dim temp As String = ""
  8.       Dim Token As String
  9.       Dim line As String
  10.       ' Create an instance of StreamReader to read from the file.
  11.       Dim InFile As System.IO.StreamReader = New System.IO.StreamReader(InFileName)
  12.  
  13.       Try
  14.          ' Read and display the lines from the file until the end 
  15.          ' of the file is reached.
  16.          Do
  17.             line = ""
  18.             line = InFile.ReadLine()
  19.             ' AmateurRadioDataSet.HDDataTable. = GetToken(True, line, "|")
  20.             HeaderRec.RecType = GetToken(True, line, "|")
  21.             ' want to replace above with something like
  22.             ' ARDataSet.HD.RecTypeColumn = GetToken(True, line, "|")
  23.             ' and so on for all columns I get a 'Column is readonly error
  24.             Token = GetToken(False, "", "")
  25.             HeaderRec.SysId = System.Convert.ToInt32(Token)
  26.             HeaderRec.UlsFileNum = GetToken(False, "", "")
  27.             HeaderRec.EbfNum = GetToken(False, "", "")
  28.             HeaderRec.CallSign = GetToken(False, "", "")
  29.             HeaderRec.LicenseStatus = GetToken(False, "", "")
  30.             HeaderRec.RadioServiceCd = GetToken(False, "", "")
  31.             HeaderRec.GrantDt = GetToken(False, "", "")
  32.             HeaderRec.ExpiredDt = GetToken(False, "", "")
  33.             HeaderRec.CancellationDt = GetToken(False, "", "")
  34.             HeaderRec.EligibilityRuleNum = GetToken(False, "", "")
  35.             HeaderRec.Reserved1 = GetToken(False, "", "")
  36.             HeaderRec.Alien = GetToken(False, "", "")
  37.             HeaderRec.AlienGovernment = GetToken(False, "", "")
  38.             HeaderRec.AlienCorporation = GetToken(False, "", "")
  39.             HeaderRec.AlienOfficer = GetToken(False, "", "")
  40.             HeaderRec.AlienControl = GetToken(False, "", "")
  41.             HeaderRec.Revoked = GetToken(False, "", "")
  42.             HeaderRec.Convicted = GetToken(False, "", "")
  43.             HeaderRec.Adjudged = GetToken(False, "", "")
  44.             HeaderRec.Reserved2 = GetToken(False, "", "")
  45.             HeaderRec.CommonCarrier = GetToken(False, "", "")
  46.             HeaderRec.NonCommonCarrier = GetToken(False, "", "")
  47.             HeaderRec.PrivateComm = GetToken(False, "", "")
  48.             HeaderRec.Fixed = GetToken(False, "", "")
  49.             HeaderRec.Mobile = GetToken(False, "", "")
  50.             HeaderRec.RadioLocation = GetToken(False, "", "")
  51.             HeaderRec.Satellite = GetToken(False, "", "")
  52.             HeaderRec.DevelOrSTAOrDemo = GetToken(False, "", "")
  53.             HeaderRec.InterconnectedService = GetToken(False, "", "")
  54.             HeaderRec.CertifierFirstName = GetToken(False, "", "")
  55.             HeaderRec.CertifierMi = GetToken(False, "", "")
  56.             HeaderRec.CertifierLastName = GetToken(False, "", "")
  57.             HeaderRec.CertifierSuffux = GetToken(False, "", "")
  58.             HeaderRec.CertifierTitle = GetToken(False, "", "")
  59.             HeaderRec.Female = GetToken(False, "", "")
  60.             HeaderRec.AfericanAmerican = GetToken(False, "", "")
  61.             HeaderRec.NativeAmerican = GetToken(False, "", "")
  62.             HeaderRec.Hawaiian = GetToken(False, "", "")
  63.             HeaderRec.Asian = GetToken(False, "", "")
  64.             HeaderRec.White = GetToken(False, "", "")
  65.             HeaderRec.Hispanic = GetToken(False, "", "")
  66.             HeaderRec.EffectiveDt = GetToken(False, "", "")
  67.             HeaderRec.LastActionDt = GetToken(False, "", "")
  68.             HeaderRec.AuctionId = GetToken(False, "", "")
  69.             HeaderRec.BroadcastSrvcRegulatoryStatus = GetToken(False, "", "")
  70.             HeaderRec.BandManagerRegulatoyStatus = GetToken(False, "", "")
  71.             HeaderRec.BroadcastSrvcTypeOfRadioSrvc = GetToken(False, "", "")
  72.             HeaderRec.AlienRuling = GetToken(False, "", "")
  73.             HeaderRec.LicenseNameChange = GetToken(False, "", "")
  74.             ' To do Commit dataset into database table
  75.          Loop Until line Is Nothing
  76.          InFile.Close()
  77.       Catch ex As Exception
  78.          ' Let the user know what went wrong.
  79.          Console.WriteLine("The file could not be read:")
  80.          Console.WriteLine(ex.Message)
  81.       End Try
  82.    End Sub
  83.  
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
Oct 1 '08 #3
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

Expand|Select|Wrap|Line Numbers
  1.    Dim CurrentDirectory As String = Application.StartupPath() + "\"
  2.    Dim DestinationDirectory As String = CurrentDirectory + "DataFiles\"
  3.    Dim FileName As String = "l_amat.zip"
  4.    Private Sub GetFCCDataFile()
  5.       Dim remoteUri As String = "http://wireless.fcc.gov/uls/data/complete/"
  6.       Dim Downloadfile As String = remoteUri + FileName
  7.       Dim myWebClient As New System.Net.WebClient()
  8.  
  9.       Try
  10.          ' Determine whether the directory already exists.
  11.          ' Create the directory.if it's not there
  12.          System.IO.Directory.CreateDirectory(DestinationDirectory)
  13.          ' Move to data directory
  14.          System.IO.Directory.SetCurrentDirectory(DestinationDirectory)
  15.          ' Download the file
  16.          myWebClient.DownloadFile(Downloadfile, FileName)
  17.          ' Restore original directory
  18.          System.IO.Directory.SetCurrentDirectory(CurrentDirectory)
  19.       Catch ex As Exception
  20.          Console.WriteLine("Unable to download file: {0}", ex.ToString())
  21.       End Try
  22.    End Sub
Larry
Oct 1 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by siliconmike | last post: by
3 posts views Thread by amorphous999 | last post: by
5 posts views Thread by Paul M. | last post: by
10 posts views Thread by ircmaxell | last post: by
1 post views Thread by Fordraiders | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.