Solved

Getting data and populating table

Posted on 2013-12-17
3
237 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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