Importing multiple tables from a single text file

Ga$Analy$t

New Member
Joined
Nov 28, 2006
Messages
24
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!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

hollifd

Board Regular
Joined
Apr 3, 2002
Messages
248
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.

Code:
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

New Member
Joined
Nov 28, 2006
Messages
24
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top