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...
Problem I get is I will get loads of multiples, like 20 of the same, then 20 of the next etc. etc.
For Example:
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?
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:
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:
.