TXT File to MS Access

ElDorado.

We have a manufacturing machine that spits out txt files every so often and we want to read the files and upload them MS Access. Below is a sample of the txt file:

 

[Job]
Line=041
Machine=F/M #59 K155
Description=786201 PP0.5ml 0003-2
PartNumber=0003-2
NominalCycle=68.00
JobStartDate=2/22/2007 11:08:52 AM
StartDate=2/27/2007 11:00:00 PM
StopDate=2/28/2007 1:01:52 AM
Cycles=108
RuntimeInSeconds=7312
DowntimeInSeconds=0
PartsMade=216

 
[Job]
Line=001
Machine=Engel #1 150T
Description=L723 16x100mm Non-ridged Tube
PartNumber=920206
NominalCycle=7.80
JobStartDate=2/19/2007 11:24:10 AM
StartDate=2/27/2007 11:00:00 PM
StopDate=2/28/2007 7:00:00 AM
Cycles=0
RuntimeInSeconds=28800
DowntimeInSeconds=28800
PartsMade=0

A typical file has between 400 and 500 jobs. I was thinking bring them to Excel and write some macros to process the data to look like a table and then have the file linked to Access. Is there an easier way to easily process this data directly to Access I want to be able to select the txt files I want to process (from the same folder).

I was thinking using arrays to load the data and then paste them to brand new Sheet. However, I have not been able to do that. As you can see by now,  I'm not an expert on VB.

Any help is appreciated,

ElDorado.-

Jay Vora


i think u can try by importing the text file into access or try the tools available to convert txt to access

Jay Vora

try

msdn2.microsoft.com/en-us/cakac7e6(VS.80).aspx

its in asp tht may help u.

http://forum.weborum.com/index.php showtopic=3314

www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22100810.html

try this i wil figure out by code/

ElDorado

I like your approach to this problem, but keep in mind that everything before the "=" sign is a header and everything after is the value for each line. Unfortunately, the txt file is not created in a table like format. In other words, for every line you have the header and the value. I guess this is the most difficult part for me to figure out. Any ideas

Thanks,

ElDorado.-

Spidermans_DarkSide

Hi,

 ( Please MARK AS ANSWER if the below program is of any use to you. )

 I've sussed it so far as Excel is concerned.

My program produces a CSV (comma separated value ) text file and for your example it produces>>

041,F/M #59 K155,786201 PP0.5ml 0003-2,0003-2,68.00,2/22/2007 11:08:52 AM,2/27/2007 11:00:00 PM,2/28/2007 1:01:52 AM,108,7312,0,216,
001,Engel #1 150T,L723 16x100mm Non-ridged Tube,920206,7.80,2/19/2007 11:24:10 AM,2/27/2007 11:00:00 PM,2/28/2007 7:00:00 AM,0,28800,28800,0,

 

Save the above text as outputFile.csv from NotePad and load it into Excel to see what i mean.

 It produces 1 blank row + 2 rows of data as you would expect in an Excel Form.

However loading it into MicroSoft Office 2007-Access field 4 and a few others are confused by the dataType for some reason.

Put the line>>

Imports System.IO 'at the top of your code window.>>

Rename your input file to inputFile.txt and put it into C:\ drive then run this>>

The outputFile.csv will appear as an Excel file.

 

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sr As New StreamReader("C:\inputFile.txt")

Dim sw As New StreamWriter("C:\outputFile.csv")

Dim myLine As String

Do While sr.Peek >= 0

myLine = sr.ReadLine

If myLine.Length >= 4 Then

If Mid(myLine, 1, 5) = "[Job]" Then

sw.WriteLine()

End If

If Mid(myLine, 1, 4) = "Line" Then

sw.Write(myLine.Substring("Line".Length + 1) & ",")

End If

If Mid(myLine, 1, 7) = "Machine" Then

sw.Write(myLine.Substring("Machine".Length + 1) & ",")

End If

If Mid(myLine, 1, 11) = "Description" Then

sw.Write(myLine.Substring("Description".Length + 1) & ",")

End If

If Mid(myLine, 1, 10) = "PartNumber" Then

sw.Write(myLine.Substring("PartNumber".Length + 1) & ",")

End If

If Mid(myLine, 1, 12) = "NominalCycle" Then

sw.Write(myLine.Substring("NominalCycle".Length + 1) & ",")

End If

If Mid(myLine, 1, 12) = "JobStartDate" Then

sw.Write(myLine.Substring("JobStartDate".Length + 1) & ",")

End If

If Mid(myLine, 1, 9) = "StartDate" Then

sw.Write(myLine.Substring("StartDate".Length + 1) & ",")

End If

If Mid(myLine, 1, 8) = "StopDate" Then

sw.Write(myLine.Substring("StopDate".Length + 1) & ",")

End If

If Mid(myLine, 1, 6) = "Cycles" Then

sw.Write(myLine.Substring("Cycles".Length + 1) & ",")

End If

If Mid(myLine, 1, 16) = "RuntimeInSeconds" Then

sw.Write(myLine.Substring("RuntimeInSeconds".Length + 1) & ",")

End If

If Mid(myLine, 1, 17) = "DowntimeInSeconds" Then

sw.Write(myLine.Substring("DowntimeInSeconds".Length + 1) & ",")

End If

If Mid(myLine, 1, 9) = "PartsMade" Then

sw.Write(myLine.Substring("PartsMade".Length + 1) & ",")

End If

End If

Loop

sw.Close()

sr.Close()

' I was hoping to open ACCESS as a PROCESS here

' with the csv file created above. Anyone want to help further

End Sub

 

Regards,

S_DS

 

Jay Vora

gr8 work done..this may work for the thread starter...

Spidermans_DarkSide

Jay Vora wrote:

gr8 work done..this may work for the thread starter...

Hi,

Thanks for the compliment.

 It only takes a little thought and knowing how certain file operations work and what CSV files are.

See my latest effort in this thread.>>

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1301718&SiteID=1

 

Regards,

S_DS

 

ElDorado

What happens if there are commas in the "description" field. Also I need to write the filename for every record in column1. For example, the filename is S260207, I need to break that down into 02/26/07,etc,etc,etc. Now this is only to process one file. What about processing more than one. I'd like to dump, let's s,ay 20 txt files in a folder and when I execute the code, I want to select the files I want to process using your code.

Thanks again in advance,

ElDorado.-

Jay Vora

ya u r rght spider..,..i ddnt know CSV concept..

but u seem to b a gudd programmer sir..great going...keep doing good

GOD bless u

Spidermans_DarkSide

Hi,

My code will not be affected by a comma in the description field as it takes everything to the right of the equals symbol "=" for it's output.

<--edit-->

Just thought.....

Actually it would be affected, i'd have to change a comma to say a colon or a semi-colon in the code.

</--edit-->

Are you saying you now want the filename in the outputFile as well as the date, or just the date

Will all the filename names be of the form &$$$$$$ like S260207 Will they all start with the letter S

 If so i could either have you enter a range of dates like 230806 to 310307 for all the files in that range ( and now the clever bit ), make the program ask the user which dates are needed so you can skip certain dates.

An easy way to do the 2nd idea, enter all the dates needed in a textbox on seperate lines or separated by commas. You could even just enter 2301 and 3108 and have a program run through the files for any particular year.

Oh one last thing, do you want separate outputFiles for every inputFile, yes or no

Let me know what you would like please

Which folder are all of these files in,

C:\ or C:\machineFiles or some other path

 I guess the above code was okay for you then

By the way i'm a mature university student ( = ' skint ' ), aged 42, if i'm going to write more code and this is for a company to use, any chance of a PayPal donation please

 

 

Regards,

S_DS

 

ElDorado

Ideally, the code should open a Dialog box and we would select the files. They are always going to be in the same folder. The files always start with the letter "S" then the year (yy) then month (mm) then day (dd) and then one letter (A,B, or C) for example: S070228A.txt, S070228B.txt. The first column (called "When") of the output file should be the input filename excluding the "S" and the extension file. So, for every record read from file S070228A.txt, the first column in the output file should be populated with 070228A and then repeat the same for all selected files. We need just one output file regardless of how many input files we select.

Thanks again for your help.

PEng1

Try this:

Imports System.IO

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myOpenFileDialog As New System.Windows.Forms.OpenFileDialog
Dim Result As DialogResult
myOpenFileDialog.InitialDirectory = "C:\" 'you need to enter the Path of the Directory that the files will be located in here, this will make the OpenFileDialog always load this folder
Result = myOpenFileDialog.ShowDialog()
Try
If Result <> vbCancel Then
Dim sw As New StreamWriter("C:\") 'enter the path to save the file to when you are done
sw.Write("When,Line,Machine,Part Number,Nominal Cycle,Start Date,Stop Date,Cycles,Run Time,Down Time,Parts Made")
For Each FileName As String In myOpenFileDialog.FileNames
Dim MD As New MachineData
MD.OperationDate = Strings.Mid(FileName, 2, 6) 'you will need to change the numbers here to match your path name this looks for the Section of the Path that you want to use as in the When column
Dim myLine As String = ""
Dim sr As New StreamReader(FileName)
Do While sr.Peek >= 0
myLine = sr.ReadLine
If myLine.Length >= 4 Then
Select Case myLine
Case InStr(myLine, "Line") <> 0
MD.Line = myLine.Substring("Line".Length + 1) & ","
Case InStr(myLine, "Machine") <> 0
MD.Machine = myLine.Substring("Machine".Length + 1) & ","
Case InStr(myLine, "PartNumber") <> 0
MD.PartNumber = myLine.Substring("Partumber".Length + 1) & ","
Case InStr(myLine, "NominalCycle") <> 0
MD.NominalCycle = myLine.Substring("NominalCycle".Length + 1) & ","
Case InStr(myLine, "StartDate") <> 0
MD.StartDate = myLine.Substring("StartDate".Length + 1) & ","
Case InStr(myLine, "StopDate") <> 0
MD.StopDate = myLine.Substring("StopDate".Length + 1) & ","
Case InStr(myLine, "Cycles") <> 0
MD.Cycles = myLine.Substring("Cycles".Length + 1) & ","
Case InStr(myLine, "RunTime") <> 0
MD.RunTime = myLine.Substring("RunTime".Length + 1) & ","
Case InStr(myLine, "DownTime")
MD.DownTime = myLine.Substring("DownTime".Length + 1) & ","
Case InStr(myLine, "PartsMade")
MD.PartsMade = myLine.Substring("PartsMade".Length + 1) & ","
End Select
End If
With MD
sw.Write(.OperationDate & .Line & .Machine & .PartNumber & .NominalCycle & .StartDate & .StopDate & .Cycles & .RunTime .DownTime & .PartsMade)
End With
Loop
sr.Close()
Next
sw.Close()
Else
Me.Close()
End If
Catch ex As Exception
MsgBox(ex.InnerException.ToString)
End Try
Me.Close()
End Sub
End Class

Public Structure MachineData
Dim OperationDate As String
Dim Line As String
Dim Machine As String
Dim PartNumber As String
Dim NominalCycle As String
Dim StartDate As String
Dim StopDate As String
Dim Cycles As String
Dim RunTime As String
Dim DownTime As String
Dim PartsMade As String
End Structure

This should do what you want, but seeing as I don't have your files I cant test it so there might be some debuging todo, also there are a few places were you need to enter things specific to your system, they are commented. As before this does not look for commas inside of the text file, you will need to implement that capability if it is needed. The only form that will be displayed is the OpenFileDialog, other than selecting the files this program is completely automated.

I know that the structure is unneccessary, but if you ever decide that you would like to bypass the CSV files and update straight to your Database file this will allow you to do some simple changes instead of revamping the entier program.

Let me know if there is anything else you need and I will see what I can do.

ElDorado

Thanks so much for the code above. I really like it how you have structured it. I could definetely skip over the CSV. The only reason I wanted to do that was b/c I didn't know how to import it directly into MS Access. That would be the ultimate solution. What suggestions do you have for that I need to import the CSV to a MS Access database (Tracker.mdb) table called "Shift" and that's it, but if we could import it directly into Access that would be awesome. I put the code in VB (Excel) and it gave all sorts of errors. Is this code for Visual Studio (I'm not that familiar w/it) I tried to put it as a WinApp Form1 and I still got some errors (Me.Close event can't be fount. Same with the Load event). Any help is highly appreciated. If I could perform this code in Excel I would prefer it. One file contains everything and I just execute the macro. I guess I should have post this in the VBA section.

Thanks,

ElDorado.-

PEng1

Not a problem, I wasn't aware that you were using VBA, that makes things difficult because I don't think that VBA has the OpenFileDialog. Also, I got to thinking last night and realized that this won't do exactly what you want. I will have to make a few updates. Also let me see what I can do about the VBA.

As far as going right to Access, I think that this might be easier in VB or you can write the macro in Access. What version of Office are you using, do you have acces to VB, if not you can Download the Express Version for Free and this code will compile in there.

PEng1

Here is the update, put this in place of the original Do While Loop:

Do While sr.Peek >= 0
myLine = sr.ReadLine
If myLine.Length >= 4 Then
If InStr(myLine, "Job") Then
While myLine IsNot ""
Select Case myLine
Case InStr(myLine, "Line") <> 0
MD.Line = myLine.Substring("Line".Length + 1) & ","
Case InStr(myLine, "Machine") <> 0
MD.Machine = myLine.Substring("Machine".Length + 1) & ","
Case InStr(myLine, "PartNumber") <> 0
MD.PartNumber = myLine.Substring("Partumber".Length + 1) & ","
Case InStr(myLine, "NominalCycle") <> 0
MD.NominalCycle = myLine.Substring("NominalCycle".Length + 1) & ","
Case InStr(myLine, "StartDate") <> 0
MD.StartDate = myLine.Substring("StartDate".Length + 1) & ","
Case InStr(myLine, "StopDate") <> 0
MD.StopDate = myLine.Substring("StopDate".Length + 1) & ","
Case InStr(myLine, "Cycles") <> 0
MD.Cycles = myLine.Substring("Cycles".Length + 1) & ","
Case InStr(myLine, "RunTime") <> 0
MD.RunTime = myLine.Substring("RunTime".Length + 1) & ","
Case InStr(myLine, "DownTime")
MD.DownTime = myLine.Substring("DownTime".Length + 1) & ","
Case InStr(myLine, "PartsMade")
MD.PartsMade = myLine.Substring("PartsMade".Length + 1) & ","
End Select
myLine = sr.ReadLine
End While
End If
End If
With MD
sw.Write(.OperationDate & .Line & .Machine & .PartNumber & .NominalCycle & .StartDate & .StopDate & .Cycles & .RunTime .DownTime & .PartsMade)
End With
Loop