CODEWITHSUNDEEP
c#mysqlsql
Smail
Smail

Reputation: 41

C# - Insert first colum from text file to database

I would like to read from text file and insert his first column as row in database.

This is the text file how it looks like:

1264311 | SMS_DR | DELIVERED
 373414 | SMS_DR | EXPIRED
    418 | SMS_DR | UNDELIVERABLE
1628984 | SMS_MT |

I want to get only the first column with integer values and insert them in my database as a row to have this:

DELIVERED   EXPIRED     UNDELIVERABLE       BLABLA
1264311     373414      418                 1628984

When i run my code it inserts all 4 lines, this my code:

sr1 = new StreamReader("C:\\Users\\adamoui\\Desktop\\Statjbm_20161009.txt");

string[] allLines1 = File.ReadAllLines("C:\\Users\\adamoui\\Desktop\\Statjbm_20161009.txt");

for (int i = 0; i < allLines1.Length; i++)
{                
    string[] column = allLines1[i].Split(new char[] { '|' });
    SqlCommand cmdJBM = new SqlCommand("INSERT INTO dbo.StatJBM_NEW (Noeud, Total_MT, Date, DELIVERED, EXPIRED, UNDELIVERABLE) VALUES (@Noeud, @Total_MT, @Date, @DELIVERED, @EXPIRED, @UNDELIVERABLE)", con);
    cmdJBM.Parameters.AddWithValue("@Noeud", "JBM");
    cmdJBM.Parameters.AddWithValue("@DELIVERED", column[0]);
    cmdJBM.Parameters.AddWithValue("@EXPIRED", column[0]);
    cmdJBM.Parameters.AddWithValue("@UNDELIVERABLE", column[0]);
    cmdJBM.Parameters.AddWithValue("@Total_MT", column[0]);
    cmdJBM.Parameters.AddWithValue("@Date", DateTime.Now.AddDays(-1));
    cmdJBM.ExecuteNonQuery();                
}
con.Close();

Result:

enter image description here

Upvotes: 3

Views: 263

Answers (5)

luxus
luxus

Reputation: 11

U have logical mistake... In your column array are the splits of the current line.

But why you create sr1 = new StreamReader and don't use it ?

File.ReadAllLines is a quick version for small files...but for big Files you can get a OutOfMemoryException.

Try this.

class Program { static void Main(string[] args) {

        //current line from the file
        string line;

        //filereader
        using (var file = new StreamReader("C:\\Users\\adamoui\\Desktop\\Statjbm_20161009.txt"))
        {
            //list for the first columns
            var firstColumnInFile = new List<string>();

            //read every line 
            while ((line = file.ReadLine()) != null)
            {
                //split line
                var items = line.Split('|');
                // add the first column in the list;
                firstColumnInFile.Add(items.First());
            }

            if(firstColumnInFile.Count < 4)
                return;

            SqlCommand cmdJBM = new SqlCommand(@"INSERT INTO dbo.StatJBM_NEW
                                (Noeud, Total_MT, Date, DELIVERED, EXPIRED,
                                UNDELIVERABLE) VALUES (@Noeud, @Total_MT, @Date,
                                @DELIVERED, @EXPIRED, @UNDELIVERABLE)", con);
            cmdJBM.Parameters.AddWithValue("@Noeud", "JBM");
            cmdJBM.Parameters.AddWithValue("@DELIVERED", firstColumnInFile[0]); //first column
            cmdJBM.Parameters.AddWithValue("@EXPIRED", firstColumnInFile[1]); //second column
            cmdJBM.Parameters.AddWithValue("@UNDELIVERABLE", firstColumnInFile[2]); //third column
            cmdJBM.Parameters.AddWithValue("@Total_MT", firstColumnInFile[3]); //...
            cmdJBM.Parameters.AddWithValue("@Date", DateTime.Now.AddDays(-1));
        }
    }
}

Upvotes: 1

Adriano Repetti
Adriano Repetti

Reputation: 67128

First of all opening file with StreamReader and then read content with File.ReadAllLines() is useless (and you keep file in use because you do not dispose StreamReader.)

Assuming that file is well-formed (and not huge to use all process memory) then first read its content:

var allLines = File.ReadAllLines(@"C:\Users\adamoui\Desktop\Statjbm_20161009.txt");

Then strip unwanted fields (just two tokens, we don't need to create 2X strings we do not use):

var allValues = allLines.Select(x => x.Split(new char[] { '|' }, 2)[0]);

Now you need to group lines 4-by-4. I'd first introduce an helper function you may reuse elsewhere (no error checking here):

static IEnumerable<int> Range(int start, int count, int step) {
    for (int i=0; i < count; i += step)
        yield return start + i;
}

Now we can loop through our values as required:

var records = Range(0, allValues.Length, 4).Select(x => {
    return new {
        Delivered = allValues[x + 0],
        Expired = allValues[x + 1],
        // ...
    };
});

Let's also introduce some constants to clean your code:

const string InsertRecordCommand = "INSERT INTO dbo.StatJBM_NEW (Noeud, Total_MT, Date, DELIVERED, EXPIRED, UNDELIVERABLE) VALUES (@Noeud, @Total_MT, @Date, @DELIVERED, @EXPIRED, @UNDELIVERABLE)";

To put everything together (also disposing disposable objects where required):

var allValues = File.ReadAllLines(@"C:\Users\adamoui\Desktop\Statjbm_20161009.txt")
    .Select(x => x.Split(new char[] { '|' }, 2)[0])
    .ToArray();

var yesterday = DateTime.Now.AddDays(-1);
var records = Range(0, allValues.Length, 4).Select(x => {
    return new {
        Noeud = "JBM",
        Delivered = allValues[x + 0],
        Expired = allValues[x + 1],
        Undeliverable = allValues[x + 2],
        TotalMt = allValeus[x + 3],
        Date = yesterday
    };
});

foreach (var record in records)
{
    using (var command = new SqlCommand(InsertRecordCommand , con))
    {
        command.Parameters.AddWithValue("@Noeud", record.Noeud);
        command.Parameters.AddWithValue("@DELIVERED", record.Delivered);
        command.Parameters.AddWithValue("@EXPIRED", record.Expired);
        command.Parameters.AddWithValue("@UNDELIVERABLE", record.Undeliverable);
        command.Parameters.AddWithValue("@Total_MT", record.TotalMt);
        command.Parameters.AddWithValue("@Date", record.Date);

        command.ExecuteNonQuery();                
    }
}

Now maybe it's time to simplify your code. You may want to use a lightweight mapper like Dapper:

var allValues = File.ReadAllLines(@"C:\Users\adamoui\Desktop\Statjbm_20161009.txt")
    .Select(x => x.Split(new char[] { '|' }, 2)[0])
    .ToArray();

var yesterday = DateTime.Now.AddDays(-1);
con.Insert(Range(0, allValues.Length, 4).Select(x => {
    return new {
        Noeud = "JBM",
        Delivered = allValues[x + 0],
        Expired = allValues[x + 1],
        Undeliverable = allValues[x + 2],
        Total_Mt = allValeus[x + 3],
        Date = yesterday
    };
}));

That's all (I do not mention to do not hardcode paths and to use Environment.GetFolder() because I guess this is just an example.) Please consult Dapper documentation for further details about its configuration and conventions, note that without contribs it may also be like this:

foreach (var record in records)
    con.Execute(InsertRecordCommand, record);

Upvotes: 0

patrykgliwinski
patrykgliwinski

Reputation: 316

You need something of this sort:

var delivered = allLines[0].Split(new char[] { '|' })[0];
var expired = allLines[1].Split(new char[] { '|' })[0];
var undeliverable = allLines[2].Split(new char[] { '|' })[0];
....
cmdJBM.Parameters.AddWithValue("@DELIVERED", delivered);
cmdJBM.Parameters.AddWithValue("@EXPIRED", expired);
cmdJBM.Parameters.AddWithValue("@UNDELIVERABLE", undeliverable);

Upvotes: 1

decPL
decPL

Reputation: 5402

Your code makes an insert for each line, using the first column as all params. Probably not what you had in mind. Try this:

var values = allLines1.Split('|')
                      .Select(arr => arr[0])
                      .ToArray();

SqlCommand cmdJBM = new SqlCommand(@"INSERT INTO dbo.StatJBM_NEW
                                    (Noeud, Total_MT, Date, DELIVERED, EXPIRED,
                                    UNDELIVERABLE) VALUES (@Noeud, @Total_MT, @Date,
                                    @DELIVERED, @EXPIRED, @UNDELIVERABLE)", con);
cmdJBM.Parameters.AddWithValue("@Noeud", "JBM");
cmdJBM.Parameters.AddWithValue("@DELIVERED", values[0]);
cmdJBM.Parameters.AddWithValue("@EXPIRED", values[1]);
cmdJBM.Parameters.AddWithValue("@UNDELIVERABLE", values[2]);
cmdJBM.Parameters.AddWithValue("@Total_MT", values[3]);
cmdJBM.Parameters.AddWithValue("@Date", DateTime.Now.AddDays(-1));
cmdJBM.ExecuteNonQuery();

Upvotes: 2

jle
jle

Reputation: 9489

Get rid of the loop and do something like this:

cmdJBM.Parameters.AddWithValue("@DELIVERED", allLines1[0].Split(new char[] { '|' })[0]);

If you know the indexes and exact number of rows that your file will have, you can just put the indexes right into your code.

Upvotes: 1

Related Questions