CODEWITHSUNDEEP
c#sqltext-files
Debbie Dippenaar
Debbie Dippenaar

Reputation: 513

Load data from specific lines in a text file into SQL using C#

My problem is simple yet complex. I'm a SQL DB Developer that now has to create an SSIS package with C# code in it. The code needs to do a few things but the components I'm having a problem with are:

  1. Reading the last line in the text file and inserting that line into columns in a SQL table.
  2. Each of the detail rows from the 2nd row to the 2nd last row need to be inserted into another table.

I'm relatively new to C# and have NO idea how to go about this. Can someone please guide me in the right direction?

Upvotes: 1

Views: 1246

Answers (2)

JohnnBlade
JohnnBlade

Reputation: 4327

Try this found it online for you

public static String ReadLastLine(string path)
{
    return ReadLastLine(path, Encoding.ASCII, "\n");
}

public static String ReadLastLine(string path, Encoding encoding, string newline)
{
    int charsize = encoding.GetByteCount("\n");
    byte[] buffer = encoding.GetBytes(newline);
    using (FileStream stream = new FileStream(path, FileMode.Open))
    {
        long endpos = stream.Length / charsize;
        for (long pos = charsize; pos < endpos; pos += charsize)
        {
            stream.Seek(-pos, SeekOrigin.End);
            stream.Read(buffer, 0, buffer.Length);
            if (encoding.GetString(buffer) == newline)
            {
                buffer = new byte[stream.Length - stream.Position];
                stream.Read(buffer, 0, buffer.Length);
                return encoding.GetString(buffer);
            }
        }
    }
    return null;
}

http://social.msdn.microsoft.com/Forums/en/netfxbcl/thread/ff6c07e2-9c36-4490-a989-f24dcff76145

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500055

How big is the file? If you can read it all into memory, it'll make life a lot simpler:

string[] lines = File.ReadAllLines("file.txt");

// Could use lines[lines.Length - 1] but LINQ makes it simpler...
string lastLine = lines.Last();

IEnumerable<string> otherLines = lines.Skip(1).Take(lines.Length - 2);

So that's the "getting the data out of a file" part. For the database part, you haven't really given us enough information to help you. You need to determine how you're going to do database access (straight ADO.NET, LINQ etc) and then read tutorials on that topic. There are plenty around.

Upvotes: 3

Related Questions