C# Help (Processing multiple txt files from a single file)

Soldato
Joined
10 Sep 2008
Posts
2,570
Location
Grendon
Hi Guys, out of ideas on how to fix my issue, so any help would be greatly appreciated. Lucky for me my manager doesn't know i'm attempting this so he isn't expecting it to be done (HA!)

I'll try and explain everything first....

Our ERP system (Once an order is dispatched) sends a text file with all the information to a network folder. Our courier providers software then takes this txt file, and a consignment number to it and prints a label.

What I am trying to do, is a SQL call to get all the orders from the day put them into a text file (works fine) and then open these order txt files to extract the consignment number from them, unfortunately the text files are the order number (6 characters) but out order numbers are actually 7 characters, so up to 9 orders can be in a single text file.

Here is the code I have so far...

Code:
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string dbFile = @"C:\temp\SQL_Output.txt";

            writeFileFromDB(dbFile);
        }

        public static void writeFileFromDB(string dbFile)
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = new SqlConnection(@"Server=sername; database=databasename; User id=user; Password=pwd");

            String sql = @"SELECT order_no FROM scheme.opheadm WITH (NOLOCK) WHERE RTRIM(appearance) = '' and date_despatched =  dateadd(dd, datediff(dd, 0, getdate()), 0)";

            comm.CommandText = sql;
            comm.Connection.Open();

            SqlDataReader sqlReader = comm.ExecuteReader();

            using (StreamWriter file = new StreamWriter(dbFile, false))
            {
                while (sqlReader.Read())
                {
                    //THIS WORKS FINE, ALL THE ORDER NUMBERS ARE DISPLAYED IN THE TEXT FILE
                    file.WriteLine(sqlReader["order_no"]);
                }
            }

            sqlReader.Close();
            comm.Connection.Close();

            string[] lines = File.ReadAllLines(dbFile);

            foreach (string line in lines)
            {
                string fullOrder = line;
                //BECAUSE THE TEXT FILES ARE ONLY 6 CHARACTERS I TAKE THE FIRST 6 CHARS AND ADD .TXT TO IT
                string orderFile = line.Substring(0, 6) + ".txt";
                string orderPath = @"\\NETWORK\LOCATION\";
                string readOrder = orderPath + orderFile;

                if (File.Exists(readOrder))
                {
                    StringBuilder sb = new StringBuilder();
                    List<string> list = new List<string>();

                    using (StreamReader sr = new StreamReader(@readOrder))
                    {
                        while (sr.Peek() >= 0)
                        {
                            list.Add(sr.ReadLine());
                        }
                    }
                    for (int i = 0; i < list.Count; i++)
                    {
                        foreach (string orderNumber in lines)
                        {
                                //THE FILE IS PIPE DELIMITED AND IS SET OUT LIKE
                                //ORDERNUMBER|CONSIGMENTNUMBER|OTHERSTUFF|ETC|ETC|ETC
                                //AND CAN HAVE MULTIPLE LINES
                                string[] strlist = list[i].Split('|');

                                Console.WriteLine(strlist[0]);

                                //CHECK TO SEE IF THE ORDER NUMBER MATCHES THE ORDER NUMBER FROM THE SQL CALL
                                if (strlist[0] != fullOrder)
                                {
                                    Console.WriteLine("Order Number: " + strlist[0]);
                                    Console.WriteLine("Consignment Number: " + strlist[1]);
                                }
                        }
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

Problem I get is I will get loads of multiples, like 20 of the same, then 20 of the next etc. etc.

For Example:

console.png


Any Ideas? :(

*EDIT*

If I remove the

foreach (string orderNumber in lines)

It does them singly, but seems to be missing a hell of a lot, ill check now just how many it is missing.

*EDIT* Yep way off..

SQL output displays 419 order numbers
Yet the data extract finds only 28, yet I know the other files are there =\



LOL ok it does actually work fine. Reason it only gets 28, is because the odd file has a line that is TAB delimited instead of pip, so the app stops running. is there anyway to ignore a line if it's NOT pipe delimited?
 
Last edited:
Associate
Joined
16 Aug 2010
Posts
1,365
Location
UK
Yeah does look like you had one too many loops there possibly. May be better to extract what you need too through regex expressions.

Are you able to give me a text file to use and fix it with that, or is it secret info?
 
Last edited:
Soldato
OP
Joined
10 Sep 2008
Posts
2,570
Location
Grendon
NVM I added

if (list.Contains("|"))

Been struggling with this all day, amazing how when you type it out to explain things the answer comes to you :)
 
Associate
Joined
16 Aug 2010
Posts
1,365
Location
UK
LOL ok it does actually work fine. Reason it only gets 28, is because the odd file has a line that is TAB delimited instead of pip, so the app stops running. is there anyway to ignore a line if it's NOT pipe delimited?

Can check last character of the string per iteration, or pull out from the list only those pipe delimited then loop through those.

Bit of regex would make it much more robust, concise and easier :p.
 
Soldato
OP
Joined
10 Sep 2008
Posts
2,570
Location
Grendon
Not secret info at all, but I "think" I have it now. i'll check over a decent amount of them to double check, but it looks good.

Will just have to add an insert to put the data into SQL :)
 
Soldato
OP
Joined
10 Sep 2008
Posts
2,570
Location
Grendon
What would be the best way to insert them?

I need to add strlist[1] to apperance where it matches the order_no in scheme.opheadm

Something like: String sqlInsert = @"INSERT into scheme.opheadm (appearance) VALUES (@strlist[1]) WHERE order_no = '@strlist[0]'";

??
 
Last edited:
Associate
Joined
16 Aug 2010
Posts
1,365
Location
UK
Format the string to insert your variables of strlist[0] etc into the string with the String.Format method.

PHP:
var sqlInsert = String.Format(@"INSERT into scheme.opheadm (appearance) VALUES ({0}) WHERE order_no = ''{1}";",strlist[1], strlist[0]);

I'd put a breakpoint after the string to make sure it's formed correctly and nothing's been left out.

I put var too as it can auto deduce the types in many cases. Just a little time saving tip :p.
 
Last edited:
Soldato
OP
Joined
10 Sep 2008
Posts
2,570
Location
Grendon
Ah ok, i'll give that a try :) Thank you

seemed to be some errors with that. will this work?

PHP:
String sqlInsert = String.Format(@"INSERT into scheme.opheadm(appearance) VALUES({ 0}) WHERE order_no = ''{ 1} ", strlist[1], strlist[0]);
 
Associate
Joined
16 Aug 2010
Posts
1,365
Location
UK
Probably need to deal with the quotes you need in the sql statement.

Edit - Infact probably better to use string interpolation which is new-ish and nicer. I wasn't sure how it worked with the @ sign before a string but I had a quick play in VS with what I thought it should be. I'd check your quotes still incase I've left anything out, been a while since I dealt with raw sql (thanks ORMs :p).

PHP:
var sqlInsert = $@"INSERT into scheme.opheadm (appearance) VALUES ({strlist[1]}) WHERE order_no = '{strlist[0]}';";
 
Last edited:
Back
Top Bottom