Solved

Getting data and populating table

Posted on 2013-12-17
3
242 Views
Last Modified: 2014-01-30
Hi Experts,

I have file where there are html tags and data. So here is what I have to find and then populate in database or maybe to create xml and them load into SQL. Need help here.

We first check if its <!-- BEGIN MAIN CONTENT --> and end when we get to <!-- END MAIN CONTENT --> because file has much more lines so we have to be in this range.


<!-- BEGIN MAIN CONTENT -->
<!-- need to insert Item Name into product table and give it unigue ID --->
<!-- when you find <tr></tr> and td tags inside .. use first td tag's value as next column name and second td tag's value as actual value for this column -->

<table summary="item name" cellspacing="0">
  <thead>
  <tr>
    <th>Item</th>
    <th>Value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
    <td>column-01</td>
    <td><strong><font color="#CC0000">some value</font></strong></td>
  </tr>
  <tr>
    <td>column-02</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-03</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-04</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-05</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-06/td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-07</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-08</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-09</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-10</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-11</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-12</td>
    <td>U/L</td>
  </tr>
  <tr>
    <td>column-13</td>
    <td>
      <!--- if inside of second td tag we find <table> we have to insert those values in a separate table using our item name unigue id in first column
            to identify these entries in second table but we insert one row per each <tr> tag
         
       second table will look like :

       col1         col2       col3
       item-id      value-01   value-02
       item-id      value-01   value-02  
       item-id      value-01   value-02
      --->

      <table>
        <tbody>
        <tr>
          <td>value-01</td>
          <td>value-02</td>
        </tr>
        <tr>
          <td>value-01</td>
          <td>value-02</td>
        </tr>
        <tr>
          <td>value-01</td>
          <td>value-02</td>
        </tr>
        <tr>
          <td>value-01</td>
          <td>value-02</td>
        </tr>
        <tr>
          <td>value-01</td>
          <td>value-02</td>
        </tr>
        </tbody>
      </table>
      <br>      
      <br>

<!--- this one is wihout tags but we need to catch it by "Note:" name column "Note" and get all between "Note:" and </td> as Note column value

   
Note:      <br>   LOTS OF NOTES GOES HERE ------------------------------------------------------------  

</td>
  </tr>
  <tr>
    <td>column-14</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-15</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-16</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-17</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-18</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-19</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-19</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-20</td>
    <td>some value</td>
  </tr>
  <tr>
    <td>column-21</td>
    <td><a href="" target="_blank">Click here</a></td>
  </tr>
  </tbody>
</table>

<!-- END MAIN CONTENT -->

thanks.
0
Comment
Question by:fpoyavo
  • 2
3 Comments
 
LVL 3

Expert Comment

by:PrisonBroken
ID: 39726089
You could read the html file into a string

StreamReader objStreamReader = default(StreamReader);
string fstrFile = null;

objStreamReader = File.OpenText("PAth to file");

//Now, read the entire file into a string
fstrFile = objStreamReader.ReadToEnd();
objStreamReader.Close();

while (fstrFile .Length > 0) {
'pattern match string here
}

Then iterate over the string looking for <td> tags to strip out the data.
If you can figure out a consistent break point in the string you could perhaps excise the second tables string first and deal with the 2 separately.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 39726430
Hi Prison,

Good thinking but can you provide code how to do that ?

Thanks a lot.
0
 
LVL 3

Accepted Solution

by:
PrisonBroken earned 500 total points
ID: 39726904
Ok something like

Appologies C# not really my normal language so the syntax might need a little tweaking

int count;
string col1;
string col2;


if (fstrFile.IndexOf("<td>") != -1)
{
fstrFile = fstrFile.Substring(0, fstrFile.IndexOf("<td>") )
      if( (count % 2) == 0 )
          'trap column 1
          col1 = fstrFile.Substring(0, strFile.Substring(0, fstrFile.IndexOf("<td>") ))
      else
      {
         'trap column 2
          col2 = fstrFile.Substring(0, strFile.Substring(0, fstrFile.IndexOf("<td>") ))
          'insert into database read to new table file etc
      }
      intCount++
fstrFile = fstrFile.Substring(0, fstrFile.IndexOf("<td>") )
}
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now