Please teach me how to handle the text file ( clock in-out )

Question

ID DD MM YYYY Hour : Minute (In/Out)
0000042003 27 08 2008 09 38 I
0000042003270820080938I
0000042003270820080939O

The data above I got from swipe card time machine for employee clock in-out .

How can I import to database ( I expect to use the sql server 2008 )

and how to the table should be.

Programmer please teach me please.

natchaya khamkuna · Answer

Thank you all expert. I appreciated. I'll try to do as your advice.

Sam Hobbs · Answer

As I said there are many ways to do it and OLEDB is another possibility as Joao Kiewel says. I assume that OLEDB does not provide many options for handling exceptions. Sure it is advantageous to not have to hastle with the details of exceptions but sometimes good software must deal with exceptions. It depends on how reliable the data is and how critical it is if there are errors. So sure avoiding exceptions is desirable but if the data causes an exception then it is also desirable to deal with it in an appropriate manner.

Joao Kiewel · Answer

If the data has defined field sizes its possible to open the data as an OleDb(Jet) database by creating a schema.ini file in the same directory. You can find more info on how to create the schema.ini in http://msdn.microsoft.com/en-us/library/ms709353.aspx . The connectionstring for it is as follows Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:	xtFilesFolder\; Extended Properties = text;HDR=Yes;FMT=Fixed; The main advantage of the schema.ini file is that you don't need to define these fields in your application and avoid many unnecessary exception treatment.

Sam Hobbs · Answer

There are many ways to do it. The following is a sample of one way to read the input. There is a lot more to be done; you need to learn how to use databases. Start by reading some of the articles in this web site about database programming and then when you have specific questions ask for help with that in a new thread in one of these forums. For the database part you probably want to use ADO .Net so you should ask questions about that in the ADO .Net forum. using System; using System.IO; namespace _125812 { class Program { static void Main(string[] args) { String Buffer; String id; int year month day hour minute; DateTime dt = new DateTime(); char io; // you must change the filename in the StreamReader using (StreamReader sr = new StreamReader(@Data.txt)) { while (!sr.EndOfStream) { Buffer = sr.ReadLine(); id = Buffer.Substring(0 10); day = ConvertNumber(Buffer.Substring(10 2)); month = ConvertNumber(Buffer.Substring(12 2)); year = ConvertNumber(Buffer.Substring(14 4)); hour = ConvertNumber(Buffer.Substring(18 2)); minute = ConvertNumber(Buffer.Substring(20 2)); io = Buffer[22]; dt = new DateTime(year month day hour minute 0); Console.WriteLine({0} {1} {2} id dt io); } } } static int ConvertNumber(String text) { int i; // Int32.TryParse returns true if the text was converted to a number if (Int32.TryParse(text out i)) return i; Console.WriteLine(Invalid data: {0} text); return 0; } } }

natchaya khamkuna · Answer

1. The space I made it to show that meaning above to easy understand. Actually the data is no space. 2. I would like to import by coding.

Sam Hobbs · Answer

The first thing that we need to know is if you altered the data. I see that the first record has a space between the fields but the other two records do not have any spaces. It really helps to be specific about things like that. The next thing to know is if you can use a SQL Server utility to import the data or if it needs to be done in a program.