Import tab delimited multiple files using VBA

Pete

I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.

Feb 17 '07 #1

Tom van Stiphout

On 17 Feb 2007 14:32:21 -0800, "Pete" <pe********@comcast.netwrote:

Don't import them; just attach to them, which automatically creates
the correct columns.

-Tom.

>I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.

Feb 17 '07 #2

Pete

On Feb 17, 6:24 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:

On 17 Feb 2007 14:32:21 -0800, "Pete" <petebea...@comcast.netwrote:

Don't import them; just attach to them, which automatically creates
the correct columns.

-Tom.

I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.- Hide quoted text -

- Show quoted text -

I can not append the files. There is no existing table. I need to
create tables from each new file without knowing the column names or
content.

Feb 17 '07 #3

Bob Quintal

"Pete" <pe********@comcast.netwrote in
news:11*********************@m58g2000cwm.googlegro ups.com:

I need to import multiple tab delimited files for which I do
not have the formats. All files have column headers in the
the line. The files must be dynamic. The only common value
is that the files are text and use tabs to delimit the column
data.

I can not use the File/Import as people using the program do
not have the necessary experience to perform this function.
Therefore, I need to programtically create unique tables for
each text file.

I'd attack this by using the low-level file manipulation
functions in VBA (open #1, get #1) to read each line of the file
into a string variable or one column table, then use a loop to
read the column names, create the table from that (but here
you'll have to guess at the column type), then loop through each
row to find the tabs, writing out the values to the appropriate
column.

It is much too much code to post on a newsgroup.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Feb 17 '07 #4

'69 Camaro

Hi, Pete.

I need to import multiple tab delimited files for which I do not have
the formats.

.. . .

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function.

What sort of magic do you intend to use to read the data type specifications
from the file and create the "dynamic" tables with them?

Therefore, I need
to programtically create unique tables for each text file.

You could link to these text files as tables, but database applications rely
on stable database structures. Otherwise, those database applications
aren't going to be stable -- or very practical.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Pete" <pe********@comcast.netwrote in message
news:11*********************@m58g2000cwm.googlegro ups.com...

>I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.

Feb 17 '07 #5

Pete

On Feb 17, 6:38 pm, "'69 Camaro" <ForwardZERO_SPAM.To.
69Cam...@Spameater.orgZERO_SPAMwrote:

Hi, Pete.

I need to import multiple tab delimited files for which I do not have
the formats.

. . .

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function.

What sort of magic do you intend to use to read the data type specifications
from the file and create the "dynamic" tables with them?

Therefore, I need
to programtically create unique tables for each text file.

You could link to these text files as tables, but database applications rely
on stable database structures. Otherwise, those database applications
aren't going to be stable -- or very practical.

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.comhttp...utors2.htmlfor contact
info.

"Pete" <petebea...@comcast.netwrote in message

news:11*********************@m58g2000cwm.googlegro ups.com...

I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.- Hide quoted text -

- Show quoted text -

If I open a sample txt file using the file/import, the wizard
automatically find the correct structure. The only thing that I need
to set is the Tab and the header indicators.

I need a way to duplicate this function in the code and set the taba
and header indicators. I know I can tell the TransferText to use the
first line as the column headers. However, I can not set the tab
indicator.

Feb 17 '07 #6

Larry Linson

"Pete" <pe********@comcast.netwrote in message
news:11*********************@l53g2000cwa.googlegro ups.com...

On Feb 17, 6:24 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:

>On 17 Feb 2007 14:32:21 -0800, "Pete" <petebea...@comcast.netwrote:

Don't import them; just attach to them, which automatically creates
the correct columns.

-Tom.

>I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

>I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.- Hide quoted
text -

- Show quoted text -

I can not append the files. There is no existing table. I need to
create tables from each new file without knowing the column names or
content.

I don't see where Tom suggested anything about "appending" to an existing
table.

What he was talking about was, on the menu "File" then "Get External Data"
then "Link Table" then browse for the table, then follow the prompts. I just
created a tab-delimited text file, and stepped through the process -- if
that's too complicated for you to teach your users, I'll have to say I've
never, in nearly 50 years in the computer business, worked with users so
"learning disadvantaged".

Larry Linson
Microsoft Access MVP

Feb 19 '07 #7

Tom van Stiphout

On Mon, 19 Feb 2007 02:14:31 GMT, "Larry Linson"
<bo*****@localhost.notwrote:

Plus you can do this programmatically.
-Tom.

>
"Pete" <pe********@comcast.netwrote in message
news:11*********************@l53g2000cwa.googlegr oups.com...

>On Feb 17, 6:24 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:

>>On 17 Feb 2007 14:32:21 -0800, "Pete" <petebea...@comcast.netwrote:

Don't import them; just attach to them, which automatically creates
the correct columns.

-Tom.

I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.

I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each text file.- Hide quoted
text -

- Show quoted text -

I can not append the files. There is no existing table. I need to
create tables from each new file without knowing the column names or
content.

I don't see where Tom suggested anything about "appending" to an existing
table.

What he was talking about was, on the menu "File" then "Get External Data"
then "Link Table" then browse for the table, then follow the prompts. I just
created a tab-delimited text file, and stepped through the process -- if
that's too complicated for you to teach your users, I'll have to say I've
never, in nearly 50 years in the computer business, worked with users so
"learning disadvantaged".

Larry Linson
Microsoft Access MVP

Feb 19 '07 #8