C# help anyone? Need help with StreamWriter
 

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>
/// Summary description for Class1.
/// </summary>
class Parser

{
/// <summary>
/// The main entry point for the application.
/// </summary>
[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"+cur rentDate+".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();
}
}
}

Not C#, but take a look at the source code for the gnu utility sed - it does search/replace on strings in a stream of output/input/etc. Assuming your file already existed, you could use the gnu textutils (there are win32 versions) to do

cat createdfile | sed s/\\/-/g | sed s/\~/-/g | sed s/\^/-/g | sed s/\&/and/g | cut -f 1-8 -d " " > new_file_with_replacements

Hmmm, sadly even that is beyond me. I was hoping to keep everything in C# or within my SQL query that creates the DataSet.

Thanks though..

Like I said, take a look at the source code for sed. You should be able to find it on gnu.org . As long as a) you don't copy/paste the code and b) if you do, you don't redistribute it then the GPL won't come into play...

C# has a regular expression type that will do just what you want. If memory serves correctly it is RegEx in the System.Text.RegularExpressions namespace.

So I looked more carefully at the code and figured out how to exclude the last column.

I just needed to change (select * from...) to (select Billing,AccountNumber,CommentDateEx,CommentText from...)

Instead of replacing the invalid characters after the users type them. I am going to prevent my users from using them by using text validation on the Access Form feeding the SQL table. I put a text validation of ' Not Like "*[|\&~^]*" 'with a message box opening up telling the user not to use those characters. That might not be elegant, but at least the original user will modify what they write instead of me trying to figure out what their tilde etc was meant to be.

I am going to look at the source code for the SED utility this weekend, as I am sure I will need to learn how to replace more text in the future.

Thanks guys!