I have a text file that I want to convert into useable data but I have no idea how to do that. the text looks like this:
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Fri Feb 27 12:57:00 2009
The text does run across two rows unless the space between the date is manually deleted.
The key text would be 1: between the speech marks and 2:the date and time.
Can anyone suggest where I begin with this?

Have you tried different import specs such as setting column widths, setting it on a delimter such as a comma, semicolon, tab etc.?

Assuming all rows look like the one you posted, this would fetch the text between the speech marks:

In a query,

SM1: InStr([FieldName], "'")

SM2: InStr([FieldName], SM1 + 1)

TextBetween: Mid$([FieldName], SM1 + 1, (SM2 - SM1) + 1)

The next is probably best done with a user-defined function.

Public Function FindDayAbbr(VariableName As String) As String

Dim intI As Integer
Dim intJ As Integer
Dim strTemp As String
Dim strDayNames(7) As String
Dim intFoundIt As Integer
strDayNames(1) = " Sun "
strDayNames(2) = " Mon "
strDayNames(3) = " Tue "
strDayNames(4) = " Wed "
strDayNames(5) = " Thu "
strDayNames(6) = " Fri "
strDayNames(7) = " Sat "
intFoundIt = 0
For intI = 1 To Len(VariableName) Step 5
strTemp = Mid$(VariableName, intI, 5)
For intJ = 1 To 7
If strTemp = strDayNames(intJ) Then
intFoundIt = intI
Exit For
End If
Next intJ
If FoundIt > 0 Then
Exit For
Next intI

FindDayAbbr = Mid$(VariableName, intFoundIt)

Yes I've tried that but the varaition in the lengths for the field in speech marks make it impossible, also as I said the text goes around two rows for each set of data.

Have you tried different characters like tabs, semicolons, | etc?

Missed the last bit.
now my results look like
9,0,#error
0,12,Fri Feb 27 12
from data
Field1
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Fri Feb 27 12:57:00 2009

Could you upload a sample of the file that you're working with? It would make it easier for Frank and I to assist you with the correct answer.

Can't download a txt file so see below

PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Fri Feb 27 12:57:00 2009
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Fri Feb 27 13:16:57 2009
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Mon Mar 02 09:17:29 2009
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Mon Mar 02 09:18:27 2009
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Mon Mar 02 10:24:20 2009
PROGRAM '57122 cover' RAN AT THIS TIME AND DATE
Mon Mar 02 10:28:09 2009
PROGRAM '2898-20 - TATAMAZ OP1' RAN AT THIS TIME AND DATE
Tue Mar 03 14:59:58 2009
PROGRAM '2898-20 - TATAMAZ OP1' RAN AT THIS TIME AND DATE
Tue Mar 03 15:16:44 2009
PROGRAM '2898-20 - TATAMAZ OP1' RAN AT THIS TIME AND DATE
Tue Mar 03 15:28:24 2009
PROGRAM '2898-20 - TATAMAZ OP1' RAN AT THIS TIME AND DATE
Tue Mar 03 15:39:33 2009

I looked at it briefly and if it's the same way that you C&Ped it then it takes in every other row and the data looks consistent "Program" and the date and time being at the end.

A way that you could approach this is to assign an Auto-ID when you import it then you use "Mod" to get the odd and even rows. The "Program" line will be on the odd #s and the Date will be the evens.

Let's say that you name the table you imported to tblImport. You would then create another table with 2 fields we'll call them "Program" and "RunDate" set both the fields to "Text" type. You'll have to format the date field later, we'll call the table "tblCleanup"

Now in a button's click event place the below code.



This will place the data into 2 separate fields and strip out the unnecessary data from the program portion. As long as your file is in the format that you C&Ped, this should work.

HTH

Hi
Thanks for that.
I'm getting a compile error at
Dim
As New ADODB.Recordset
?Think I've set everytkhing as you indicated.

You're probably just missing a reference. Go to your Module and click on Tools>References and look for "Microsoft ActiveX Data Objects 2.8 Library" and check the box.

Thanks - that gets past that but I am getting a run time error 2147217904 saying no value given for one or more parameteres.
Stopping at - "rst.Open strSQL, Application.CurrentProject.Connection"

Is the strSQL correct? Right tables and all that? If you could upload a stripped down version of your db, I could take a quick look at it. I'm running Access 2003, so it would have to be converted to a previous version.

Converted to 2003 but excuse my ignorance, how do I go about uploading a database to you?

Just zip it and then make sure that you the "Preview my post and/or attach a file" check box checked when you respond. You'll have the option to upload on the next screen.

Attached Zip file as requested thnks

You were missing the "ID" field in your import table and also in tblCleanup you named the field "RunDate" in my sample code it was "RunTime", so just needed to change that in the code.

I'm attaching the database with the fixes. Good luck.

That's done the seperation thanks so much. Hopefully just one more question. The Date field has the last date repeated throughout rather than the relevant date. Any idea why that would be?

Whoops. Forgot to set the Where clause. under the "Else" statement replace it with this.

CurrentDb.Execute "UPDATE tblCleanup " & _
"SET tblCleanup.RunDate = '" & rst.Fields("Field1").Value & "'" & _
"WHERE RunDate Is Null"

Attached latest version of BD to help sort out the strange daate format

Here is the function that takes the second line, extracts the date pieces, then joins them together into a usable Date/Time value.

The date format? The code I gave you will separate the corresponding data into rows. It's all consistent, so you would just need to convert it to a date format.

Just noticed that Frank uploaded something for you to do that very thing. Looks like you're all set.

When I apply Franks function, my query seems to get stuck at the same point with a type missmatch ie:-
IsolateDate = CDate(strMonthDay & " " & strYear & " " & strTime)

Any ideas?

I'm running Access 2003, so I can't open Frank's example. I just made a quick fix to get the date. I added a query that will get the characters to form a date and append to a formatted table where the field is set to date data type.

When you click on the button to import the file, it will also run the query to append to the formatted table. HTH

Can you look at each piece of the calculation, is it getting good values? It is possible that there are some rows that do not fit the pattern that the code is designed to handle.

Frank and Dan
Thanks for your help. We have made a Quality manager very happy with the results.

YW