How do I open a txt file?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to import data from a text file and the file name is Baby_Product.txt under C:\My Documents\TDC

I get error says 'C:\My Documents\TDC\Baby_Product.txt' is not a valid path. Appreciate some hint of my mistak. I am using ADO and my code is follows:

====== code starts ========
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\TDC\Baby_Product.txt;"

Set rst1 = New ADODB.Recordset
rst1.Open "Baby", cnn1, adOpenKeyset, adLockOptimistic, adCmdTable

rst1.MoveFirst

====== code ends ========

Many Thanks
 
I read the article and changed my code to method1. Unfortunately I still gets the same error saying that 'C:\My Documents\TDC\' is not a valid path. My code is as below. Appreciate further hint.

Private Sub ImportTDC_Click()

Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim PathtoTextFile As String
Dim PathtoMDB As String

PathtoTextFile = "C:\My Documents\TDC\"
PathtoMDB = "C:\PathtoMDB\"

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
rst1.Open "select * from Baby_Product.txt", _
cnn1, adOpenStatic, adLockReadOnly, adCmdText

rst1.MoveFirst
Do Until rst1.EOF
Debug.Print rst1.GetString
rst1.MoveNext
Loop

End Sub
 
The full error message is as below:

'C:\My Documents\TDC\' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file is resides.

Please help.
 
Unlike a lot of error messages that are rather cryptic, this one is *very*
clear.

You don't have a directory at "C:\My Documents\TDC\" on your computer.

Recheck your path.

I was able to easily duplicate and fix this error my adding and removing
this directory.

Is your "My Documents\TDC\" folder maybe at "C:\Documents and
Settings\YourUserName\My Documents\TDC\"?

Good luck.

Sco

Peter said:
I want to import data from a text file and the file name is
Baby_Product.txt under C:\My Documents\TDC
I get error says 'C:\My Documents\TDC\Baby_Product.txt' is not a valid
path. Appreciate some hint of my mistak. I am using ADO and my code is
follows:
 
I tried the code in the article mentioned by Doug and it accepted the path.
What is your Operating System? C:\My Documents would be a possible location
on a Win9x computer, but not an NT computer unless you've made the directory
yourself.

--
Wayne Morgan
MS Access MVP


Peter said:
The full error message is as below:

'C:\My Documents\TDC\' is not a valid path. Make sure that the path name
is spelled correctly and that you are connected to the server on which the
file is resides.
 
Thanks Douglas, Wayne and Sco. My mistake was that I have to put in the full path just like Sco's suggestion. But I have 2 questions and would like to seek you guys advise.

1. Where can I obtain more detail explanation on the connection parameters, such as Extended Properties, HDR, FMT etc. because I do not understand why the first button display ONE since there are few lines in the TextFile.txt?

2. Is it possible to open the txt file and read the content row by row? I want to open the file, read the first row, perform calculation and read the second row and so on.

Many Thanks
 
A great place for information about ADO connection strings is Carl
Prothman's http://www.able-consulting.com/ado_conn.htm

He shows how to connect to virtually every provider, and has links to all
sorts of different resources.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Peter said:
Thanks Douglas, Wayne and Sco. My mistake was that I have to put in the
full path just like Sco's suggestion. But I have 2 questions and would
like to seek you guys advise.
1. Where can I obtain more detail explanation on the connection
parameters, such as Extended Properties, HDR, FMT etc. because I do not
understand why the first button display ONE since there are few lines in the
TextFile.txt?
2. Is it possible to open the txt file and read the content row by row?
I want to open the file, read the first row, perform calculation and read
the second row and so on.
 
Thanks Douglas. The provided link is very helpful. However I still having problem of retrieving data from the .txt file. I will post another thread to seek for advise.

Cheers
 
Back
Top