[pwtc222]

Hello,
I have a text file in this format :
02/24/2011 Dynamic List Display 1
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
| ItemNum |Plnt|SLoc|SL|BUn|QTY |Trans./Tfr|Qual.Insp.|Restricted|Blocked|Returns|
---------------------------------------------------------------------------------------------
| 06326-50014-11 |5212|300A| |EA | 1 | 0 | 0 | 0 | 0 | 0 |
| 06650013-0132 |5212|300A| |EA | 1 | 0 | 0 | 0 | 0 | 0 |
| 06650233-0202 |5212|300A| |EA | 2 | 0 | 0 | 0 | 0 | 0 |
| 200-45394-0021 |5212|300A| |EA | 1 | 0 | 0 | 0 | 0 | 0 |
---------------------------------------------------------------------------------------------
|* | | | |EA | 273 | | | | | |
---------------------------------------------------------------------------------------------


can i convert this values to sql server compact database in PC?
i wan the itemnumber and the QTY to be converted to sdf(sqlserver database).

Is this possible?
If yes,how may i do this?

or in second case;
i have a sdf(sql server compact database).i wan to compare the item number in the database with the item number in above text file.if match found,it should list the itemnumber in another report says that itemnumber found.and vice-versa.
is it possible to do comparison between textfile and database,or we need to convert the textfile first to database before we can do the comparison?

which is possible?

any ideas guys?

[eclipsed4utoo]

There is no magic command to turn a text file into a compact database. You will have to do it manually using code.

You can definitely read the data from the file and compare it to data that is already in a database.

[NotarySojac]

Helpful tools:
If you gotta do it manually, use notepad2.exe and make sure Settings -> Show Status Bar is on... This lets you see the column of the line you're currently on, and if you select text, it will count how many characters you have selected. (oops, your style of text data is all variable, dependant upon where the next "|" character is, thus you will do lot's of currentLine.IndexOf("|",whereYouLeftOff); instead of locating data in fixed positions...)

Actually... Access Database and Excel at least, allow you to easily import data like yours, using wizards. You might want to snoop around the user interface for what ever front end you may have handy for SQL. For access, the auto-wizard can be reached through External Data -> (Import) -> TextFile but Access isn't cheap nor does it scale well =/

This post has been edited by NotarySojac: 22 March 2011 - 03:59 PM

[pwtc222]

"You can definitely read the data from the file and compare it to data that is already in a database. "

this is what i'm trying to do..any idea how to do this?sample source code i can refer to?

[NotarySojac]

The algorithm is...
1) setup text reader

2) read first line of source file

3) parse out relevant data on line and store in a dataset (or variables or w/e)

4) go back to 2 until you're out of file

5) Do SQL stuff to save your data to your database of choice

            // the reader that actually parses
            StreamReader mt = new StreamReader(FileInScrutiny);

            for (int i = 0; i < 9; i++)
                mt.ReadLine();    // setup the text reader to the first line of data to read...

            // Parse loop
            while (!mt.EndOfStream)
            {
                stepProg();
                string linebuffer = mt.ReadLine();
                
                int firstdataStartIndex = 0;
                int lengthOfYourFirstData = lineBuffer.IndexOf("|") - firstdataStartIndex;

                string yourFirstData = linebuffer.Substring(firstdataStartIndex,lengthOfYourFirstData);                          
                ...
            }

do you get that? After you set up the StreamReader, everytime you invoke a:

string aString = mt.ReadLine()

call, you read the next line of the text file you've set it up to read. It's fun after you get the hang of it. You should really spend the time familiarizing yourself with coding in this parsing fashion, because later on you'll end up navigating the web through similar algorithms and rooting through all kinds of cool stuff.

This post has been edited by NotarySojac: 22 March 2011 - 07:08 PM

[pwtc222]

Ok,now its reading from the textfile right?
how we can insert the reading values to sql compact database..
do you think it can be easier to compare data?

[NotarySojac]

pwtc222, on 22 March 2011 - 08:12 PM, said:

Ok,now its reading from the textfile right?

Yup

pwtc222, on 22 March 2011 - 08:12 PM, said:

how we can insert the reading values to sql compact database..
do you think it can be easier to compare data?

There's nothing easy about databases bro. I don't know SQL yet, but I've been meaning to learn for a project I've had on the back burner for a while. I do have experience with Access database though. Let me explain to you the gist of how data works, you would be wise to copy this down onto a flow chart to keep handy while you're still in the learning phase.

DataFlow Diagram for Database Storage

DataSet: DataSets live in memory just like other variables and act as sort of the mother ship of database data.

DataTable: DataTables Can live in DataSets. They serve us by storing "tables" of data.

DataColumn: Each DataTable Contains Columns of data.

DataRow: Each DataTable contains Rows of data... Let me show you with a table


DataTable "MyTable":
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
| ItemNum |Plnt|SLoc|SL|BUn|QTY |Trans./Tfr|Qual.Insp.|Restricted|Blocked|Returns|
---------------------------------------------------------------------------------------------
| 06326-50014-11 |5212|300A| |EA | 1 | 0 | 0 | 0 | 0 | 0 |
| 06650013-0132 |5212|300A| |EA | 1 | 0 | 0 | 0 | 0 | 0 |
| 06650233-0202 |5212|300A| |EA | 2 | 0 | 0 | 0 | 0 | 0 |
| 200-45394-0021 |5212|300A| |EA | 1 | 0 | 0 | 0 | 0 | 0 |
---------------------------------------------------------------------------------------------
|* | | | |EA | 273 | | | | | |
---------------------------------------------------------------------------------------------

Ok, the text above that is in bold are the DataColumns. They each have a name. DataColumn[0].Name = "ItemNum" get that?

Then below the columns you have rows of data. If you store this data in a DataTable, they would be called DataRows! All the above text together could be considered a DataTable (contains Columns and Rows).

Now for your reference, draw a diagram with "DataSet" on top, and "DataRow" & "DataColumn" on the bottom... like this...

DataSet
|
DataTable
| . . . .|
DataColumns . DataRows

And make notes about them as you progress in your study.

Next flow chart is getting data into a database

DataSet -> DataAdapter -> Update() (updates it to a database file on harddrive)

Finally, some code:

//  EMPLOYEE table
            string[] employeeColumns = { "P_Id", "EmployeeID", "EmployeeName", "PositionTitle", "OriginDate" };
            string[] employeeColType = { "AUTOINCREMENT", "int", "text", "text", "datetime" };

// I personally made this CreateTable function.  You should make your own functions imo, but can learn a bit from here

// PUT IN A BUTTON OR SOMETHING
            CreateTable("Employees", "EmployeeID", employeeColumns, employeeColType);


DataSet employees = new DataSet();
            employees.Tables.Add("Employees");
            for (int i = 0; i < employeeColumns.Length; i++)
            {
                Type mysystype = (3).GetType();

                switch (employeeColType[i])
                {
                    case "AUTOINCREMENT":
                        {
                            mysystype = (3).GetType();
                            break;
                        }
                    case "int":
                        {
                            mysystype = (3).GetType();
                            break;
                        }
                    case "text":
                        {
                            mysystype = ("blaa").GetType();
                            break;
                        }
                    case "datetime":
                        {
                            mysystype = new DateTime().GetType();
                            break;
                        }
                }
                employees.Tables[0].Columns.Add(employeeColumns[i], mysystype);
            }

            #region add each employee manually...
            employees.Tables[0].Rows.Add(new object[] { null, 1, "Jill" });
            employees.Tables[0].Rows.Add(new object[] { null, 2, "Mark" });
            employees.Tables[0].Rows.Add(new object[] { null, 3, "Frank" });
            employees.Tables[0].Rows.Add(new object[] { null, 4, "Tomb" });
            employees.Tables[0].Rows.Add(new object[] { null, 5, "Chris" });
            employees.Tables[0].Rows.Add(new object[] { null, 6, "Jerry" });
            employees.Tables[0].Rows.Add(new object[] { null, 7, "Alison" });
            employees.Tables[0].Rows.Add(new object[] { null, 8, "Lee" });
            employees.Tables[0].Rows.Add(new object[] { null, 9, "KEVIN" });
            employees.Tables[0].Rows.Add(new object[] { null, 10, "Kate" });
            employees.Tables[0].Rows.Add(new object[] { null, 11, "Kruge" });
            employees.Tables[0].Rows.Add(new object[] { null, 12, "Sink" });
            employees.Tables[0].Rows.Add(new object[] { null, 13, "Philip" });
            employees.Tables[0].Rows.Add(new object[] { null, 14, "Anthony" });
            employees.Tables[0].Rows.Add(new object[] { null, 15, "Dragon" });
            employees.Tables[0].Rows.Add(new object[] { null, 16, "Andy" });
            employees.Tables[0].Rows.Add(new object[] { null, 17, "" });
            employees.Tables[0].Rows.Add(new object[] { null, 18, "Padro" });
            employees.Tables[0].Rows.Add(new object[] { null, 19, "" });

            UploadToDbsClean(employees, "Employees", @"C:\test.mdb");  

            #endregion

// END PUT IN A BUTTON OR SOMETHING END 


// PUT THIS IN YOUR NAMESPACE, perhaps right after FormLoad


internal static void CreateTable(string TableName, string PrimaryKey, string[] DailySalesColumns, string[] DailySalesColType)
        {
            string pstrDB = @"c:\test.mdb";

            #region Paramiter validity checks
            bool fail = false;
            // check if daily sales columns and col types are of equal length
            if (DailySalesColumns.Length != DailySalesColType.Length) fail = true;

            if (fail == true)
            {
                MessageBox.Show("Error in Parameters sent to CreateTable(" + TableName + "...)");
                throw new Exception("Paramiters for CreateTable(...) were inapropriate.");
            }
                    
            #endregion


            //  This creates a brand new table called SampleTable in test.mdb
            string tableName = TableName;
            string[] dailySalesColumns = DailySalesColumns;
            string[] dailySalesColType = DailySalesColType;
            string primaryKey = PrimaryKey;

            if (primaryKey != "")
                primaryKey = ", CONSTRAINT " + tableName + "_PK PRIMARY KEY (" + primaryKey + ")";

            String svlQuery = "CREATE TABLE " + tableName + " ( " + dailySalesColumns[0] + " " + dailySalesColType[0];

            for (int i = 1; i < dailySalesColumns.Length; i++)
            {
                svlQuery += ", " + dailySalesColumns[i] + " " + dailySalesColType[i];
            }
            if (primaryKey != "")
                svlQuery += primaryKey;

            svlQuery += ")";

            // Setup Connection
            OleDbConnection oledbConnection1 = new OleDbConnection();
            oledbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + pstrDB + ";";
            oledbConnection1.Open();

            // Setup Command
            OleDbCommand oledbCommand1 = new OleDbCommand(svlQuery);
            oledbCommand1.Connection = oledbConnection1;

            //Run Command
            try
            {
                oledbCommand1.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                MessageBox.Show("Table " + tableName + " not created because... \n\r" + exp.ToString());
            }
            oledbConnection1.Close();                       // Close connection
        }


        public static bool CreateDB(string pstrDB)
        {
            try
            {
                //MessageBox.Show("Warning!  You had to disable this CreateDB function in order to get it to run on windows 98se, remember?!?  The database test.mdb must not be at the c:\\...");
                Catalog cat = new Catalog();
                string strCreateDB = "";

                strCreateDB += "Provider=Microsoft.Jet.OLEDB.4.0;";
                strCreateDB += "Data Source=" + pstrDB + ";";
                strCreateDB += "Jet OLEDB:Engine Type=5";
                cat.Create(strCreateDB);

                return true;
            }
            catch (Exception)
            {
                //MessageBox.Show("Database file already exists.  This database will be used to store the data.", "dbs already Exists");
                return false;
            }
        }

Oh god... Some of the comments in that code are old from another project and don't actually apply to their current context... Sry it's all I got. Basically look it over, it should compile and should make test.mdb on your hard drive. That's not what you want, but you can see how DataSets work, and you can send DataSets to any database type you want, even XML files. Let me know if something isn't compiling with that

This post has been edited by NotarySojac: 23 March 2011 - 11:14 AM

[NotarySojac]

oops, You need to add a referenece to your project to get that code to work. Add reference to the COM "Microsoft ADO Ext 2.x for dll a..." and you should be good to go. Also do

using ADOX;

[pwtc222]

thanks bro..now i'm able to transfer all the textfile values to sql compact database.
the scenario now is ;
i have a table1 contains :
itemno,serialno,qty
111111 1221 1
111111 1222 1
111111 1223 1
111111 1224 1
111112 2221 1
and table2 (which contains values from the text file) contains :
itemno,Plnt,SLoc,SL,BUn,QTY
111111, 0 , 0 ,0 , 0 , 3
111113, 0 , 0 ,0 , 0 , 4
i want to compare this two tables and produce an output where it will show itemnum not exist in table2,itemnum not exist in table1,total qty,and variance for qty (if same item number's qty not tally)

my report format is something like this in .txt or .csv or .xls

itemnum,table1QTY,table2QTY,QTY variance,item not exist in table1,item not exist in table2
111111 3 3 0 - -
111112 1 0 -1 - yes
111113 0 4 +4 yes yes



any way to do this?could you guide me?

This post has been edited by pwtc222: 23 March 2011 - 08:48 PM

[NotarySojac]

That's a tricky question, you might want to ask in a new thread. I don't have that code, but I have some code that may work similarly to what you will have to do.

private DataTable SearchDatabaseForRows(string SearchPattern, string TableName, string ColumnToSearch, string pstrDB)
        {
            DataSet wasInDataBase = FillNewDataSet(TableName, "*", pstrDB);
            DataTable wasInTable = wasInDataBase.Tables[TableName];
            DataTable hits = wasInTable.Clone();

            for (int i = 0; i < wasInTable.Rows.Count; i++)
            {
                if (wasInTable.Rows[i][ColumnToSearch].ToString().ToLower() == SearchPattern.ToLower())
                {
                    hits.Rows.Add(wasInTable.Rows[i]);
                }
            }

            if (hits.Rows.Count > 0)
                return hits;
            else
                return null;
        }

gl