[Neilk]

Hi,

The OT forum is so helpful with all topics that I thought I would query ya'll on some C# info. I've been moved around in my company and my boss is trying to get me to learn C#. Two months ago all I knew was Access programming. Well we started work on a C# project and I am stuck...

We need to move all the notes written in a table in SQL to a pipe delimited text file. The text file has been created, but it's not doing what I want it to do. How can I delete the last column and replace the following characters "\,~,^" and | to "-" and "&" to "and".

One note: The exported commentDate has to be in yyyyMMdd format. If it is easier, I can alter the table and have a regular MM/dd/yyyy format and have the StreamWriter convert the date to the specified format

Thanks for any help,
Neil


For example;
My Starting Point

In SQL Database
Billing AccountNumber CommentDateEx CommentText CommentDate
BILLING 20596300 20070711 Account Balance test 1 \ backslash 7/11/07 0:00
BILLING 20596350 20070711 Account Balance test 2 ~ tilde 7/11/07 0:00
BILLING 20596375 20070711 Account Balance test 3 ^ caret 7/11/07 0:00
BILLING 20596275 20070711 Account Balance test 4 & ampersand 7/11/07 0:00
BILLING 20596475 20070711 Account Balance test 5 | pipe 7/11/07 0:00


My Current Output
BILLING|20596300|20070711|Account Balance test 1 \ backslash|7/11/07 12:00:00 AM
BILLING|20596350|20070711|Account Balance test 2 ~ tilde|7/11/07 12:00:00 AM
BILLING|20596375|20070711|Account Balance test 3 ^ caret|7/11/07 12:00:00 AM
BILLING|20596275|20070711|Account Balance test 4 & ampersand|7/11/07 12:00:00 AM
BILLING|20596475|20070711|Account Balance test 5 | pipe|7/11/07 12:00:00 AM


My Desired Output
BILLING|20596300|20070711|Account Balance test 1 backslash
BILLING|20596350|20070711|Account Balance test 2 tilde
BILLING|20596375|20070711|Account Balance test 3 caret
BILLING|20596275|20070711|Account Balance test 4 ampersand
BILLING|20596475|20070711|Account Balance test 5 pipe


Code below is what I currently have
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;



namespace CommentBuilder
{
///

/// Summary description for Class1.
///

class Parser

{
///

/// The main entry point for the application.
///

[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//
DataSet DS=GetComments(DateTime.Today);
//if program needs to be re-run, change (DateTime.Today) to (DateTime.Parse("InsertDate");
BuildFile(DS);
}
public static string GetUniqueFileNameIdentifier()

{
return DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
}


static DataSet GetComments( DateTime CommentDate )
{
using ( SqlConnection conn = new SqlConnection( "server=Xportal-DB;database=Custom;Integrated Security=true;" ) )
{
using ( SqlCommand cmd = new SqlCommand( "select * from CommentsExport where CommentDate >= '" + CommentDate + "' and

CommentDate < '" + CommentDate.AddDays(1) + "'", conn))
{
cmd.CommandType = CommandType.Text;
DataSet DS=new DataSet();
SqlDataAdapter DA=new SqlDataAdapter(cmd);
DA.Fill(DS);
return DS;
}
}
}
static void BuildFile( DataSet DS)
{ string currentDate=DateTime.Now.ToString("MMddyy");
FileStream fs = new FileStream(@"\\MYSERVER\FOLDER\CommentsExport"+currentDate+".txt",FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter SW = new StreamWriter(fs);
foreach ( DataRow DR in DS.Tables[0].Rows )
{
string txtline = "";
//The function below removes any manual line breaks that might be in the text AND removes the last "|"
foreach ( DataColumn dc in DS.Tables[0].Columns )
{
string val = DR[dc].ToString();
string[] values = val.Split( "\r\n".ToCharArray() );
val = "";
foreach ( string valu in values )
val += valu;
txtline += val + "|";
}
//txtline = txtline.Substring(0, txtline.Remove.c
txtline = txtline.Substring(0, txtline.Length-1);
txtline = txtline.Replace("*&*","*and*"); //DOESN'T WORK
SW.WriteLine( txtline );
}
SW.Flush();
SW.Close();
}
}
}