Selecting Columns conditionally?

[Guest]

Good morning all,

Just a quick question - I have a table that looks something like this... The
first row are the field names as stored in access, everything after that is
the data stored in the table.

NodeName T1F1 T1F2 T2F1 T2F2 T3F1 T3F2
NodeName (SG) (AQ) (%) (%) (6) ($)
ABAO .62 39 .69 .01 39.5 867
ABCD .61 39.1 .68 .05 30.1 666

This table is being created by importing a text file. The 1st Record in this
table, contains the "actual" field names. Due to the ugly formatting of the
file as you can see there are some entries that have the same names. The
fields do not always appear in the same order in the text file, and even vary
in number, so I can't create an import specification to set up the fields how
I want them...

The good news is I only need three fields out of this mess - and they do
have unique field names - specifically I need the NodeName, (SG) and (6)
fields. These three columns fortunately do appear in each file.

So what I'm thinking is, is there a way to Find the "Access Field Name", for
the "actual" field I am after, and then just select those columns in a query.

Any assistance will be much appreciated.

Regards

Dave

 

[Pieter Wijnen]

Something like

SELECT * FROM
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[ImportFile#txt]

see also

http://office.microsoft.com/en-us/access/HP010321661033.aspx

HTH

Pieter

 

[Guest]

Hmm, yes I see where you are coming from; although I should have said that
the import is actually coming from several text files to merge together into
one table, rather than just the one. I have a some code in place to parse
through each of those files and merge it into the ugly table in access.

So what I've done, Ive kept my original import routine such that I get the
ugly table I described below, I then have a module using the transfer text
method to dump just the results of that table into a comma delimited text
file on a local disk drive (without the T1F1 field names that I added at
first).

This means I get a text file that reads as follows - where again, the AAAA
row is my field names (some of them are blank - I can't avoid them or else
the data won't line up properly in the table).

"AAAA","(11)","(CV)","(%)","(%)",,"(2)",,,,,,,,,,"(6)","(3)","(14)","(11)","(5)"
"ABAI","61.47","0.6258","1.83","0.13","1.363","2.612",,,,,,,,,,"0.4432","39.82","5.0","---------","0.0"
"ABAO","61.47","0.6258","1.83","0.13","1.363","2.612",,,,,,,,,,"0.4432","39.82","5.0","---------","0.0"
And so on...

I have then attempted to apply the expression you suggested in a query as
follows:

Expr1: SELECT * FROM
[Text;Database=Currentdb();TextDelimiter=",";ColNameHeader=True;Format=CSVDelimited;].[H:\EXPTNGR.txt]

However this is producing a a syntax error.

Can you see where I'm going wrong?

Thanks!

Dave

Something like

SELECT * FROM
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[ImportFile#txt]

see also

http://office.microsoft.com/en-us/access/HP010321661033.aspx

HTH

Pieter

Good morning all,

Just a quick question - I have a table that looks something like this...
The
first row are the field names as stored in access, everything after that
is
the data stored in the table.

NodeName T1F1 T1F2 T2F1 T2F2 T3F1 T3F2
NodeName (SG) (AQ) (%) (%) (6) ($)
ABAO .62 39 .69 .01 39.5 867
ABCD .61 39.1 .68 .05 30.1 666

This table is being created by importing a text file. The 1st Record in
this
table, contains the "actual" field names. Due to the ugly formatting of
the
file as you can see there are some entries that have the same names. The
fields do not always appear in the same order in the text file, and even
vary
in number, so I can't create an import specification to set up the fields
how
I want them...

The good news is I only need three fields out of this mess - and they do
have unique field names - specifically I need the NodeName, (SG) and (6)
fields. These three columns fortunately do appear in each file.

So what I'm thinking is, is there a way to Find the "Access Field Name",
for
the "actual" field I am after, and then just select those columns in a
query.

Any assistance will be much appreciated.

Regards

Dave

 

[Pieter Wijnen]

you have to use # instead of . (myfile#txt, not myfile.txt)
just one of those things <g>

Pieter

Hmm, yes I see where you are coming from; although I should have said that
the import is actually coming from several text files to merge together
into
one table, rather than just the one. I have a some code in place to parse
through each of those files and merge it into the ugly table in access.

So what I've done, Ive kept my original import routine such that I get the
ugly table I described below, I then have a module using the transfer text
method to dump just the results of that table into a comma delimited text
file on a local disk drive (without the T1F1 field names that I added at
first).

This means I get a text file that reads as follows - where again, the AAAA
row is my field names (some of them are blank - I can't avoid them or else
the data won't line up properly in the table).

"AAAA","(11)","(CV)","(%)","(%)",,"(2)",,,,,,,,,,"(6)","(3)","(14)","(11)","(5)"
"ABAI","61.47","0.6258","1.83","0.13","1.363","2.612",,,,,,,,,,"0.4432","39.82","5.0","---------","0.0"
"ABAO","61.47","0.6258","1.83","0.13","1.363","2.612",,,,,,,,,,"0.4432","39.82","5.0","---------","0.0"
And so on...

I have then attempted to apply the expression you suggested in a query as
follows:

Expr1: SELECT * FROM
[Text;Database=Currentdb();TextDelimiter=",";ColNameHeader=True;Format=CSVDelimited;].[H:\EXPTNGR.txt]

However this is producing a a syntax error.

Can you see where I'm going wrong?

Thanks!

Dave

Something like

SELECT * FROM
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[ImportFile#txt]

see also

http://office.microsoft.com/en-us/access/HP010321661033.aspx

HTH

Pieter

Good morning all,

Just a quick question - I have a table that looks something like
this...
The
first row are the field names as stored in access, everything after
that
is
the data stored in the table.

NodeName T1F1 T1F2 T2F1 T2F2 T3F1 T3F2
NodeName (SG) (AQ) (%) (%) (6) ($)
ABAO .62 39 .69 .01 39.5 867
ABCD .61 39.1 .68 .05 30.1 666

This table is being created by importing a text file. The 1st Record in
this
table, contains the "actual" field names. Due to the ugly formatting of
the
file as you can see there are some entries that have the same names.
The
fields do not always appear in the same order in the text file, and
even
vary
in number, so I can't create an import specification to set up the
fields
how
I want them...

The good news is I only need three fields out of this mess - and they
do
have unique field names - specifically I need the NodeName, (SG) and
(6)
fields. These three columns fortunately do appear in each file.

So what I'm thinking is, is there a way to Find the "Access Field
Name",
for
the "actual" field I am after, and then just select those columns in a
query.

Any assistance will be much appreciated.

Regards

Dave