none
Text File into Database RRS feed

  • Question

  • Hello,
    I have a text file generated by a php FormMail web inquiry.  Roughly a thousand inquires seperated by Asteriks (see sample below.  Categories shown in caps below for clarity, but not in caps in the actual file.)  Would like to create a database or spreadsheet of all the inquiries.  Ideally want to be able to sort/display the whole list by any of the categories (siteOwner, siteLocation, etc), and group similiar records (all inquiries from same state, houseBudget, etc.). 

    Is there a way to do this simply with Microsoft Access? or VB?
    Any help would be greatly, greatly appreciated.
    Regards,
    Oskkar


    EXAMPLE INQUIRY FROM TEXT FILE-
    *******************************************************************************
    siteOwner:            Yes
    siteLocation_street1: 1763 Maopa Highway
    siteLocation_street2:
    siteLocation_city:    Ojai
    siteLocation_state:   CA
    siteLocation_county:  usa
    siteSize:             14 acres
    houseBudget:          up to $200,000
    houseMortgage:        0
    providingContractor:  No
    myBudget:            
    myName:               stee laruew
    myMail:               steelaruew@yahoo.com
    myMailconfirm:        steelaruew@yahoo.com
    myPhone:              805785172
    Date:                 13-11-2006

    siteConditions:

    had a 1500 sq. ft home burned down was manufactured home.  Electricity is there...   I am trying to get it approved for a building site using existing sq. footage.   I may have to limit it to 1500 sq. feet or maybe use your 1000 foot module. 

    houseUsage:

    As a home.

    houseOccupants:

    Me

    houseSchedule:

    hopefully within a year.

    myQuikHouseQuestions:

    Can you email me plans or pictures of the 1000 foot one?  I would prefer the larger one but might not be able to use that.
    *******************************************************************************
    Tuesday, September 2, 2008 8:36 PM

Answers

  • Like people have discussed the problem is mainly a text parsing issue. After you have things parsed out you can either use a datatable, create a dataRow and then call update. If you didn't want to use dataset you could simply create sql commands to insert data into a database. In order to do this you would open a connection, then create a sql command, then execute it.

     

    If have problems inserting data into the database using dataset please provide the specifics. If the inserts are not working correctly I would advise you to print out all the values to the console or MessageBox the string, then add to a DataTable and call update on the tableAdapter.

     

    Thanks

    Chris Robinson

    Software Developer in Test - DataSet

    Friday, September 5, 2008 9:15 PM

All replies

  • I do not think there is simple way to do this, but what you need is to create some sort of custom text parser that would split file content into separate inquires and then will parse each inquiry into separate values that could be stored into database. To split content you could use regular expressions class from .NET. Is it fixed structure for each inquiry in your case?

     

    Wednesday, September 3, 2008 9:52 AM
    Moderator
  • Thank you for your response VMazur.  The inquiries are fixed in terms of the categories, but people's responses vary especially towards the end of each inquiry where respones range from 2 or 3 words to 2 or 3 paragraphs. 
    Thanks again,
    Oskkar
    Wednesday, September 3, 2008 2:24 PM
  • I recommend switching the file to XML. Not only will that simplify the parsing, it will also allow you to use XPath to navigate through the file.

    Example:
    Code Snippet

    <siteOwner>Yes</siteOwner>

    <siteLocation>

    <street1> 1763 Maopa highway</street1>

    <street2></street2>

    <city>Ojai</city>

    <state>CA</state>

    <county>USA</country>

    </siteLocation>


    What is that file being generated by ? You could possibly be able use .Net XML serialization to easily generate the XML output I described above.

    Using XPath would then allow you to easily fetch pieces of data and manipulate them as needed.

    Oh also, obviously this takes care of multi-line responses, since the response is contained between an opening and closing tag.
    Wednesday, September 3, 2008 9:18 PM
  • Thanks for your thoughts Ventsislav.  The txt file was created by a php FormMail html page.  Your XML idea sounds like the right move if we were starting from scratch, but we already have the text file with responses (about 1000).  Need to find a solution that will let us sort/rearange the existing info in its current state (not that we are apposed to changing anything, but we are amatuers here and are hoping for a "less painful" solution.
    Wednesday, September 3, 2008 9:39 PM
  • I see. Why not try something along the lines of :

    Code Snippet
    void parseFile(String filePath)

    {

       String[] keyWords = {"siteLocation", "isOwner" .... whatever else you got in order of appearence };

       String[keyWords.Length()] answers;

       if (filePath == String.Empty || !File.Exists(filePath))
          return;
       else
       {

          StreamReader sr =  File.OpenText(filePath);
          while (!sr.EndOfStream)//you might want to have 2 dimensional answers array.
          {
             for (int i= 0; i < keyWords.Length(); i++)
             {
                String response = String.Empty;
                String line = sr.ReadLine();                    
                while (!sr.EndOfStream && !line.Contains(keyWords[i])//keep reading untill end of file or next keyword is reached
                {
                   //you might want to do line.Trim() and skip empty lines
                   response += line;
                   line = sr.ReadLine();//read next line
                }

                if (i != 0)//add to the corresponding answer slot.
                   answers[i-1] = response;
             }//end for
          }//end while

        }

    } //end function


                  
            
         
      
    Wednesday, September 3, 2008 10:59 PM
  • Would this code work in VB?
    Thursday, September 4, 2008 12:40 AM
  • I came up with that code off the top of my head and as such it might not work perfectly right away. It will probably need some touching up, but demonstartes the idea well enough I thnink. It should not be too much of a trouble to re-write this in VB, just syntax changes, all libraries I used are .NET and are thus available for VB as well. This is C# by the way.
    Thursday, September 4, 2008 2:36 PM
  • Like people have discussed the problem is mainly a text parsing issue. After you have things parsed out you can either use a datatable, create a dataRow and then call update. If you didn't want to use dataset you could simply create sql commands to insert data into a database. In order to do this you would open a connection, then create a sql command, then execute it.

     

    If have problems inserting data into the database using dataset please provide the specifics. If the inserts are not working correctly I would advise you to print out all the values to the console or MessageBox the string, then add to a DataTable and call update on the tableAdapter.

     

    Thanks

    Chris Robinson

    Software Developer in Test - DataSet

    Friday, September 5, 2008 9:15 PM