11 Replies - 1329 Views - Last Post: 09 August 2013 - 10:53 AM Rate Topic: -----

#1 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 08:45 AM

I normally don't have problems getting data from a VB form to populate my SQL Server database, but I can't figure this one out. I had help from this forum to read the data file and break up the data into rows; however, there are going to be about 800 rows of data per file. I need help getting the code right to dynamically upload the data to the database without having to write code for 800 or so rows. Any ideas are welcome.

I have attached my sample data file. And here is my code to read the file in case seeing the output will help.

 Dim inlines(-1) As String
        Dim antData() As String

        If File.Exists("SampleDataSet.txt") Then
            ' here, we read in the entire file all at once, and
            '  split it into individual lines in an array. Notice how we just tack
            '   on the Split to the end of the function call to ReadAllLines.
            inlines = File.ReadAllText("SampleDataSet.txt").Split(CChar(vbNewLine))
        Else
            MessageBox.Show("File SampleDataSet.txt not found in " & Application.StartupPath)
        End If

        lines.AddRange(inlines) 'Now we add all the lines in the array to the List.


        ' This Do Loop will handle each antenna data set
        Do Until lines.Count < 5
            ' Get rid of all the initial lines by removing lines until
            '  one of tem contains "Degree"
            Do Until lines(0).Contains("Notes:")
                lines.RemoveAt(0)
            Loop

            lines(0) = lines(0).Replace(vbLf, "")
            antData = lines(0).Split(CChar(vbTab))

            GetAntennaHeader(antData)  'do something with the current line (specifies Antenna?)

            lines.RemoveAt(0)
            lines.RemoveAt(0)
            ' this Do loop will split each data line using
            '   the Tab character as a delimiter
            Do Until lines(0).Contains("Date:") Or lines.Count < 2
                lines(0) = lines(0).Replace(vbLf, "")
                If lines(0).Contains("980   18.266  -57.318") Then
                    Exit Do
                End If
                antData = lines(0).Split(CChar(vbTab))

                If Not antData(0) = "" Then
                    GetAntennaData(antData)
                End If
                lines.RemoveAt(0)
            Loop
        Loop

    End Sub

    Private Sub GetAntennaHeader(ByVal hdrData() As String)

        'Loop to pull out Degree and Antenna values
        For i = 0 To hdrData.Count - 1
            Debug.Print(hdrData(i))
        Next    


    End Sub

    Private Sub GetAntennaData(ByVal aData() As String)
        'Loop to pull out row values
        Debug.Print("---------------")
        For i = 0 To 9
            If Not aData(i) = "" Then
                Debug.Print(i.ToString & "  " & aData(i))
            End If
        Next

    End Sub


Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: Trouble getting read data from a file to insert into MS SQL database

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14764
  • View blog
  • Posts: 59,030
  • Joined: 12-June 08

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 09:01 AM

why not just read each line.. break it into the required columns, and use a typical sql INSERT statement to add the values? It's a pretty quick and short loop.. read the lines until the file ends.
Was This Post Helpful? 0
  • +
  • -

#3 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 12:21 PM

How do I do that? My VB knowledge is basic. I had A LOT of help with the code posted above (thanks Lar3ry!).
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14764
  • View blog
  • Posts: 59,030
  • Joined: 12-June 08

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 01:11 PM

... do what?

Read in a text file? Use the String namespace's .split? Making an insert call to the database?


The first two can be illustrated here:

        Dim oStreamReaders As New StreamReader("C:\test\20130808.txt") '-- a stream reader to your file

        Dim sTemp As String = String.Empty '-- holds the entire line you read in.

        Dim sTempCellsOfData() As String '-- holds the broken up string in the individual cells of the "row".

        Try
            Do
                sTemp = oStreamReaders.ReadLine '-- read in a line.
                If sTemp IsNot Nothing Then '-- if not nothing (nothing is the end of the file)
                    sTempCellsOfData = sTemp.Split(",") '-- split the line by the commas (because commas are what I used to deliniate columns of data.

                    '-- print them out to show we have individual rows.
                    For Each foo As String In sTempCellsOfData
                        Console.WriteLine(foo)
                    Next
                    Console.WriteLine(" ----------------------- ")
                    '-- call insert into DB here with array of strings (aka sTempCellsOfData)
                End If
            Loop While sTemp <> Nothing '-- do until the end of the file.

        Catch ex As Exception
            Console.WriteLine(ex.Message)

        End Try



data
100,a,9.99
101,b,1.91
202,c,100.15


Database interactions can be found in the tutorial section.

Examples:

OleDb Basics in VB.Net

A Really Simple Database Create a Database using Access & VB.net Express 2008
Was This Post Helpful? 1
  • +
  • -

#5 lar3ry   User is offline

  • Coding Geezer
  • member icon

Reputation: 314
  • View blog
  • Posts: 1,296
  • Joined: 12-September 12

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 06:16 PM

View Posttwins2me, on 08 August 2013 - 09:45 AM, said:

I normally don't have problems getting data from a VB form to populate my SQL Server database, but I can't figure this one out. I had help from this forum to read the data file and break up the data into rows; however, there are going to be about 800 rows of data per file. I need help getting the code right to dynamically upload the data to the database without having to write code for 800 or so rows. Any ideas are welcome.

Well, with the above code, you already have the rows in memory. 800 rows is not a big thing at all, and just about any machine (and your program) will be able to handle that. As for writing code for 800 rows, you really want to insert your data in a loop that will deal with logical chunks of data, repeating it for each data set.

As you go through the raw data from the file, you are first getting the "header". By that, I mean the information on which antenna it is, the Angle, etc. Currently, in the subroutine where you deal with that, you are only printing it out. You need to change that action to do whatever you want to do with the info. You can store all or part of the split-out data into one or more variables, to be used when gather up all the data rows for that antenna.

Then, you call the subroutine to get the antenna data for each frequency. Again, you can store this data for use when it's all been gathered for that antenna. You could use a 2-dimensional array, or a ListBox, etc., but the important thing is to gather it all up. Once you have gathered all the data for one antenna, you can insert it into the database (or perhaps you can do it line by line). Again, I don't do database stuff, so I'm guessing here.
Was This Post Helpful? 1
  • +
  • -

#6 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 06:45 PM

I see what you are saying and pasted your code into my tester application and looked at what the output looks like with my file. I like it; however, there are lines in my file that I need to remove and just pull the chunks of data out for insertion into my database. The code I have does that. I'm working with your code to see how I can change the code I posted earlier to accomplish this. I know how to read a file and write to a file, but we weren't taught how to pull certain parts of info from the file. My VB class was very basic and most of what I know has been self taught and spending hours reading tutorials and forums along with trial and error. My database class was more intensive than my VB class and I am more confident in my skills in that area. I can write the code to interact with my database quite easily, but I'm at a loss at how to do this with my many arrays that my code splits my data into.

As always, I am truly thankful for the help and direction I receive from everyone.
Was This Post Helpful? 0
  • +
  • -

#7 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 07:03 PM

I think I understand what you were saying Lar3ry. I will work on this tonight and post what I have tomorrow to either say it works, or I'm still working on it, or I'm stuck. I'm going to try and get my antenna header data to populate listboxes (one for each antenna number) and the header info to populate textboxes associated with each listbox. I know once I am able to do that part I can then write the SQL adapter and command codes to insert the data into the appropriate table(s) in my database (for me that is the easy part). :smile2:
Was This Post Helpful? 0
  • +
  • -

#8 lar3ry   User is offline

  • Coding Geezer
  • member icon

Reputation: 314
  • View blog
  • Posts: 1,296
  • Joined: 12-September 12

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 08 August 2013 - 08:15 PM

View Posttwins2me, on 08 August 2013 - 08:03 PM, said:

we weren't taught how to pull certain parts of info from the file

The code above does pull certain parts of the file out. It pulls out and separates the line that starts with with "Notes:", and sends the array containing it to GetAntennaHeader(). Then it pulls out and separates each line containing the data for each frequency, and sends them (one at a time) to GetAntennaData(). Are there any other lines that need to be kept? The various other lines are simply discarded.

Quote

I'm going to try and get my antenna header data to populate listboxes (one for each antenna number) and the header info to populate textboxes associated with each listbox. I know once I am able to do that part I can then write the SQL adapter and command codes to insert the data into the appropriate table(s) in my database (for me that is the easy part). :smile2:/>/>

OK. For each data set, you will call GetAntennaHeader() once. If you need to put all or part of that data into a TextBox, you can do it in this Subroutine.

For each data set, you will call GetAntennaData() once for each Frequency. If you need to put all or part of this data into a ListBox, you can do it in this Subroutine.

Assuming you put what you need into the TextBox and ListBox for one data set, you should be able to insert that data into the database. A good place to do this is at the end of the loop that extracts the data. In the code above, That would be between lines 44 and 45. The best way to handle that is to call another Subroutine called something like InsertDataSet(). In that routine, you can insert the TextBox and ListBox contents into your database.

Oh, and I just noticed a bit of extra code that should not be there. It was in there as part of a test. You can remove lines 35, 36, and 37.

This post has been edited by lar3ry: 08 August 2013 - 08:18 PM

Was This Post Helpful? 1
  • +
  • -

#9 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 09 August 2013 - 09:12 AM

I have groupboxes set up for each antenna. In those group boxes, I have a text box for degree, angle, and antenna along with a listbox for the data values. I am able to get the header info to populate the correct textbox. I even went and changed some of the header values to make sure I was getting them to populate correctly and it works. :) I'm still stuck on getting the getantennadata to split into their separate listboxes. I'm still working on it and will keep at it til I get it figured out.
Was This Post Helpful? 0
  • +
  • -

#10 lar3ry   User is offline

  • Coding Geezer
  • member icon

Reputation: 314
  • View blog
  • Posts: 1,296
  • Joined: 12-September 12

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 09 August 2013 - 09:52 AM

View Posttwins2me, on 09 August 2013 - 10:12 AM, said:

I'm still stuck on getting the getantennadata to split into their separate listboxes. I'm still working on it and will keep at it til I get it figured out.

You do realize that when GetAntennaData() is called, you have an array with the values already split into individual entries, right? antData is passed to GetAntennaData(), and within GetAntennaData(), the name of the aray is adata. So the entries are as follows:

adata(0) is Frequency
adata(1) is Transmitted power
adata(2) is Backscattered power
adata(3) is Backscattered signal phase
adata(4) is Electric field strength
adata(5) is DeltaRCS
adata(6) is Power on tag forward
adata(7) is Power on tag reverse
adata(8) is Theoretical read range forward
adata(9) is Theroretical read range reverse

Another point is worth mentioning. If you are just interested in getting the data sets into the database, you don't need to use Separate TextBoxes and ListBoxes for each antenna. You can reuse one set of them, clearing them betweem data sets.

This post has been edited by lar3ry: 09 August 2013 - 09:54 AM

Was This Post Helpful? 0
  • +
  • -

#11 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 09 August 2013 - 10:29 AM

My list boxes are Ant1ListBox, Ant2ListBox, Ant3ListBox, and Ant4ListBox. I can get one listbox to populate using:

Ant1ListBox.Items.Add(aData(0)) 'or what ever array entry I choose



How do I get just antenna 1 entries to show up?
Was This Post Helpful? 0
  • +
  • -

#12 twins2me   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-July 13

Re: Trouble getting read data from a file to insert into MS SQL database

Posted 09 August 2013 - 10:53 AM

I think I have it! I left the print lines in so I can see them on the output and will remove them when I am finished working on this form. But I now have the correct headers and data separated into the appropriate fields in the different antenna group boxes.

 Private Sub GetAntennaData(ByVal aData() As String)
        'Loop to pull out row values
        Debug.Print("---------------")
        For i = 0 To 9
            If Not aData(i) = "" Then
                Debug.Print(i.ToString & "  " & aData(i))
            End If
        Next

        If Ant1TextBox.Text = "1" Then
            Ant1ListBox.Items.Add(aData(0))
            Ant1ListBox.Items.Add(aData(1))
            Ant1ListBox.Items.Add(aData(2))
            Ant1ListBox.Items.Add(aData(3))
            Ant1ListBox.Items.Add(aData(4))
            Ant1ListBox.Items.Add(aData(5))
            Ant1ListBox.Items.Add(aData(6))
            Ant1ListBox.Items.Add(aData(7))
            Ant1ListBox.Items.Add(aData(8))
            Ant1ListBox.Items.Add(aData(9))
        End If

        If Ant2TextBox.Text = "2" Then
            Ant2ListBox.Items.Add(aData(0))
            Ant2ListBox.Items.Add(aData(1))
            Ant2ListBox.Items.Add(aData(2))
            Ant2ListBox.Items.Add(aData(3))
            Ant2ListBox.Items.Add(aData(4))
            Ant2ListBox.Items.Add(aData(5))
            Ant2ListBox.Items.Add(aData(6))
            Ant2ListBox.Items.Add(aData(7))
            Ant2ListBox.Items.Add(aData(8))
            Ant2ListBox.Items.Add(aData(9))
        End If

        If Ant3TextBox.Text = "3" Then
            Ant3ListBox.Items.Add(aData(0))
            Ant3ListBox.Items.Add(aData(1))
            Ant3ListBox.Items.Add(aData(2))
            Ant3ListBox.Items.Add(aData(3))
            Ant3ListBox.Items.Add(aData(4))
            Ant3ListBox.Items.Add(aData(5))
            Ant3ListBox.Items.Add(aData(6))
            Ant3ListBox.Items.Add(aData(7))
            Ant3ListBox.Items.Add(aData(8))
            Ant3ListBox.Items.Add(aData(9))
        End If

        If Ant4TextBox.Text = "4" Then
            Ant4ListBox.Items.Add(aData(0))
            Ant4ListBox.Items.Add(aData(1))
            Ant4ListBox.Items.Add(aData(2))
            Ant4ListBox.Items.Add(aData(3))
            Ant4ListBox.Items.Add(aData(4))
            Ant4ListBox.Items.Add(aData(5))
            Ant4ListBox.Items.Add(aData(6))
            Ant4ListBox.Items.Add(aData(7))
            Ant4ListBox.Items.Add(aData(8))
            Ant4ListBox.Items.Add(aData(9))
        End If

    End Sub


This post has been edited by twins2me: 09 August 2013 - 10:55 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1