Handling CSV Files

[Nigel]

I have a CSV file that contains text data extracted from a
main systems server around 3,000 rows. The data can have
the values illustrated below......

Field1,Field2,+Next,LastField

My problem concerns the +Next value. When I open this is
Excel the +Next is treated as a formula, and it tries to
associate with a named range (presumably called Next).
The cell displays #NAME? since this is clearly not valid
or intended. Subsequent processing of the file causes an
error.

So far my ideas are.....
Load the CSV and trawl through it looking and replacing
text values begining with a + sign (I guess in the general
case I would need to include the - sign and the = sign)
with the text equivalent, I do not want to loose the +
sign from the text.

Or

As I process each record / field I test for this condition
and adjust the value accordingly.

I believe the 2nd option would be preferrable and faster,
since I might only have this situation very rarely.

Can I ask for comments on the proposed solutions or
alternatives if there is something more appropriate.

TIA
Nigel

 

[J.E. McGimpsey]

one way:

If you use the Text Import wizard (you may need to rename the .csv
extension to .txt) you can, in the third tab, choose the third
column and select the Text radio button. Then XL will not try to
parse that column.

You could use a macro as well:

Public Sub readTextFile()
Workbooks.OpenText _
FileName:="<your path><your file name>.txt", _
DataType:=xlDelimited, _
comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 2), Array(4, 1))
End Sub

 

[Nigel]

Thanks, I have now incorporated the approach and we are in
business again!
Cheers
Nigel