Importing multiple tables from a single text file

[Ga$Analy$t]

Hi all, if anyone can offer any advice or hints on how best to tackle this issue, i'd be eternally greatful!

I've had a good trawl through the forums and can't seem to find any solutions to this issue...

I have a text file that contains data tables that I want to import into an Access database. The only issue is there is multiple tables in the one file.

My text file looks like this

Results for "FILENAME"

Table1

Name State Power Flow
ABER ON 30 50
ALRE OFF 40 10
etc
.
.
.

Table 2

Name Tag Eff(%) Pres Speed Temp
AAAA RNE 100 55.1 5000 3.5
etc
.
.
.

(there are multiple spaces between each column and the columns are all lined up perfectly)

If there was just one table per text file, then there would be no problem importing the data, but as the tables are all of different widths with different amounts of columns, any of Access import options screws up all the tables...


I was thinking about maybe searching the text file for "Table 1", then "Table 2" and somehow separating the tables first so that they can be imported as separate tables.

Does anyone know of a solution for this type of problem??

I can elaborate further or upload my text file somewhere if it will help..

Thanks in advance!

 

[hollifd]

OK...

I am on my lunch break at work but thought I would give it a try really quick. This might help you with some ideas.

This code will search through your text file and create additional text files named as your table names. You can then import these additional text files into your database.

It is crappy code but I am out of time. Maybe this will work for you or give you or others some ideas.

Public Sub SplitUpFile()

Dim MyPath As String
Dim Hold As String
Dim MyFileName As String
Dim TableName As String
Dim SaveTableName As String
Dim FirstTimeThruLoop As Integer
Dim TableIdentifier As String


MyPath$ = "C:\Temp\"                'Path where master table file is stored
MyFileName$ = "MultipleTables.txt"  'filename containing table data
TableName$ = ""
SaveTableName$ = ""
TableIdentifier$ = "TABLE"          'text that starts new table information in your file

Open MyPath$ & MyFileName$ For Input As #1
While Not EOF(1)
    Line Input #1, Hold$
    If UCase$(Left$(Hold$, 5)) = TableIdentifier$ Then
        Let TableName$ = UCase$(Hold$)
    End If
    
    If TableName$ <> SaveTableName$ Then
        Let SaveTableName$ = TableName$
        If FirstTimeThruLoop = 1 Then
            'Do nothing
        Else
            Close #2
            Open MyPath$ & Hold$ & ".txt" For Output As #2
        End If
    Else
        Print #2, Hold$
    End If
Wend
Close #1, #2
msgbox "Done."

End Sub

Your text file must begin with your table identifier like table1

Good luck. I have to go.

Thanks,

David

 

[Ga$Analy$t]

Thanks a lot for your reply hollifd, my vb knowledge is limited, but i've translated what your code does. It doesn't do exactly what I want, but this will give me a great starting point to solve this problem. Thanks again