Need Help with importing Sequential Text files into MS ACCESS 1

[GigglyGirl]

Hi,

I need help with importing sequential text files that look like this:

Marker: 042xe3

Project: CEPH

Date: Thursday, May 02, 1996

Frequencies:
1) 170 - 0.019
2) 169 - 0.315
3) 168 - 0.167
4) 167 - 0.500

Het: 0.623
PIC: 0.573

Individuals Used: 27 of 133

1331-01: 167 167
1331-02: 167 167

Mutations: none

Mendelian Check: No Mendelian errors


I would like to know or any idea how to put that into a table with automation
Example table that looks like:
Marker Project Het PIC Individuals Used
042xe3 CEPH 0.623 0.573 27 of 133


I will appreciate your help, We could try either in Access or VB6

 

[GDGarth]

I think I do almost exactly what you're talking about. I'm assuming you have a number of files with the above format, and you want to be able to load them all into your database without manually rearranging the data. If that's correct, here's what I do:

Write code that takes the file and puts the fields in the proper order. I like to use comma separated values (.csv) text files, with the field names in the first line.

Set one file up in the proper format, and give it a standard name (something like "Template.txt".

Create a linked table, linked to "Template.txt"

Next, create an append query to put the data from your linked table into your data table.

Now, set up code that looks in the directory where the files are stored, and, one by one, append the data from each file to template.txt.

Run the append query

delete or rename template.txt

You can set this up to run periodically - check for files, reformat and dump into template.txt, run your query, then rename or delete the file.

That's the easiest way to automate data import. I'm sure somebody out there has another way......

 

[GigglyGirl]

Thanks GDGarth it really give me an idea how to start.
But one quetion.. How that code will look like?? How do I wirte that code that can puts the fields in the proper order?? How I can replace all the [vbcrlf] =[Enter] to commas??
I am kind of re-learning all that thing since I didn't use it for long time.

 

[GDGarth]

In this example, the fields are always in exactly the same position, so I just import a line and ignore it if I don't need it.

If your files aren't so uniform, you'll have to search for the label in order to know what you want to capture.

This is in the "Open" event of a form, and it has a time field so it runs early in the morning when nothing else is happening (the form gets opened every minute, all day, as part of another routine).

Keep in mind that the text will wrap to fit this window, so some of the lines will look like two lines, instead of one. Hopefully, you can figure that out.

Also keep in mind I'm not a professional programmer, and I don't write pretty code.

If you want, you can send me a copy of your file, and I can probably write something for you that will put it in the correct order (you may have to wait a day or two, since I'll have to do it at home in my spare time)




Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler

Dim TimeNow As String
TimeNow = Str(Time)

TimeBox.SetFocus
TimeBox.Text = TimeNow
If TimeBox.Text = "4:15 AM" Then


Dim FileName As String
Dim FileDate As String

Dim hold As String
Dim Model As String
Dim Trans As String
Dim ULCode As String
Dim ChargerType As String
Dim ACStart As String
Dim ACStartTolerance As String
Dim T1Spec As String
Dim T1Tol As String
Dim T2Spec As String
Dim T2Tol As String
Dim T3Spec As String
Dim T3Tol As String
Dim T4Spec As String
Dim T4Tol As String
Dim T5Spec As String
Dim T5Tol As String
Dim T6Spec As String
Dim T6Tol As String
Dim T7Spec As String
Dim T7Tol As String
Dim T8Spec As String
Dim T8Tol As String
Dim sp As String
sp = ","







Kill "N:\Tdata99\spec\specfile.txt" 'Deletes old spec file

Open "N:\Tdata99\spec\specfile.txt" For Append As #1
Print #1, "Model" + sp + "filedate" + sp + "Trans" + sp + "ULCode" + sp + "ChargerType" + sp + "ACStart" + sp + "ACStartTolerance" + sp + "T1Spec" + sp + "T1Tol" + sp + "T2Spec" + sp + "T2Tol" + sp + "T3Spec" + sp + "T3Tol" + sp + "T4Spec" + sp + "T4Tol" + sp + "T5Spec" + sp + "T5Tol" + sp + "T6Spec" + sp + "T6Tol" + sp + "T7Spec" + sp + "T7Tol" + sp + "T8Spec" + sp + "T8Tol" + sp
Close #1

FileName = Dir("U:\final\spec\*.*&quot
Do Until FileName = ""

If Right(FileName, 3) = "doc" Then GoTo 100 'checks for Word Doc
If Right(FileName, 3) = "bak" Then GoTo 100 'checks for Word Bak file


Open "N:\Tdata99\spec\specfile.txt" For Append As #1
Open "U:\final\spec\" + FileName For Input As #2


FileDate = Str(FileDateTime("U:\final\spec\" + FileName))



Model = FileName


Line Input #2, hold
Trans = Mid(hold, 10, 10)
Line Input #2, hold 'line 2
ULCode = Mid(hold, 10, 1)
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold 'line 18
ChargerType = Mid(hold, 1, 1)
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold 'line 30
Line Input #2, hold
ACStart = Mid(hold, 1, 7)
ACStartTolerance = Mid(hold, 10, 7)
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold
Line Input #2, hold 'line 43
T1Spec = Mid(hold, 50, 8)
T1Tol = Mid(hold, 60, 8)

Line Input #2, hold
T2Spec = Mid(hold, 50, 8)
T2Tol = Mid(hold, 60, 8)
Line Input #2, hold
T3Spec = Mid(hold, 50, 8)
T3Tol = Mid(hold, 60, 8)
Line Input #2, hold
T4Spec = Mid(hold, 50, 8)
T4Tol = Mid(hold, 60, 8)
Line Input #2, hold
T5Spec = Mid(hold, 50, 8)
T5Tol = Mid(hold, 60, 8)
Line Input #2, hold
T6Spec = Mid(hold, 50, 8)
T6Tol = Mid(hold, 60, 8)
Line Input #2, hold
T7Spec = Mid(hold, 50, 8)
T7Tol = Mid(hold, 60, 8)
Line Input #2, hold
T8Spec = Mid(hold, 50, 8)
T8Tol = Mid(hold, 60, 8)



Print #1, Model + sp + FileDate + sp + Trans + sp + ULCode + sp + ChargerType + sp + ACStart + sp + ACStartTolerance + sp + T1Spec + sp + T1Tol + sp + T2Spec + sp + T2Tol + sp + T3Spec + sp + T3Tol + sp + T4Spec + sp + T4Tol + sp + T5Spec + sp + T5Tol + sp + T6Spec + sp + T6Tol + sp + T7Spec + sp + T7Tol + sp + T8Spec + sp + T8Tol + sp

Close #1
Close #2

100 FileName = Dir
Loop
DoCmd.SetWarnings False

DoCmd.OpenQuery "specificationsDelete"
DoCmd.OpenQuery "Specificationsupdate"
End If

DoCmd.Close acForm, "updatespec"
Exit Sub

ErrHandler:

DoCmd.Close acForm, "updatespec"


End Sub

 

[GigglyGirl]

That is a great example.. Thank you for your help.
You must be a really good programmer.

Thank you

 

[MichaelRed]

Oh, my, ... MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[allanon]

Some people are better typists than others.

 

[GDGarth]

GigglyGirl,

I'm going to print out your response and show it everytime someone makes fun of my ugly, brute-force code!

Hope you get your project worked out.

 

[GigglyGirl]

Hi GDGarth,

Yes please if you could show me how to do it with one file I will appreciate that. But how I can send you a file?? There is an email well I can send you Garth?

 

[GDGarth]

You can email it to me. Please answer the following questions in your email:

Are all your files EXACTLY the same (same number of lines, same position, everything - always) If not, send me a couple that are different, or tell me what is likely to vary.

Do all the files have the label next to the data you want?

I'm running Access 97. If you've got a newer version, I'm sure you can convert it to your version. I'll just put the code in a form, and go ahead and have it dump the data into a table, if you want.

Email it to gdgarth@aol.com

I'll try to get to it over the weekend.