Solved

Getting data and populating table

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

15 Experts available now in Live!

Get 1:1 Help Now