Still stuck with importing a delimitted file.

[Tino]

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.

 

[RWN]

Tino;
Dimension your input fields as string.
By not dimensioning them basic assumes that the variables are
"Variants", which means it will decide what format they are.
Thus, when it sees the "1 0" it assumes that it is a numeric (it thinks
the blank is the sign byte).
Here's what I did, using your file.

Dim F1 As String
Dim F2 As String

Sub Testit()
Open "C:\xfer\tino.txt" For Input As #1
Do Until EOF(1)
Input #1, F1, F2
mysecondfield = (Int(Val(F2) * 100)) / 100 *** Told basic that I wanted
it to treat the string "F2" as a numeric value.

Debug.Print F1, F2, mysecondfield
Loop
Close #1

End Sub

And here's what I got

F1 ...............F2..............mysecondfield
abc 123 123.4 123.4
1 0 002 900.23231 900.23
1 0 003 31.12 31.12


--
Regards;
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.

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?

-

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

Thanks Rob!

 

[Tino]

That did the trick.

Thanks a heap for your help.

Tino;
Dimension your input fields as string.
By not dimensioning them basic assumes that the variables are
"Variants", which means it will decide what format they are.
Thus, when it sees the "1 0" it assumes that it is a numeric (it thinks
the blank is the sign byte).
Here's what I did, using your file.

Dim F1 As String
Dim F2 As String

Sub Testit()
Open "C:\xfer\tino.txt" For Input As #1
Do Until EOF(1)
Input #1, F1, F2
mysecondfield = (Int(Val(F2) * 100)) / 100 *** Told basic that I wanted
it to treat the string "F2" as a numeric value.

Debug.Print F1, F2, mysecondfield
Loop
Close #1

End Sub

And here's what I got

F1 ...............F2..............mysecondfield
abc 123 123.4 123.4
1 0 002 900.23231 900.23
1 0 003 31.12 31.12


--
Regards;
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.

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?

-

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