Can't seem to use FileSystemObject

[brigitte]

The original problem:
I need a procedure to import a csv file created by a third party
application into an Access database. This file contains fields which
may include commas, and when they do, Access confuses the commas for
field separators, so that a block of text which should all be in one
field ends up in two fields. I've seen by reading through this group
that this is a pretty common problem with csv files.

The solution I came up with:
I noticed Excel, for some reason, always read the file correctly. I
can't explain it, I would have thought it would have the same problem
as Access, but for whatever reason, it reads the file just fine.

Because of that, I thought I could use automation to open the file in
Excel, convert it to a tab delimited file, then import the tab
delimited file in Access. This is complicated by another aspect to the
problem: the file can be over 65,536 lines long.

To solve that problem, I'm in the process of building a procedure that
reads the file and splits it in two when it has too many lines. If
anybody can suggest a better way to handle the third party csv file
and make it behave when imported, I'd be very happy to read about it:
the workaround described above doesn't exacly feel ideal.

Now here's the real problem: I declared a FileSystemObject and
referenced the Microsoft Scripting Runtime library, but the reference
doesn't seem to work. The visual basic editor can't list the
properties and methods for the object, and doesn't recognize the
OpenTextFile method (Typed it in all lowercase, and typed it all since
I can't get a pull-down list from the fs object, and the editor does
not change any letter to uppercase, or give me any cool-tip for the
arguments).

Here's the code I'm using:

*******
Function fnFirstHalf(strFullPath As String) As String

'This function creates the first part of the file specified by
'strFullPath and returns the full path for that file.

Dim fs, fileOrig, fileA As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set fileOrig = fs.opentextfile(strFullPath, False)

End Function
*****

So, hoping for one of two things:

1. Somebody can tell me how to avoid the whole messy thing and make
the csv behave and import correctly

or

2. Somebody can tell me how to make my FileSystemObject work. As
mentionned above, I do have the Microsoft Scripting Runtime library
referenced.

Thanks a bunch to anyone who tries! :D

[Bob Bridges]

1) I'm a little skeptical about this csv situation. It seems to me that any
csv file with fields that contain commas themselves isn't in csv
format...unless, of course, those fields have quotes around them. But I
shouldn't think Access would be confused by them (though I admit I've never
tried it).

Be that as it may, I'm really here about the second part of the question.
2) What message, exactly, are you getting? And since you mention Access and
Excel both, I suppose I'd better ask: Which application are you running
this code in?

Notice that in your code snippet, "opentextfile" has no capital letters; I
take that as a hint that your application didn't recognize it as a valid
method. In Access, the way to import a text file is TransferText; in Excel
it seems to be OpenText (though I've never used it in VBA). So I suspect
the problem isn't the fs object but the method you're trying to use on it.
Does that point you in the right direction?

---
Bob Bridges, rh*****@attglobal.net, cell 336 382-7313
(hotel) 612 869-7704 xt 118, fax [phone]

/* A crooked politician may be faithful to his wife, but a man who deceives
his own wife can't be relied on to deal honestly with the public. We have
been getting this backward lately. We've been assuming that you can have
public virtue without private virtue. -Joseph Sobran, 1998 */

--- <br******@mun.cawrote in message
news:11**********************@8g2000cwh.googlegrou ps.com...

The original problem:
I need a procedure to import a csv file created by a third party
application into an Access database. This file contains fields which
may include commas, and when they do, Access confuses the commas for
field separators, so that a block of text which should all be in one
field ends up in two fields. I've seen by reading through this group
that this is a pretty common problem with csv files.

The solution I came up with:
I noticed Excel, for some reason, always read the file correctly. I
can't explain it, I would have thought it would have the same problem
as Access, but for whatever reason, it reads the file just fine.

Because of that, I thought I could use automation to open the file in
Excel, convert it to a tab delimited file, then import the tab
delimited file in Access. This is complicated by another aspect to the
problem: the file can be over 65,536 lines long.

To solve that problem, I'm in the process of building a procedure that
reads the file and splits it in two when it has too many lines. If
anybody can suggest a better way to handle the third party csv file
and make it behave when imported, I'd be very happy to read about it:
the workaround described above doesn't exacly feel ideal.

Now here's the real problem: I declared a FileSystemObject and
referenced the Microsoft Scripting Runtime library, but the reference
doesn't seem to work. The visual basic editor can't list the
properties and methods for the object, and doesn't recognize the
OpenTextFile method (Typed it in all lowercase, and typed it all since
I can't get a pull-down list from the fs object, and the editor does
not change any letter to uppercase, or give me any cool-tip for the
arguments).

Here's the code I'm using:

*******
Function fnFirstHalf(strFullPath As String) As String

'This function creates the first part of the file specified by
'strFullPath and returns the full path for that file.

Dim fs, fileOrig, fileA As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set fileOrig = fs.opentextfile(strFullPath, False)

End Function
*****

So, hoping for one of two things:

1. Somebody can tell me how to avoid the whole messy thing and make
the csv behave and import correctly

or

2. Somebody can tell me how to make my FileSystemObject work. As
mentionned above, I do have the Microsoft Scripting Runtime library
referenced.

[Tom]

Have you tried Chuck Grimsby's solution at: http://www.mvps.org/access/modules/mdl0057.htm.
It might solve both your issues.

Tom

[brigitte]

On 6 mar, 17:24, "Tom" <[e-mail]:

Have you tried Chuck Grimsby's solution at: http://www.mvps.org/access/modules/mdl0057.htm.
It might solve both your issues.

Tom

I didn't even know this existed.

Thank you Tom, I'll check it out!

Bob:
I can't confirm that the file is a genuine csv. It has the csv
extension and is comma delimited, despite the lack of quotation marks
around text fields.
I'm working in Access, and I would be using automation to make Excel
open the file (because for unexplainable reasons, Excel reads it
properly and Access does not).

I haven't got as far as getting an error message. The editor doesn't
recognize the object or the function, so I'm assuming something along
the line of "undefined function". Is there any instance of the editor
not recognizing a function like that but still running it fine?

I was avoiding using TransferText on the csv because since Access does
not read the file properly, I was reluctant to put it into a table (I
would have extra fields for some records, which I'm afraid would
complicate things). Unless I'm reading the help file wrong and
TransferText doesn't put the data in a table?

OpenTextFile is an alternate way of reading a file, this time line by
line and as straight text, that I found in the help of the visual
basic editor. I've seen it refered to in this group when I searched
for it, but I can't honestly tell you whether it works in Access 2003.
All I know is that it's not working for me, thus my initial post.

I hope this clears things up, thanks for your reply. I'm going to
check Chuck Grimsby's solution and I'll post again if that doesn't
help.