Opening a text file for input

[Tino]

Hi,

I do a lot of creating text files from Excel workbooks using "Open
blahblah for output as #1" and then printing to that file with "Print
#1, "etc"..

I now need to do some input from text files and don't know the syntax
& can't find it in HELP.

Specifically, I have a comma separated text file with an undefined
number of rows but always two fields.

What code would I need to achieve the following..

-Open a text file for reading
-Start a loop that will check line after line until the end of the
file
-Take both fields of a line of data (comma separated) and put each
field in a separate variable
-end the loop

I expect it would be something like this...(and I do know this does
nothing at the moment. I will be doing further manipulation of the
variables & dumping to another file later).

Open "c:\myfile.csv" for input as #1
Do while not EOF(myfile.csv)
read myfirstfield,mysecondfield
loop


Any help would be much appreciated.

Thanks.

 

[Tino]

Thanks Rob!

Try this

Open "c:\myfile.csv" for Input as #1
Do until EOF(1)
Input #1,myfirstfield,mysecondfield
Loop

Close #1

 

[RWN]

Try this

Open "c:\myfile.csv" for Input as #1
Do until EOF(1)
Input #1,myfirstfield,mysecondfield
Loop

Close #1

 

[Tino]

Oops... It's not actually working quite right as it's not using comma
as a delimeter. Some of the values in my first field have spaces and
it appears to be picking up those as delimeters too.
'
Any ideas on how to resolve this?

Thanks

 

[RWN]

Hmmm. Work fine here.
File;
123,456
789,012
"ROB,RWN","Test it"

Dim F1 as String
Dim F2 as String

Sub Testit()
Open "C:\xfer\test.csv" For Input As #1
Do Until EOF(1)
Input #1, F1, F2
Debug.Print F1, F2
Loop
Close #1
End Sub

Out
123 456
789 012
ROB,RWN Test it

Perhaps I'm missing something as to what your file looks like?

 

[Tino]

Thanks for the on-going interest Rob.

Three example lines of my csv file are..

abc 123,123.4
1 0 002,900.23231
1 0 003,31.12

And it appears to recognise the spaces in the lines as delimeters as
well as the commas.

The purpose of my code is to open a text file and round values in the
second field of each line and the full code is as follows:

mycount = 1
Do While Range("a" & mycount) <> ""

a = "c:\web\" & Range("a" & mycount) & ".csv"
b = "c:\web\" & Range("a" & mycount) & ".txt"
Open a For Input As #1
Open b For Output As #2

b = LCase(b)

Do Until EOF(1)
Input #1, myfirstfield, mysecondfield
mysecondfield = (Int(mysecondfield * 100)) / 100
Print #2, myfirstfield & "," & mysecondfield
Loop
Close
mycount = mycount + 1

Loop


Thanks again.