473,554 Members | 2,100 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Loading database from flat file using ADO.net

11 New Member
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.HDDat aTable.?? = 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?

Oct 1 '08 #1
3 3158
1,754 Recognized Expert Top Contributor
HI OldieNubie,

Kindly post your code, will try to debug it. Take care.
Oct 1 '08 #2
11 New Member

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
  10.       If NewItem Then
  11.          HoldString = NewString
  12.          Delim = Delimeter
  13.          LastPos = 0
  14.       End If
  16.       If (LastPos > 0) Then
  17.          LastPos = LastPos + 1
  18.       End If
  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
  27.       If (ThisChar) = Lf Then
  28.          Token = "GoodBye"
  29.       Else
  30.          Token = builder.ToString
  31.       End If
  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
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.Re cTypeColumn = 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)
  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
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

Oct 1 '08 #3
11 New Member

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()
  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
Oct 1 '08 #4

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

Similar topics

by: siliconmike | last post by:
How do I synchronize MySQL table data of my home PC with latest data from a remote server ? My home PC is on a very slow internet connection, so implementing replication will cause long time read lock on remote server, which is not desirable. What are the tools for manual sync of tables ? I'm running FreeBSD on my home PC.
by: amorphous999 | last post by:
I want to use DTS to load from a flat file. But I can't be sure when the flat file will arrive. I would like the job to check every 10 minutes or so for a couple of hours. If the file is present, then load the file and rename it so that it doesn't get overwritten. Any suggestions? TIA, amorphous999
by: Jonathan Buckland | last post by:
Can someone give me an example how to append data without having to load the complete XML file. Is this possible? Jonathan
by: Paul M. | last post by:
Hi, Are there any examples of building a simple local database with C# Express 2005? TIA
by: vunet.us | last post by:
Hi, I would like to use flat file data storage instead of database. Since I am new to it, I am wondering: What text file extension is a safe one to store my data online and how cost- and time-effective is this method (flat file data storage). Anyone can share thoughts? Thanks
by: ircmaxell | last post by:
Ok, I have a program that reads the contents of a file (1 line, 5 '|' seperated items). Every so often (between twice a day, and 200 times a day), it rewrites the contents of that file. I also do a few database update queries whenever the file is written. I only open up the database connector if I need to update the database. The script...
by: Fordraiders | last post by:
vb.net 2003 Office 2003 What I have: C:\TestData\Input.txt Text File Pipe Delimited : 4 columns Of data example: 00001|NO BRAND NAME ASSIGNED|6DU27|M3-.5 X 6 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL 00002|NO BRAND NAME ASSIGNED|6DU28|M3-.5 X 8 FLAT HD SOCKET CAP SCREW,
by: felciano | last post by:
Hello -- Is there a convention, library or Pythonic idiom for performing lightweight relational operations on flatfiles? I frequently find myself writing code to do simple SQL-like operations between flat files, such as appending columns from one file to another, linked through a common id. For example, take a list of addresses and append...
by: techquest | last post by:
Hi, I want to connect into oracle database and export the table data into a flat file using UNIX shell scripts. I cant use other GUI tools to do this, as the dataload will be in millions. hence if i try to export from toad/sqlplus its hanging. And also i need to some validation using UNIX. So please give me some tip to write a script which does...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.