Dynamically Determine Delimited File Using VBA?

TC · Nov 12 '05, 04:05 PM

Re: Dynamically Determine Delimited File Using VBA?

Most comma-delimited files would seldom contain tabs, IMO. So perhaps you
could say: "if the first line contains any tabs, it is tab delimited,
otherwise it is comma delimited".

Or - since you clearly need the code to do both - why not try an initial
parse of the first 10 lines of each file, using each of the two different
methods, & see how many columns resulted?

For example, say parsing those lines gave a # of columns of 5, 5, 5, 5, 5,
5, 5, 5, 5, 5 assuming tab delimiting, but 0, 3, 6, 0, 0, 3, 5, 1, 1, 0
using comma delimiting. That file is clearly tab delimited! If the results
were reversed, it would be comma delimited. If the results were *mixed*,
then maybe that file is your grocery list!

Of course, if you have control of the process creating those files, you
could just put "TABS!" or "COMMAS!' as the first line in each file.

HTH,
TC

"Ben" <ben@hoffstein. net> wrote in message
news:1bde515c.0 311041505.62450 252@posting.goo gle.com...[color=blue]
> Hi all -
>
> I am having a bit of trouble and thought maybe someone in this group
> could shed some light. Here's the skinny...
>
> I am creating an automated process to import a bunch of text files
> into Access. I want to avoid creating a separate "Spec" for each file
> (there are over 180 files) and instead want to code my own dynamic
> importing rules. So far it's been going fine, except for one item...
>
> I can't figure out a reliable way to determine in VBA whether a given
> text file is comma-delimited, tab-delimited, etc. Obviously there is
> nothing inherent in the file's properties, at least as far as I know.
> Sometimes the extension is the ticket (e.g. "csv" usually means
> comma-delimited), but that's not always reliable, since "txt" could
> mean a million things. The only thing I can think of is to open the
> file via IO and come up with some logic (e.g. if each line contains a
> certain number of commas, then assume the file is comma-delimited),
> but that too sounds like a half-baked method.
>
> I am hoping that I'm just missing something incredibly obvious and can
> benefit from the great minds in this forum. Thanks in advance for any
> ideas.
>
> - Ben[/color]

**Ben** · Nov 12 '05, 04:06 PM

Re: Dynamically Determine Delimited File Using VBA?

Thanks for the advice TC. I think the parsing/testing method might
work, so I'll give that a shot today. It is less efficient than I had
hoped (i.e. having to test each scenario, count the columns, make
final decision, delete other imports), but it's certainly better than
anything I've come up with. One complication is that I'm dealing with
tab-delimited, comma-delimited, semicolon-delimited, and
pipe-delimited possibilities, so I need to run all of those tests for
each file.

If anyone else has ideas, I'm all ears. Unfortunately I don't have
control over the input files, so putting "TABS" or the like inside the
file won't work. But I certainly could code logic that runs this test
for each file initially, records the results, and then uses those
settings going forward.

Thanks again,
Ben

"TC" <a@b.c.d> wrote in message news:<106800062 9.281517@teutho s>...[color=blue]
> Most comma-delimited files would seldom contain tabs, IMO. So perhaps you
> could say: "if the first line contains any tabs, it is tab delimited,
> otherwise it is comma delimited".
>
> Or - since you clearly need the code to do both - why not try an initial
> parse of the first 10 lines of each file, using each of the two different
> methods, & see how many columns resulted?
>
> For example, say parsing those lines gave a # of columns of 5, 5, 5, 5, 5,
> 5, 5, 5, 5, 5 assuming tab delimiting, but 0, 3, 6, 0, 0, 3, 5, 1, 1, 0
> using comma delimiting. That file is clearly tab delimited! If the results
> were reversed, it would be comma delimited. If the results were *mixed*,
> then maybe that file is your grocery list!
>
> Of course, if you have control of the process creating those files, you
> could just put "TABS!" or "COMMAS!' as the first line in each file.
>
> HTH,
> TC
>
>
> "Ben" <ben@hoffstein. net> wrote in message
> news:1bde515c.0 311041505.62450 252@posting.goo gle.com...[color=green]
> > Hi all -
> >
> > I am having a bit of trouble and thought maybe someone in this group
> > could shed some light. Here's the skinny...
> >
> > I am creating an automated process to import a bunch of text files
> > into Access. I want to avoid creating a separate "Spec" for each file
> > (there are over 180 files) and instead want to code my own dynamic
> > importing rules. So far it's been going fine, except for one item...
> >
> > I can't figure out a reliable way to determine in VBA whether a given
> > text file is comma-delimited, tab-delimited, etc. Obviously there is
> > nothing inherent in the file's properties, at least as far as I know.
> > Sometimes the extension is the ticket (e.g. "csv" usually means
> > comma-delimited), but that's not always reliable, since "txt" could
> > mean a million things. The only thing I can think of is to open the
> > file via IO and come up with some logic (e.g. if each line contains a
> > certain number of commas, then assume the file is comma-delimited),
> > but that too sounds like a half-baked method.
> >
> > I am hoping that I'm just missing something incredibly obvious and can
> > benefit from the great minds in this forum. Thanks in advance for any
> > ideas.
> >
> > - Ben[/color][/color]

TC · Nov 12 '05, 04:08 PM

Re: Dynamically Determine Delimited File Using VBA?

I think the "try each method & compare results" approach would work quite
well. It would probably only be a few lines of extra code. The only problem
would be, how to decide which set of values was "better". For example,
5,5,5,5,5 is clearly better than 5,1,2,3,8. But is 5,4,6,5,1 better or
worse than 6,6,1,5,7?

HTH,
TC

Ben <ben@hoffstein. net> wrote in message
news:1bde515c.0 311050711.243dc 440@posting.goo gle.com...[color=blue]
> Thanks for the advice TC. I think the parsing/testing method might
> work, so I'll give that a shot today. It is less efficient than I had
> hoped (i.e. having to test each scenario, count the columns, make
> final decision, delete other imports), but it's certainly better than
> anything I've come up with. One complication is that I'm dealing with
> tab-delimited, comma-delimited, semicolon-delimited, and
> pipe-delimited possibilities, so I need to run all of those tests for
> each file.
>
> If anyone else has ideas, I'm all ears. Unfortunately I don't have
> control over the input files, so putting "TABS" or the like inside the
> file won't work. But I certainly could code logic that runs this test
> for each file initially, records the results, and then uses those
> settings going forward.
>
> Thanks again,
> Ben
>
> "TC" <a@b.c.d> wrote in message news:<106800062 9.281517@teutho s>...[color=green]
> > Most comma-delimited files would seldom contain tabs, IMO. So perhaps[/color][/color]
you[color=blue][color=green]
> > could say: "if the first line contains any tabs, it is tab delimited,
> > otherwise it is comma delimited".
> >
> > Or - since you clearly need the code to do both - why not try an initial
> > parse of the first 10 lines of each file, using each of the two[/color][/color]
different[color=blue][color=green]
> > methods, & see how many columns resulted?
> >
> > For example, say parsing those lines gave a # of columns of 5, 5, 5, 5,[/color][/color]
5,[color=blue][color=green]
> > 5, 5, 5, 5, 5 assuming tab delimiting, but 0, 3, 6, 0, 0, 3, 5, 1, 1, 0
> > using comma delimiting. That file is clearly tab delimited! If the[/color][/color]
results[color=blue][color=green]
> > were reversed, it would be comma delimited. If the results were *mixed*,
> > then maybe that file is your grocery list!
> >
> > Of course, if you have control of the process creating those files, you
> > could just put "TABS!" or "COMMAS!' as the first line in each file.
> >
> > HTH,
> > TC
> >
> >
> > "Ben" <ben@hoffstein. net> wrote in message
> > news:1bde515c.0 311041505.62450 252@posting.goo gle.com...[color=darkred]
> > > Hi all -
> > >
> > > I am having a bit of trouble and thought maybe someone in this group
> > > could shed some light. Here's the skinny...
> > >
> > > I am creating an automated process to import a bunch of text files
> > > into Access. I want to avoid creating a separate "Spec" for each file
> > > (there are over 180 files) and instead want to code my own dynamic
> > > importing rules. So far it's been going fine, except for one item...
> > >
> > > I can't figure out a reliable way to determine in VBA whether a given
> > > text file is comma-delimited, tab-delimited, etc. Obviously there is
> > > nothing inherent in the file's properties, at least as far as I know.
> > > Sometimes the extension is the ticket (e.g. "csv" usually means
> > > comma-delimited), but that's not always reliable, since "txt" could
> > > mean a million things. The only thing I can think of is to open the
> > > file via IO and come up with some logic (e.g. if each line contains a
> > > certain number of commas, then assume the file is comma-delimited),
> > > but that too sounds like a half-baked method.
> > >
> > > I am hoping that I'm just missing something incredibly obvious and can
> > > benefit from the great minds in this forum. Thanks in advance for any
> > > ideas.
> > >
> > > - Ben[/color][/color][/color]

Dynamically Determine Delimited File Using VBA?

Dynamically Determine Delimited File Using VBA?

Comment

Comment

Comment