Re: Can't seem to use FileSystemObject

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, rhbridg@xxxxxxxxxxxxx, cell 336 382-7313
(hotel) 612 869-7704 xt 118, fax 612 869-7383

/* 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 */

--- <brigitte@xxxxxx> wrote in message
news:1173208054.316647.298850@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: Use a database function without importing the data
    ... Start with a User DSN, add Microsoft Text Driver, supply the info asked for ... having to import it into Excel? ... reference to external data, becuase I don't know how to get my db function ... to reference the CSV file. ...
    (microsoft.public.excel)
  • Re: Save as CSV not saving the same from Excel 2007 then in Excel 2003
    ... I can confirm that xl 2003 saves blank rows as blank rows while xl2007 saves them with commas and seems to add a few rows at the end ... Microsoft Excel MVP ... "Craig" wrote in message ... blank rows from Excel show up as about 15 commas on the CSV file. ...
    (microsoft.public.excel.misc)
  • Re: How do I allow blank columns in a .CSV file?
    ... I am using the csv file to upload data from excel to a website - the website ... "Dave Peterson" wrote: ... not buying much by making sure that there are extra commas in the file. ...
    (microsoft.public.excel.misc)
  • Re: Excel should warn about commas when saving .csv files.
    ... Excel will put double quotes around fields with embedded commas. ... If you open a CSV file, excel will be smart enough to know that you're in one ... This post is a suggestion for Microsoft, ...
    (microsoft.public.excel.misc)
  • Re: How to save text as CSV which also contains commas?
    ... Excel will include double quotes round the contents of such ... and when importing the csv file it will treat the double quotes as ... what about if I want to see the data as an excel sheet. ... Extra commas ...
    (microsoft.public.excel.misc)
Loading