Data Handling

I'm doing an application where I want to store some data, but I've had no luck connecting to databases and my brother in law, doesn't have SQL Server and I don't know how to write to Access, though I could learn if convinced that's a good solution. So, in order to do what I want to, I've been feeding strings to text files with separators between data points. For instance, a string my look like this:

"Jul/25/2005!Cincinnati, [email protected]#1000%500^50&20*15~"

Here is my writing code:

[code]using (StreamWriter sw = File.AppendText(filename))
{
sw.WriteLine(filedate + "!" + txtCity.Text + ", " +
cbState.SelectedItem + "@ + txtJobType.Text + #" +
txtAmountPaid.Text + "%" + txtPaidLabor.Text + "^" +
txtMileage.Text + "&" + txtGasCost.Text + "*" + txtMiscCost.Text +
"~");
}
[/code]

I need to total up the number after #, the number after %, the number after ^, the number after &, and the number after *. What I planned to do, what I've done before, years ago in a C++ app, was move to the #, read until the %, save that to a string, convert it to float, add it to itself, then read fom % to ^ and save to string and convert it to float, add it to itself, etc... Obviously, this is a very brute force way to do this data handling, but I have forgotten how to do this. Can anyone tell me how I would implement this solution?

Secondly, is there a better way to do this with perhaps Microsoft Access or Microsoft Excel on a Windows XP system, the only two programs my brother has? Is there perhaps a good tutorial in doing this? My books only cover C# apps to SQL Server.

Thanks...

Comments

  • : I'm doing an application where I want to store some data, but I've had no luck connecting to databases and my brother in law, doesn't have SQL Server and I don't know how to write to Access, though I could learn if convinced that's a good solution. So, in order to do what I want to, I've been feeding strings to text files with separators between data points. For instance, a string my look like this:
    :
    : "Jul/25/2005!Cincinnati, [email protected]#1000%500^50&20*15~"
    :
    : Here is my writing code:
    :
    : [code]using (StreamWriter sw = File.AppendText(filename))
    : {
    : sw.WriteLine(filedate + "!" + txtCity.Text + ", " +
    : cbState.SelectedItem + "@ + txtJobType.Text + #" +
    : txtAmountPaid.Text + "%" + txtPaidLabor.Text + "^" +
    : txtMileage.Text + "&" + txtGasCost.Text + "*" + txtMiscCost.Text +
    : "~");
    : }
    : [/code]
    :
    : I need to total up the number after #, the number after %, the number after ^, the number after &, and the number after *. What I planned to do, what I've done before, years ago in a C++ app, was move to the #, read until the %, save that to a string, convert it to float, add it to itself, then read fom % to ^ and save to string and convert it to float, add it to itself, etc... Obviously, this is a very brute force way to do this data handling, but I have forgotten how to do this. Can anyone tell me how I would implement this solution?
    :
    : Secondly, is there a better way to do this with perhaps Microsoft Access or Microsoft Excel on a Windows XP system, the only two programs my brother has? Is there perhaps a good tutorial in doing this? My books only cover C# apps to SQL Server.
    :
    : Thanks...
    :

    Wow ...

    Well there is a couple ways you can do this actually. Obviously using Access and Excel is a better solution. But, even if you do not want to rely on these programs you can connect to a text file using an ODBC driver and use SQL queries to parse out your text file and add records:

    There is too much to explain, so the best I can do is point you to some articles:

    http://www.c-sharpcorner.com/Code/2002/Nov/AccessTextDb.asp

    http://www.c-sharpcorner.com/database/Connect/ConnectODBCText.asp

    The beautiful thing is, you can define a schema for your text file by writing definitions to a shema.ini file that resides in the same directory as your text file. The schema can define which delimiter separates the columns (comma, tab, or custom character). You can also define each column and its data type (integer, text, etc ...)

    To learn more about the schema.ini file for the Text ODBC driver go to:

    http://msdn.microsoft.com/library/?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp?frame=true

    Here is an example schema file:

    [code]
    [EMP.TXT]
    ColNameHeader = True
    CharacterSet = ANSI
    Format = TabDelimited
    Col1=EmployeeID Integer
    Col2=LastName Char Width 20
    Col3=FirstName Char Width 10
    Col4=Title Char Width 30
    Col5=TitleOfCourtesy Char Width 25
    Col6=BirthDate Date
    Col7=HireDate Date
    Col8=Address Char Width 60
    Col9=City Char Width 15
    Col10=Region Char Width 15
    Col11=PostalCode Char Width 10
    Col12=Country Char Width 15
    Col13=HomePhone Char Width 24
    Col14=Extension Char Width 4
    Col15=Photo OLE
    Col16=Notes LongChar
    Col17=ReportsTo Integer
    [/code]

    This defines the schema for the Emp.txt file in the same directory. It also specifies that the first row of the text file defines the column names. It also states that the text file is ANSI (not unicode) and separates each column with a tab ( in C#.) Finally, It defines that the text file should have 17 columns per row and of course your responsible for making sure the right data, in the right data type, gets stored to the right column.

    An example SQL query that returns data from this example text file would be:

    [code]
    SELECT LastName, FirstName FROM EMP.TXT
    [/code]

    Some simple C# code:

    [code]
    OdbcConnection conn = new OdbcConnection(
    @Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:DataFolder);

    string query = "SELECT LastName, FirstName FROM EMP.TXT";

    OdbcCommand cmd = new OdbcCommand(query, conn);
    OdbcDataReader reader = null;

    conn.Open();
    reader = cmd.ExecuteReader();

    while(reader.read())
    {
    Debug.WriteLine(reader("FirstName").ToString());
    Debug.WriteLine(reader("LastName").ToString());
    }
    reader.Close();
    conn.Close();
    [/code]

    Note that the connection string assumes that the text files are located in the c:DataFolder folder.

    Hopefully, this all answers your question.







  • That definitely looks like the way I want to go, but I have two questions. First, my program will dynamically create files based on month and year, so a typical file will be called "Jul.2005.gth." How do I apply that schema to all files created? Is there a way to dynamically add to the schema.ini file, or is there a way to apply it to all files in that folder?

    Second question, how would I totalize a column? How would I navigate through, selecting a specific column, convert text to float, and add it to itself?

    Third question (I know I said two), in your opinion, do you think it would be easier to create a database in Access, dynamically create tables based on the month and the year, create one cell to contain the totalized information, and then just read that cell? I can learn most things pretty easily, but I need to know if that would be viable solution.
  • 1. Yes you can dynamically create the schema.ini file, using objects from the System.IO namespace in .NET. I believe you can have multiple text file definitions in a schema.ini file as well.

    [code]
    [EMP1.TXT]
    ColNameHeader = True
    CharacterSet = ANSI
    Format = TabDelimited
    Col1=EmployeeID Integer
    Col2=LastName Char Width 20
    Col3=FirstName Char Width 10
    Col4=Title Char Width 30
    Col5=TitleOfCourtesy Char Width 25

    [B]
    [EMP2.TXT]
    ColNameHeader = True
    CharacterSet = ANSI
    Format = TabDelimited
    Col1=EmployeeID Integer
    Col2=LastName Char Width 20
    Col3=FirstName Char Width 10
    Col4=Title Char Width 30
    Col5=TitleOfCourtesy Char Width 25
    [/b]
    [/code]

    Finally, it should not matter what the file extension of the text file is, as long as the contents of the file are parsable by the ODBC Text driver.

    [code]
    [b]
    [Jul.2005.gth]
    [/b]
    ColNameHeader = True
    CharacterSet = ANSI
    Format = TabDelimited
    Col1=EmployeeID Integer
    Col2=LastName Char Width 20
    Col3=FirstName Char Width 10
    Col4=Title Char Width 30
    Col5=TitleOfCourtesy Char Width 25
    [/code]

    2. Well the beautiful thing about the text driver and the schema.ini is that when the ODBC driver opens the text file and matching schema.ini file, the data will be converted to the proper type based on how you defined the columns. Here is some sample code that uses a data reader to loop through the results and add up the column.

    If the third column in the text file is a floating point then ...

    [code]
    double total = 0;

    OdbcConnection conn = new OdbcConnection(
    @Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:DataFolder);

    string query = "SELECT * FROM SomeFile.TXT";

    OdbcCommand cmd = new OdbcCommand(query, conn);
    OdbcDataReader reader = null;

    conn.Open();
    reader = cmd.ExecuteReader();

    while(reader.read())
    {
    // Column ordinals are zero-based, so this grabs the value at ...
    // column number 3 and automatically converts it to a double.
    [b]total += reader.GetDouble(2);[/b]
    }
    reader.Close();
    conn.Close();
    [/code]

    I believe you can also accomplish the same thing with a SQL query. I am not sure if the ODBC Text driver supports this ...

    [code]
    double total = 0;

    OdbcConnection conn = new OdbcConnection(
    @Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:DataFolder);

    string query = [b]"SELECT SUM(ColumnToTotal) FROM EMP.TXT";[/b]

    OdbcCommand cmd = new OdbcCommand(query, conn);

    conn.Open();
    object objTotal = cmd.ExecuteScalar();
    conn.Close();

    if(null != objTotal)
    total = (double)objTotal;
    [/code]

    3. In my opinion, using a database system is easier than a text file. Because A. you can graphically create your data layouts and B. you can write more robust SQL queries.

    Sometimes it may be convinient to store the totals in a column, BUT it breaks the rules of database normalization. Usually the totals can be aquired through SQL statements.

    [code]
    SELECT SUM(ColumnA) AS TotalA, SUM(ColumnB) AS TotalB FROM SomeTable
    [/code]

  • [b][red]This message was edited by shinsnake at 2005-7-26 16:19:22[/red][/b][hr]
    [b][red]This message was edited by shinsnake at 2005-7-26 16:18:18[/red][/b][hr]
    Okay, I can write code to interact with SQL databases, and I can create SQL databases easy enough, but I'm still a little confused on a couple things. I took classes to get my MCAD.NET and the first test I took was 70-229, which was SQL Database programming. I know how to create databases and manipulate them with code using either SQL Query Analyzer or SQL Enterprise manager. But the confusion comes in when my brother in law doesn't have SQL Server 2000. Can I create a database in SQL Server 2000 on a Windows Server 2003 machine that he can then use on his Windows XP machine? I would like to do that as that's what I know best. Is that possible?

    Otherwise, what you've given me above should work fine.

    Actually, I just said screw it and I'm learning how to program an Access 2000 Database. Here's my code:

    [code]string filename = "D:\Documents and Settings\Administrator\My " +
    "Documents\Visual Studio Projects\Graebel Tax Holder\bin\" +
    "Debug\GTH.mdb";

    string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
    filename;

    string cmdSQL = "INSERT INTO Test (TestText) VALUES (" +
    txtJobType.Text + ")";

    OleDbConnection myConn = new OleDbConnection(conn);
    OleDbCommand myCmd = new OleDbCommand( cmdSQL, myConn );

    try
    {
    myConn.Open();
    myCmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.Message, "Submission error");
    }
    finally
    {
    myConn.Close();
    }[/code]

    It's going real well. I just have to add the other values now.

    Thanks...


  • [b][red]This message was edited by shinsnake at 2005-7-26 18:49:0[/red][/b][hr]
    I'm trying to write to the database with this code:

    [code]string cmdSQL = "INSERT INTO GTHData (Date, City, State, JobType, " +
    "AmountPaid, PaidLabor, Mileage, GasCost, MiscCost) VALUES ('" +
    filedate + "','" + txtCity.Text + "','" + cbState.SelectedItem +
    "','" + txtJobType.Text + "'," + fileamountpaid + "," +
    filepaidlabor + "," + filemileage + "," + filegascost + "," +
    filemisccost + ")";[/code]

    It keeps giving me this error in my previous catch statement:

    "Syntax error in INSERT INTO statement."

    I can't seem to find the error anywhere. Can you help me out?

    I've checked all of the data types and they all match up.

    I've found the one data that is giving me problems, filedate.

    Here's its definition:

    [code]string filedate = string.Format("{0}.{1}.{2}", cbMonth.SelectedItem,
    cbDay.SelectedItem, cbYear.SelectedItem);[/code]

    For some reason, that won't work. I check it in debug mode and the string looks like I want it to: 7.26.2005, but it won't go into the database. I've checked every other individual insert and it works. Why won't this one?
  • With Access, you have to put a date between a pound (#) symbol rather than single quotes:

    [code]
    string cmdSQL =
    "INSERT INTO GTHData (Date, City, State, JobType, AmountPaid, PaidLabor, Mileage, GasCost, MiscCost) " +
    "VALUES ([b]#[/b]" + filedate + "[b]#[/b],'" + txtCity.Text + "','" + cbState.SelectedItem + "','" + txtJobType.Text + "'," + fileamountpaid + "," + filepaidlabor + "," + filemileage + "," + filegascost + "," + filemisccost + ")";

    [/code]

    As for running SQL Server, there is a desktop version of SQL Server called MSDE. Go here to learn more:

    [code]
    http://www.microsoft.com/sql/msde/
    [/code]

    It should be free to use.
  • Okay, I'm still getting the error Syntax Error in INSERT INTO Statement. I decided to try just a simple string without any variables. Here it is:

    [code]string cmdSQL = "INSERT INTO GTHData (City, State, Month, Day, Year, " +
    "JobType, AmountPaid, PaidLabor, Mileage, GasCost, MiscCost) " +
    "VALUES ('Cincinnati', 'OH', 'Jul', 27, 2005, 'Delivery', 1000, " +
    "500, 50, 20, 15)";[/code]

    I still get the same error. I assume the problem is in the access database. Here's its design:

    [code](ACCESS DATABASE: USED CODE FUNCTION FOR FORMATTING PURPOSES)
    City Text
    State Text
    Month Text
    Day Number (Int)
    Year Number (Int)
    JobType Text
    AmountPaid Number (Double)
    PaidLabor Number (Double)
    Mileage Number (Double)
    GasCost Number (Double)
    MiscCost Number (Double)[/code]

    If I eliminate writing the Month, Day, and Year values, the writing to the Access DB occurs successfully. I need some help, please...

  • Hmm ... The query looks right to me ... what is the exact error message

    : Okay, I'm still getting the error Syntax Error in INSERT INTO Statement. I decided to try just a simple string without any variables. Here it is:
    :
    : [code]string cmdSQL = "INSERT INTO GTHData (City, State, Month, Day, Year, " +
    : "JobType, AmountPaid, PaidLabor, Mileage, GasCost, MiscCost) " +
    : "VALUES ('Cincinnati', 'OH', 'Jul', 27, 2005, 'Delivery', 1000, " +
    : "500, 50, 20, 15)";[/code]
    :
    : I still get the same error. I assume the problem is in the access database. Here's its design:
    :
    : [code](ACCESS DATABASE: USED CODE FUNCTION FOR FORMATTING PURPOSES)
    : City Text
    : State Text
    : Month Text
    : Day Number (Int)
    : Year Number (Int)
    : JobType Text
    : AmountPaid Number (Double)
    : PaidLabor Number (Double)
    : Mileage Number (Double)
    : GasCost Number (Double)
    : MiscCost Number (Double)[/code]
    :
    : If I eliminate writing the Month, Day, and Year values, the writing to the Access DB occurs successfully. I need some help, please...
    :
    :

  • : Hmm ... The query looks right to me ... what is the exact error message
    :

    Syntax error in INSERT INTO statement.
  • : : Hmm ... The query looks right to me ... what is the exact error message
    : :
    :
    : Syntax error in INSERT INTO statement.
    :

    I just started over and it worked. I started with JobDate instead of Date, and it seemed to work fine. :shrugs

    Thanks for your help.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories