readout textfiles to table 1

[wmbb]

I have several text files like this:

[Vector]
flStageXPosition = 30522000
flStageYPosition = -20428000
SpecimenRotation = 0
SpecimenTilt = 0
Magnification = 300.000
HighTension = 10000.000
FWD = 11.997

I want to read specific data out off the textfiles and create a table with the info like this:

<filename> <magnification> <HighTention>
sample1.txt 300.000 10000.000
sample2.txt 500.000 10000.000

Has anyone an idee how to do this ?

 

[TomCologne]

Is this about importing into Access?

This should have been posted in the Access Modules (VBA Coding) forum.

I don't have enough time now to give you a code example but you might want to look into the "FileSystemObject" in VBA Help to get you started.

TomCologne

 

[lestatdelioncourt]

u can do this in VBA, or with a macro.

If u want to do it manually just import the file into excel, select the column and copy/ paste transpose.

This will convert the column into a row.

Here is the macro to do one file (called:tran001.txt)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 18/09/2007 by Microsoft
'

'

Workbooks.OpenText Filename:="C:\tran001.txt", Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
Sheets.Add
Sheets("tran001").Select
Range("A2:A8").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("tran001").Select
Range("B2:B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub

 

[TomCologne]

Here's a code sample for Access:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' References:
'' MS DAO 3.6
'' MS Scripting Runtime
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ReadTxtImport()
On Error GoTo Err_ReadTxtImport

Dim fs As FileSystemObject, fd As Folder, fc As Files, f As File, ts As TextStream '' Variables for FileSystemObject
'Dim rst As DAO.Recordset
Dim strFolder As String, strFile As String
Dim strLine As String, strLineReturn As String
Dim strMagnification As String, strHighTension As String

strFolder = "C:\TextFiles\"

Set fs = CreateObject("Scripting.FileSystemObject")
Set fd = fs.GetFolder(strFolder)
Set fc = fd.Files

    For Each f In fc

        Set ts = f.OpenAsTextStream(1, -2)
        'Set rst = CurrentDb.OpenRecordset("tblTextFiles")
        
            Do While ts.AtEndOfStream <> True
                strFile = f.Name
                strLine = ts.ReadLine
                strLineReturn = Mid(strLine, InStr(strLine, "=") + 1)

                        If Left(strLine, 13) = "Magnification" Then
                
                            strMagnification = strLineReturn

                        ElseIf Left(strLine, 11) = "HighTension" Then
        
                            strHighTension = strLineReturn
                            Debug.Print strFile, strMagnification, strHighTension
'                            With rst
'
'                                .AddNew
'
'                                !FileName = strFile
'                                !Magnification = strMagnification
'                                !HighTension = strHighTension
'
'                                .Update
'
'                            End With
'
                        End If
                

           Loop
    Next

Exit_ReadTxtImport:

ts.Close
'rst.Close

'Set rst = Nothing
Set ts = Nothing
Set fc = Nothing
Set fd = Nothing
Set fs = Nothing

Exit Sub

Err_ReadTxtImport:

Debug.Print Err.Number, Err.Description

End Sub

TomCologne

 

[wmbb]

Thanx for all your help.
I'll play around with your suggestions and see how it works (or not)

CU

 

[wmbb]

Hello TomCologne,

I will try to do this in acces as you suggested.
How do I import the code in acces.
I've created a new module with this code but while running this module an error occurs:

fs As FileSystemObject

Compile error
User-defined type not defined.

 

[TomCologne]

You need the "Microsoft Scripting Runtime" reference.

In the VB Editor: Tools > References

TomCologne

 

[wmbb]

Thanx, the error is gone but nothing happens.
I'm no expert in acces so what excactly do I have to do ?
Do I need to create a table.
Do I have to import the code in a module or macro ?
Where do I run the code ?
Do I need delete the comment mark ' before the lines ?

I have I good feeling about this script, so can you please help me !

Thanx in advance

 

[TomCologne]

Yes, the code assumes an existing table "tblTextFiles" with 3 text fields, "FileName", "Magnification", "HighTension".

And you have to uncomment the recordset lines, "rst". Make sure that you have the Microsoft DAO reference or you're going to get an error.

In case you want to see what the code does:

In the VB Editor, go to "View", click the "Immediate Window" and the "Locals Window".

Then set two breakpoints: click on the gray bar next to the lines "Loop" and "Next"

In the "Immediate Window" type: ReadTxtImport
and hit Enter.

The code will run and stop at the first "Loop" and you'll see:

strFolder = "C:\TextFiles\"
strFile = "sample01.txt"
strLine = "[Vector]"
strLineReturn = "[Vector]"
strMagnification = ""
strHighTension = ""

Hit the F5 key to continue. Once the 2 If conditions are met
the line "Debug.Print strFile, strMagnification, strHighTension" will show the values in the "Immediate Window".

"Debug.Print" is for testing in VBE, you could replace it with "MsgBox".

To run the code in an Acc application you could eg put it behind the "On Click" event of a button on a form.

I hope that makes it a little clearer for you, let me know how it works.

TomCologne

 

[TomCologne]

I just realized that the returned values will have a leading space, so change these 2 lines:

strMagnification = Trim(strLineReturn)

strHighTension = Trim(strLineReturn)

TomCologne

 

[wmbb]

He, Thanx a lot !!!
It works fine...

It saves me a lot of time.
I will modify this script for more applications.

One question left...
Now I run the script from a module in the VB-editor.
Is there an other way to run the script by defining a button for example ?

 

[TomCologne]

Put a button on a form, cancel the wizard. In design view right click on the button > Properties.

Event tab > On Click > Event Procedure

Once you've selected it, click the ... button, VBE opens to something like:

Private Sub Command1_Click()
[i]ReadTxtImport[/i]''insert
End Sub

TomCologne

 

[HarleyQuinn]

Just another thought to go along with Tom's full answer. The text files you're reading from look like config files, if they are then rather than looping through the textstream and having several if statements you can retrieve the exact values you're after using the GetPrivateProfileString API call. An example would be something like:

Option Compare Database

Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long

Private Sub Command0_Click()
Dim strReturn As String * 255
Dim lngLen As Long
Dim strMag As String

    strStartPath = "C:\TextFiles\sample1.txt"

   lngLen = GetPrivateProfileString("Vector", "Magnification", "" & "", strReturn, 254, strStartPath)

   strMag = Left$(strReturn, lngLen)

   msgbox strMag

End Sub

This may seem like overkill for two values but it's not a bad function to be aware of

Please note this isn't meant to replace Tom's code, but simply is another alternative for extracting the data from the file found using the code he provided.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[TomCologne]

Harley,

Your code is certainly cool but I don't get any results. Could you shed some light, please?

TomCologne

 

[HarleyQuinn]

Tom,

No problem at all.

Configuration files (and wmbb's text files by the look of it), are split into sections (denoted with brackets []). Within these sections are keys, which also have a correspondong value associated with them. To use what we have as an example:

[b]Section - [/b][Vector]
[b]Key - [/b]Magnification
[b]Value - [/b]300.000

The function basically takes the section and key parameters (and the location of the file) and returns a value (type long) with the length of the found value (the return will be 0 if the Section, Key, Value or File aren't found)

So, they're the parameters that tell the function what you're looking for and where it is. The three remaining parameters are your Default Value (in this case "" but for some reason I've got it in as "" & "" but it will still work), your Return Buffer (a fixed length string declared, in this case, as Dim strReturn As String * 255) and the length of the return buffer (254 I've got in this case)

The parameters are passed to the function ByVal (meaning that if the function modifies them then the modified variables will remain modified outside of the scope of the function as well as within it. I'm aware that I'm skimming over the ByVal/ByRef explaination but that's another topic if anyone reading this doesn't understand the difference), and while within the function the Return Buffer is modified to contain the found Value (however it is still 255 in length, more on this later).

We use the Return of the function to determine how many characters where in the found value and link that in with the Return Buffer to return the value (as demonstrated by strMag = Left$(strReturn, lngLen))

Hope that's helped give you a better understanding of the function (sorry if I got too basic ).

The only reason I could think that you'd get no return is that one of Section, Key or File paramters can't be found (and for me it's usually because I've spelled something incorrectly). I say this because I copied the text from wmbb's OP into a text file and then copied the code I'd posted (to make sure I'd not modified it by accident while posting) into a command button and I got the expected msgbox containing the value 300.000.

Further than that, if you still can't get it to work, post back I'll see if there's anything more I can think of to help

Hope this helps


HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[TomCologne]

Thank you very much, Harley,

for that nice explanation. With me and API calls you can't get too basic;-)

BTW, I found the reason why I could not get any result.

I modified the first sample01.txt file like this:

[Vector]
flStageXPosition = 30522000
flStageYPosition = -20428000
SpecimenRotation = 0
SpecimenTilt = 0
Magnification = 300.000
HighTension = 10000.000
FWD = 11.997

flStageXPosition = 30522000
flStageYPosition = -20428000
SpecimenRotation = 0
SpecimenTilt = 0
Magnification = 300.001
HighTension = 10000.001
FWD = 11.997

for testing. Your code worked just fine on the 2. file!

Have a star,

TomCologne

 

[HarleyQuinn]

Tom,

Glad I could help and you got it working

Thanks for the star

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[wmbb]

Thanks for all your help.
I've modified the script and it works !

BUT ....
If i use 20 files it runs perfect
When I use 12 other files I get an overflow error at the 7th file ??!!
Has anyone a suggestion how to solve this problem ?

This the script I'm using now:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' References:
'' MS DAO 3.6
'' MS Scripting Runtime
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ReadTxtImport()
On Error GoTo Err_ReadTxtImport

Dim fs As FileSystemObject, fd As Folder, fc As Files, f As File, ts As TextStream '' Variables for FileSystemObject
Dim rst As DAO.Recordset
Dim strFolder As String, strFile As String
Dim strLine As String, strLineReturn As String
Dim strDetector As String, Magnification As Integer, HighTension As Integer, Spot As Integer

strFolder = "C:\Persdata\Temp\TextFiles\"

Set fs = CreateObject("Scripting.FileSystemObject")
Set fd = fs.GetFolder(strFolder)
Set fc = fd.Files

For Each f In fc

Set ts = f.OpenAsTextStream(1, -2)
Set rst = CurrentDb.OpenRecordset("tblTextFiles")

Do While ts.AtEndOfStream <> True
strFile = f.Name
strLine = ts.ReadLine
strLineReturn = Mid(strLine, InStr(strLine, "=") + 2)


If Left(strLine, 6) = "flSpot" Then

Spot = Val(strLineReturn)

ElseIf (Left(strLine, 8) = "lDetName" And Val(strLineReturn) = 0) Then

strDetector = "SE"

ElseIf (Left(strLine, 8) = "lDetName" And Val(strLineReturn) > 0) Then

strDetector = "BSE"

ElseIf Left(strLine, 13) = "Magnification" Then

Magnification = Val(strLineReturn)

ElseIf Left(strLine, 11) = "HighTension" Then

HighTension = Val(strLineReturn) / 1000
Debug.Print strFile, strDetector, Magnification, HighTension, Spot
With rst

.AddNew

!FileName = strFile
!Detector = strDetector
!Magnification = Magnification
!HighTension = HighTension
!Spot = Spot

.Update

End With
strFile = ""
strDetector = ""
Magnification = 0
HighTension = 0
Spot = 0

End If


Loop
Next

Exit_ReadTxtImport:

ts.Close
rst.Close

Set rst = Nothing
Set ts = Nothing
Set fc = Nothing
Set fd = Nothing
Set fs = Nothing

Exit Sub

Err_ReadTxtImport:

Debug.Print Err.Number, Err.Description

End Sub

 

[wmbb]

FYI

I allready found the problem....

I have set the datatype for magnification to integer while the value was 50000 !
Changed the type to long and it worked again..