CODEWITHSUNDEEP
c#asp.net.net
jorame
jorame

Reputation: 2207

Reading Text Files and then breaking down lines into columns

I have an assignment where I need to read a text file and then breakdown each line into columns then I need to insert that into database.

What's the best approach for this? Any help will be appreciate it and if you could provide code will be even better.

This is what I have so far

    string filename = Server.MapPath("~/Text_File_4.txt");

    StreamReader sr = new StreamReader(filename);

    string styl;
    string colr;
    string sdim;
    string size;
    string qty;
    string line;

    string sprice;
    string sretail;

    while ((line = sr.ReadLine()) != null)

    {
        styl = line.Substring(0, 6);
        colr = line.Substring(6, 2);
        sdim = line.Substring(8, 1);
        size = line.Substring(14, 3);
        qty = line.Substring(19, 5);


        sprice = line.Substring(27, 6);
        sretail = line.Substring(38, 4);

        con.Open();
        cmd = new SqlCommand("insert into ststyl00(ststyl, stcolr, stsdim, stszcd, stprq, strprq) values(@ststyl, @stcolr, @stsdim, @stszcd, @stprq, @strprq)", con);

        cmd.Parameters.Add("@ststyl", SqlDbType.VarChar, 15).Value = styl;
        cmd.Parameters.Add("@stcolr", SqlDbType.VarChar, 3).Value = colr;
        cmd.Parameters.Add("@stsdim", SqlDbType.VarChar, 8).Value = sdim;
        cmd.Parameters.Add("@stszcd", SqlDbType.VarChar, 3).Value = size;

        cmd.Parameters.Add("@stprq", SqlDbType.VarChar, 8).Value = sprice;
        cmd.Parameters.Add("@strprq", SqlDbType.VarChar, 8).Value = sretail;
        cmd.ExecuteNonQuery();
        con.Close();



    }

Upvotes: 0

Views: 2634

Answers (3)

ssis_ssiSucks
ssis_ssiSucks

Reputation: 1506

In addition to the other parsing techniques already suggested, you can use the TextFieldParser class (it's in the Microsoft.VisualBasic.FileIO namespace) in conjunction with the ADO.Net code you've already written

Upvotes: 0

Eric J.
Eric J.

Reputation: 150118

Input is a CSV

If your input files are CSV files, I strongly recommend using the CSV Reader class available at

http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader

Input is Fixed-Width

If your input is fixed-width, just read all of the lines in and parse each individual line into an appropriate structure to store in the database (more on that in a moment).

If you have just a little text to read (perhaps a few megabytes or less), just use

File.ReadAllLines

http://msdn.microsoft.com/en-us/library/s2tte0y1

to read in all of the lines of the file into a string[].

Writing to the DB

You now have a capability to read in the file. Now, you need to write it out to the database. Presumably there is a DB table with a given schema that matches the data in the file. Have a look at ADO.Net to understand how to write to a database and ask specific questions as needed.

http://msdn.microsoft.com/en-us/library/h43ks021(v=vs.100).aspx

Upvotes: 3

Umesh
Umesh

Reputation: 2732

This sounds like you have to have the text file with delimiter. The delimiter which separates the data into columns, e.g.

data1, data2, data3, data4

The delimiter could be comma or any other character which is not appearing into regular data. If you have the text file in this format, it would be easy to parse it and push it to database.

The approach could be - You open the file using StreamReader. Read the file line by line i.e. read a line at a time. Split the line into columns through specifying delimiter.

string[] lineData = sr.ReadLine().split('delimiter');
foreach(string colData in lineData)
{
     //store data into appropriate collections and push it to database
}

Upvotes: 0

Related Questions