Dynamically Determine Delimited File Using VBA?

[Ben]

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

[TC]

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" <be*@hoffstein.net> wrote in message
news:1b**************************@posting.google.c om...

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

[Ben]

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:<1068000629.281517@teuthos>...

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" <be*@hoffstein.net> wrote in message
news:1b**************************@posting.google.c om...

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

[TC]

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 <be*@hoffstein.net> wrote in message
news:1b**************************@posting.google.c om...

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:<1068000629.281517@teuthos>...

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" <be*@hoffstein.net> wrote in message
news:1b**************************@posting.google.c om...

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