Importing a formatted .Txt File to a Tbl

[rebelscum0000]

Dear all,

I have a .txt file with the following data:

A:\
C:\
C:\Downloads
C:\Dups V.1.0
C:\DVD APPZ X
C:\DVD Basic_8116
C:\HP PMD P
C:\HP PMD P II
C:\HP PMD S
C:\NO OS 80 G
D:\
E:\

I have a virgin Table called Folders with two fieldnames:
ID - Data Type: Autonumber
FoldersFound - Data Type: Text

I want to import in FoldersFound the data of the .txt file, I do not
know how to do this
In addition, I do not know which method (?) can use (DAO, ADO,
DoCmd.TransferText or ADODB (This is new for me) due my code design

I would like to use the value held in VSFLRT in order to do this task.
My code is in loop so if I can use Print #VOUF, VSFLRT and a add a new
record in my
Virgin table at the same time, maybe the code works, is this possible?

Suggestions ideas or clues are more than welcome... I am lost here!
This is the first time I am going to try to do this...

Thanks in advance
Regards,
Antonio Macias

This is my code

Sub Reading_TxtFile_R()

Dim VOTP As String 'OriginTxtPath
Dim VDTP As String 'DestinationTxtPath
Dim VRTP As String 'RenameTxtPath
Dim VTXN As String 'TxtName
Dim VINF As Integer 'InFile
Dim VOUF As Integer 'outFile
Dim VRET As String 'ReadText
Dim VSFL As String 'SearchFolderLine
Dim VDAS As String 'Documents and Settings
Dim VMSO As String 'MSOCache
Dim VPRF As String 'Program Files
Dim VWIN As String 'WINDOWS
Dim VMYC As Integer 'MyCounter
Dim VSFLR As String 'SearchFolderLineReplace
Dim VSFLRT As String 'SearchFolderLineReplaceTrim

VTXN = "LastSearch"
VSFL = "Search Folder"
VDAS = "C:\Documents and Settings"
VMSO = "C:\MSOCache"
VPRF = "C:\Program Files"
VWIN = "C:\WINDOWS"
VMYC = 0
VOTP = "C:\Program Files\Fineware\hound4\Searches\" & VTXN & ".txt"
VDTP = CurrentDb().Name
VDTP = Left$(VDTP, _
Len(VDTP) - Len(Dir$(VDTP))) & _
(VTXN & ".txt")

'Obtain legal handles
VINF = FreeFile()
Open VOTP For Input As #VINF
VOUF = FreeFile()
Open VDTP For Output As #VOUF

Do Until EOF(VINF) 'Loop until end of file.
Line Input #VINF, VRET 'Read line into variable.

'Then you can read it line by line:
'Do something with the line.
If Left(VRET, Len(VSFL)) = VSFL Then
'Counter
VMYC = VMYC + 1
'Replace
VSFLR = Replace(VRET, VSFL & _
" " & VMYC & ":", "", , , vbTextCompare)
VSFLRT = Trim(VSFLR)
'Exclusion code
If Left(VSFLRT, Len(VDAS)) = VDAS Then
ElseIf Left(VSFLRT, Len(VMSO)) = VMSO Then
ElseIf Left(VSFLRT, Len(VPRF)) = VPRF Then
ElseIf Left(VSFLRT, Len(VWIN)) = VWIN Then
Else
'Write it out
Print #VOUF, VSFLRT
End If
End If
Loop

'Tidy up
Close #VINF
Close #VOUF

 

[rebelscum0000]

Hi Karl,

Have you tried just importing by clicking on menu FILE - Get External Data -

Import?

I just tried it and works, now, I need to know how to do this thought
VBA
I do not want to use the Import text Wizard, I want to do it
Programmatically
If you have more information please post again, if not thanks man now I
have something

Regards,
Antonio Macias


File - Get External Data - Import
File name: LastSearch.txt
Files of type: Text Files
Click Import
Select Delimited
Click Next
Choose the delimiter that separates your fields
Select Tab
Click Next
Where would you like to store your data?
Select in a Existing Table: Folders
Click Next
Click Finish

 

[John Vinson]

I just tried it and works, now, I need to know how to do this thought
VBA
I do not want to use the Import text Wizard, I want to do it
Programmatically

Take a look at the VBA Help for "TransferText". It is the programmatic
implementation of the File... Get External Data... Import... Text
dialog.

John W. Vinson[MVP]

 

[rebelscum0000]

Yes, the file name is going to be always the same but I do not know how
to link the text file with my table (?) Could you please provide me
with an example?
Also How to build an append query for this case?

INSERT INTO Folders
SELECT what?
Where what?

Meanwhile I am going to look at the VBA Help for "TransferText" as John
W. Vinson
Suggested

To be honest I am learning so I will try with both solutions, if I find
a solution LOL

Thanks in advance
Regards
Antonio Macias

 

[John Vinson]

Yes, the file name is going to be always the same but I do not know how
to link the text file with my table (?)

The suggestion was to link to the text file AS a table (a different
table than the local Access table). File... Get External Data... LINK,
and then choose "text" from the "files of type".

This will give you a static link to the external .txt file named in
the link command; if you replace the text file with another of the
same name, or edit its contents, you'll see the new data.

You won't be able to *edit* the linked table but you can certainly use
it in an update query to update the local table; run Append queries in
either direction to read records from the text file into the local
table or vice versa; run queries joining the two...

John W. Vinson[MVP]

 

[rebelscum0000]

WHAT? HEHEHE

TransferText is the solution

Regards,
Antonio Macias