fpoyavo
asked on
Getting data and populating table
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.
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.
ASKER
Hi Prison,
Good thinking but can you provide code how to do that ?
Thanks a lot.
Good thinking but can you provide code how to do that ?
Thanks a lot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.