EDN Admin
Well-known member
<p style="margin:0in 0in 0pt <a name="x_x__MailAutoSig <span style="font-size:12pt; color:black <span style="font-family:Calibri Hello everyone,
<p style="margin:0in 0in 0pt <span><span style="font-size:12pt; color:black <span style="font-family:Calibri
<span>
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri The attached code currently code reads in my EXCEL file (sample shown below) and creates a TEXT file (shown below).
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<table border="0" cellspacing="0" cellpadding="0" width="768" style="margin:auto auto auto -1.15pt; width:8in; border-collapse:collapse
<tbody>
<tr style="height:15pt
<td colspan="3" width="183" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="192" style="width:144pt; border-collapse:collapse
<colgroup span="1 <col span="3" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="3" width="192" height="20" style="width:144pt; height:15pt; background-color:yellow; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri General</td>
</tr>
</tbody>
</table>
</td>
<td colspan="4" width="255" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="256" style="width:192pt; border-collapse:collapse
<colgroup span="1 <col span="4" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="4" width="256" height="20" style="width:192pt; height:15pt; background-color:#ccccff; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri Car</td>
</tr>
</tbody>
</table>
</td>
<td colspan="5" width="330" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="320" style="width:240pt; border-collapse:collapse
<colgroup span="1 <col span="5" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="5" width="320" height="20" style="width:240pt; height:15pt; background-color:#ff6600; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri Boat</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr style="height:15pt
<td colspan="3" width="183" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="192" style="width:144pt; border-collapse:collapse
<colgroup span="1 <col span="3" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="3" width="192" height="20" style="width:144pt; height:15pt; background-color:#ff99cc; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri info</td>
</tr>
</tbody>
</table>
</td>
<td colspan="2" width="134" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="128" style="width:96pt; border-collapse:collapse
<colgroup span="1 <col span="2" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="2" width="128" height="20" style="width:96pt; height:15pt; background-color:#0066cc; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri main</td>
</tr>
</tbody>
</table>
</td>
<td colspan="2" width="121" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="128" style="width:96pt; border-collapse:collapse
<colgroup span="1 <col span="2" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="2" width="128" height="20" style="width:96pt; height:15pt; background-color:#333399; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri sub</td>
</tr>
</tbody>
</table>
</td>
<td colspan="3" width="209" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="192" style="width:144pt; border-collapse:collapse
<colgroup span="1 <col span="3" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="3" width="192" height="20" style="width:144pt; height:15pt; background-color:#ffcc00; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri main</td>
</tr>
</tbody>
</table>
</td>
<td colspan="2" width="121" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="128" style="width:96pt; border-collapse:collapse
<colgroup span="1 <col span="2" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="2" width="128" height="20" style="width:96pt; height:15pt; background-color:#993300; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri cost</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr style="height:15pt
<td width="69" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri Name
</td>
<td width="27" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri ID
</td>
<td width="88" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri City
</td>
<td width="50" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri ID
</td>
<td width="84" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri model
</td>
<td width="56" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri size
</td>
<td width="65" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri load
</td>
<td width="61" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri id
</td>
<td width="78" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri model
</td>
<td width="70" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri reg
</td>
<td width="79" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri main
</td>
<td width="41" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri ex
</td>
</tr>
<tr style="height:15pt
<td width="69" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Billy
</td>
<td width="27" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 1
</td>
<td width="88" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Dallas
</td>
<td width="50" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 234
</td>
<td width="84" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri GWX
</td>
<td width="56" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 20
</td>
<td width="65" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri yes
</td>
<td width="61" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 3423
</td>
<td width="78" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri tt
</td>
<td width="70" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri 556-6
</td>
<td width="79" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 1000
</td>
<td width="41" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 45
</td>
</tr>
<tr style="height:15pt
<td width="69" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Bob
</td>
<td width="27" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 2
</td>
<td width="88" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Chicago
</td>
<td width="50" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 555
</td>
<td width="84" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri GYZ
</td>
<td width="56" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 30
</td>
<td width="65" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri no
</td>
<td width="61" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 455
</td>
<td width="78" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri dgx
</td>
<td width="70" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri 56-6
</td>
<td width="79" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 2000
</td>
<td width="41" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 50
</td>
</tr>
</tbody>
</table>
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif TEXT:
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri General<span>
F2<span> F3<span>
Car<span> F5<span>
F6<span> F7<span>
Boat<span> F9<span>
F10<span> F11<span>
F12
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Name
<span>
City<span> ID<span>
model<span> size<span> load<span>
id<span> model<span>
reg<span> main<span>
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Billy<span>
1<span> Dallas<span>
234<span> GWX<span>
20<span> yes<span>
3423<span> tt<span>
556-6<span> 1000<span> 45
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Bob<span>
2<span> Chicago<span>
555<span> GYZ<span>
30<span> no<span>
455<span> dgx<span>
56-6<span> 2000<span>
50
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri -What I would like for my code to do is:
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri 1. Based on the MAIN Category (GENERAL,CAR,BOAT) , SUB Category (INFO,MAIN,SUBâ) for each MAIN Category AND column heading (Name, ID, Cityâ)
for each SUB Category, place the <span style="text-decoration:underline value of the column in specific
<span style="text-decoration:underline START/END position in the text file.
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri EX. For Car/Main/ID â the Start position is 30 and End position is 40 (therefore it would look like â234<span>
<span> âwith 7 spaces left overâ)
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri <span>
For Car/Main/modwl â the start position is 10 and End Position is 25 (therefore it would look like âGWX â. With 12 spaces blankâ)
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri <span>
Therefore: for the first row: Billy,1,Dallas etc. would all have specific START/END positions in a text file
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Any help with this is greatly appreciated!
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Please let me know if further explanation is required! I know this might be confusing!!!
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Thank you so much!
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Calibri
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Calibri
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Calibri
<span style="font-size:small; font-family:Calibri
<div style="color:black; background-color:white
<pre><span style="color:blue using System;
<span style="color:blue using System.Collections.Generic;
<span style="color:blue using System.Linq;
<span style="color:blue using System.Text;
<span style="color:blue using System.Data.OleDb;
<span style="color:blue using System.Data;
<span style="color:blue using System.IO;
<span style="color:blue namespace Excel2010Transform
{
<span style="color:blue class Transform_OleDb
{
<span style="color:blue static <span style="color:blue void Main(<span style="color:blue string[] args)
{
<span style="color:green //The connection string to the excel file
String path = <span style="color:#a31515 @"C:AAATempNameID.xlsx";
String connStr = <span style="color:#a31515 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + <span style="color:#a31515 ";Extended Properties=Excel 12.0;";
<span style="color:green //The connection to that file
OleDbConnection conn = <span style="color:blue new OleDbConnection(connStr);
<span style="color:green //The query
<span style="color:blue string strSQL = <span style="color:#a31515 "SELECT * FROM [Sheet1$]";
<span style="color:green //The command
OleDbCommand cmd = <span style="color:blue new OleDbCommand(<span style="color:green /*The query*/strSQL, <span style="color:green /*The connection*/conn);
DataTable dt = <span style="color:blue new DataTable();
conn.Open();
<span style="color:blue try
{
OleDbDataReader dr1 = cmd.ExecuteReader();
StreamWriter sw = <span style="color:blue new StreamWriter(<span style="color:#a31515 "C:\AAAtemp\export.txt");
<span style="color:blue if (dr1.Read())
{
dt.Load(dr1);
}
<span style="color:blue int iColCount = dt.Columns.Count;
<span style="color:blue for (<span style="color:blue int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns);
<span style="color:blue if (i < iColCount - 1)
{
sw.Write(<span style="color:#a31515 "t");
}
}
sw.Write(sw.NewLine);
<span style="color:green // Now write all the rows.
<span style="color:blue foreach (DataRow dr <span style="color:blue in dt.Rows)
{
<span style="color:blue for (<span style="color:blue int i = 0; i < iColCount; i++)
{
<span style="color:blue if (!Convert.IsDBNull(dr))
{
sw.Write(dr.ToString());
}
<span style="color:blue if (i < iColCount - 1)
{
sw.Write(<span style="color:#a31515 "t");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
Console.WriteLine(<span style="color:#a31515 "File is saved");
}
<span style="color:blue catch (OleDbException caught)
{
Console.WriteLine(caught.Message);
}
<span style="color:blue finally
{
conn.Close();
}
Console.Read();
}
}
}
[/code]
<span style="font-size:small; font-family:Calibri <span style="font-size:small; font-family:Calibri
<p style="margin:0in 0in 0pt
<p style="margin:0in 0in 0pt <span><span style="font-size:small; font-family:Calibri
<br/>
View the full article
<p style="margin:0in 0in 0pt <span><span style="font-size:12pt; color:black <span style="font-family:Calibri
<span>
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri The attached code currently code reads in my EXCEL file (sample shown below) and creates a TEXT file (shown below).
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<table border="0" cellspacing="0" cellpadding="0" width="768" style="margin:auto auto auto -1.15pt; width:8in; border-collapse:collapse
<tbody>
<tr style="height:15pt
<td colspan="3" width="183" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="192" style="width:144pt; border-collapse:collapse
<colgroup span="1 <col span="3" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="3" width="192" height="20" style="width:144pt; height:15pt; background-color:yellow; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri General</td>
</tr>
</tbody>
</table>
</td>
<td colspan="4" width="255" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="256" style="width:192pt; border-collapse:collapse
<colgroup span="1 <col span="4" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="4" width="256" height="20" style="width:192pt; height:15pt; background-color:#ccccff; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri Car</td>
</tr>
</tbody>
</table>
</td>
<td colspan="5" width="330" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="320" style="width:240pt; border-collapse:collapse
<colgroup span="1 <col span="5" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="5" width="320" height="20" style="width:240pt; height:15pt; background-color:#ff6600; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri Boat</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr style="height:15pt
<td colspan="3" width="183" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="192" style="width:144pt; border-collapse:collapse
<colgroup span="1 <col span="3" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="3" width="192" height="20" style="width:144pt; height:15pt; background-color:#ff99cc; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri info</td>
</tr>
</tbody>
</table>
</td>
<td colspan="2" width="134" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="128" style="width:96pt; border-collapse:collapse
<colgroup span="1 <col span="2" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="2" width="128" height="20" style="width:96pt; height:15pt; background-color:#0066cc; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri main</td>
</tr>
</tbody>
</table>
</td>
<td colspan="2" width="121" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="128" style="width:96pt; border-collapse:collapse
<colgroup span="1 <col span="2" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="2" width="128" height="20" style="width:96pt; height:15pt; background-color:#333399; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri sub</td>
</tr>
</tbody>
</table>
</td>
<td colspan="3" width="209" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="192" style="width:144pt; border-collapse:collapse
<colgroup span="1 <col span="3" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="3" width="192" height="20" style="width:144pt; height:15pt; background-color:#ffcc00; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri main</td>
</tr>
</tbody>
</table>
</td>
<td colspan="2" width="121" valign="bottom
<table border="0" cellspacing="0" cellpadding="0" width="128" style="width:96pt; border-collapse:collapse
<colgroup span="1 <col span="2" width="64" style="width:48pt </colgroup>
<tbody>
<tr height="20" style="height:15pt
<td colspan="2" width="128" height="20" style="width:96pt; height:15pt; background-color:#993300; border:windowtext 0.5pt solid
<span style="font-size:small; font-family:Calibri cost</td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr style="height:15pt
<td width="69" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri Name
</td>
<td width="27" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri ID
</td>
<td width="88" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri City
</td>
<td width="50" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri ID
</td>
<td width="84" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri model
</td>
<td width="56" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri size
</td>
<td width="65" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri load
</td>
<td width="61" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri id
</td>
<td width="78" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri model
</td>
<td width="70" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri reg
</td>
<td width="79" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri main
</td>
<td width="41" valign="bottom
<p style="margin:0in 6pt 0pt <span style="text-decoration:underline <span style="font-size:12pt; color:black <span style="font-family:Calibri ex
</td>
</tr>
<tr style="height:15pt
<td width="69" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Billy
</td>
<td width="27" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 1
</td>
<td width="88" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Dallas
</td>
<td width="50" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 234
</td>
<td width="84" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri GWX
</td>
<td width="56" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 20
</td>
<td width="65" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri yes
</td>
<td width="61" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 3423
</td>
<td width="78" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri tt
</td>
<td width="70" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri 556-6
</td>
<td width="79" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 1000
</td>
<td width="41" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 45
</td>
</tr>
<tr style="height:15pt
<td width="69" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Bob
</td>
<td width="27" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 2
</td>
<td width="88" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Chicago
</td>
<td width="50" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 555
</td>
<td width="84" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri GYZ
</td>
<td width="56" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 30
</td>
<td width="65" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri no
</td>
<td width="61" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 455
</td>
<td width="78" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri dgx
</td>
<td width="70" valign="bottom
<p style="margin:0in 6pt 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri 56-6
</td>
<td width="79" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 2000
</td>
<td width="41" valign="bottom
<p style="margin:0in 6pt 0pt; text-align:right <span style="font-size:12pt; color:black <span style="font-family:Calibri 50
</td>
</tr>
</tbody>
</table>
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif TEXT:
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri General<span>
F2<span> F3<span>
Car<span> F5<span>
F6<span> F7<span>
Boat<span> F9<span>
F10<span> F11<span>
F12
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Name
<span>
City<span> ID<span>
model<span> size<span> load<span>
id<span> model<span>
reg<span> main<span>
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Billy<span>
1<span> Dallas<span>
234<span> GWX<span>
20<span> yes<span>
3423<span> tt<span>
556-6<span> 1000<span> 45
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Bob<span>
2<span> Chicago<span>
555<span> GYZ<span>
30<span> no<span>
455<span> dgx<span>
56-6<span> 2000<span>
50
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri -What I would like for my code to do is:
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri 1. Based on the MAIN Category (GENERAL,CAR,BOAT) , SUB Category (INFO,MAIN,SUBâ) for each MAIN Category AND column heading (Name, ID, Cityâ)
for each SUB Category, place the <span style="text-decoration:underline value of the column in specific
<span style="text-decoration:underline START/END position in the text file.
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri EX. For Car/Main/ID â the Start position is 30 and End position is 40 (therefore it would look like â234<span>
<span> âwith 7 spaces left overâ)
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri <span>
For Car/Main/modwl â the start position is 10 and End Position is 25 (therefore it would look like âGWX â. With 12 spaces blankâ)
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri <span>
Therefore: for the first row: Billy,1,Dallas etc. would all have specific START/END positions in a text file
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black; font-family:Times New Roman,serif
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Any help with this is greatly appreciated!
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Please let me know if further explanation is required! I know this might be confusing!!!
<p style="margin:0in 0in 0pt <span style="font-size:12pt; color:black <span style="font-family:Calibri Thank you so much!
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Calibri
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Calibri
<p style="margin:0in 0in 0pt <span style="font-size:small; font-family:Calibri
<span style="font-size:small; font-family:Calibri
<div style="color:black; background-color:white
<pre><span style="color:blue using System;
<span style="color:blue using System.Collections.Generic;
<span style="color:blue using System.Linq;
<span style="color:blue using System.Text;
<span style="color:blue using System.Data.OleDb;
<span style="color:blue using System.Data;
<span style="color:blue using System.IO;
<span style="color:blue namespace Excel2010Transform
{
<span style="color:blue class Transform_OleDb
{
<span style="color:blue static <span style="color:blue void Main(<span style="color:blue string[] args)
{
<span style="color:green //The connection string to the excel file
String path = <span style="color:#a31515 @"C:AAATempNameID.xlsx";
String connStr = <span style="color:#a31515 "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + <span style="color:#a31515 ";Extended Properties=Excel 12.0;";
<span style="color:green //The connection to that file
OleDbConnection conn = <span style="color:blue new OleDbConnection(connStr);
<span style="color:green //The query
<span style="color:blue string strSQL = <span style="color:#a31515 "SELECT * FROM [Sheet1$]";
<span style="color:green //The command
OleDbCommand cmd = <span style="color:blue new OleDbCommand(<span style="color:green /*The query*/strSQL, <span style="color:green /*The connection*/conn);
DataTable dt = <span style="color:blue new DataTable();
conn.Open();
<span style="color:blue try
{
OleDbDataReader dr1 = cmd.ExecuteReader();
StreamWriter sw = <span style="color:blue new StreamWriter(<span style="color:#a31515 "C:\AAAtemp\export.txt");
<span style="color:blue if (dr1.Read())
{
dt.Load(dr1);
}
<span style="color:blue int iColCount = dt.Columns.Count;
<span style="color:blue for (<span style="color:blue int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns);
<span style="color:blue if (i < iColCount - 1)
{
sw.Write(<span style="color:#a31515 "t");
}
}
sw.Write(sw.NewLine);
<span style="color:green // Now write all the rows.
<span style="color:blue foreach (DataRow dr <span style="color:blue in dt.Rows)
{
<span style="color:blue for (<span style="color:blue int i = 0; i < iColCount; i++)
{
<span style="color:blue if (!Convert.IsDBNull(dr))
{
sw.Write(dr.ToString());
}
<span style="color:blue if (i < iColCount - 1)
{
sw.Write(<span style="color:#a31515 "t");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
Console.WriteLine(<span style="color:#a31515 "File is saved");
}
<span style="color:blue catch (OleDbException caught)
{
Console.WriteLine(caught.Message);
}
<span style="color:blue finally
{
conn.Close();
}
Console.Read();
}
}
}
[/code]
<span style="font-size:small; font-family:Calibri <span style="font-size:small; font-family:Calibri
<p style="margin:0in 0in 0pt
<p style="margin:0in 0in 0pt <span><span style="font-size:small; font-family:Calibri
<br/>
View the full article