Selecting Columns conditionally?

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
0
Utf
9/28/2007 11:09:00 AM
access 16762 articles. 3 followers. Follow

3 Replies
797 Views

Similar Articles

[PageSpeed] 28

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


"Binraider" <Binraider@discussions.microsoft.com> wrote in message 
news:45B20605-6DF2-4BE7-B945-D3733EDCFAA9@microsoft.com...
> 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 


0
Pieter
9/28/2007 11:40:00 AM
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

"Pieter Wijnen" wrote:

> 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
> 
> 
> "Binraider" <Binraider@discussions.microsoft.com> wrote in message 
> news:45B20605-6DF2-4BE7-B945-D3733EDCFAA9@microsoft.com...
> > 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 
> 
> 
> 
0
Utf
9/28/2007 1:45:01 PM
you have to use # instead of . (myfile#txt, not myfile.txt)
just one of those things <g>

Pieter

"Binraider" <Binraider@discussions.microsoft.com> wrote in message 
news:5AFBB7F6-BFB1-43D2-A4BD-CD3CAE5FB748@microsoft.com...
> 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
>
> "Pieter Wijnen" wrote:
>
>> 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
>>
>>
>> "Binraider" <Binraider@discussions.microsoft.com> wrote in message
>> news:45B20605-6DF2-4BE7-B945-D3733EDCFAA9@microsoft.com...
>> > 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
>>
>>
>> 


0
Pieter
9/28/2007 6:18:43 PM
Reply:

Similar Artilces:

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Help with conditional formatting with 2000
Any help would be greatly appreciated. I am trying to group data together into increments of 10% of th numbers and then chart them based on these groups. For example, I hav 300 data points that vary from 20 to 500 in value. I want them t appear in a chart based on the number of values that fall in the lowes 10% of numbers (ie. 20-40) then the next 10% (ie. 40-60) etc. up to th top 10% of numbers, but I do not want to manually determine what thes ranges are. I want to see a distribution of how many numbers fal within each 10% of values. I am not sure if this makes sense, please let me know...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Highlight color for selected items
How can I find out what color is used for highlighting selected items (for example in Windows Explorer)on a users computer? Normally it is a blue color, but a user can change that. "Urban Olars" <anonymous@discussions.microsoft.com> wrote in message news:071c01c3c941$3db2c890$a401280a@phx.gbl... > How can I find out what color is used for highlighting > selected items (for example in Windows Explorer)on a users > computer? > Normally it is a blue color, but a user can change that. See if it's GetSysColor/COLOR_HIGHLIGHT. -- Jeff Partch [VC++ MVP] Take a ...

Routing Restrictions button unavailable (gray) no matter what options selected in IMS-->Routing tab
I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my "Routing Restrictions" button is unavailable (greyed-out) no matter what options I select. Any suggestions? Thank you. Try installing Exch Admin prog on other machine, apply SP4 and see if you can change it from there. Post back with details. ryanadmin wrote: > I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my > "Routing Restrictions" button is unavailable (greyed-out) no matter > what options I select. Any suggestions? Thank you. ...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

Selecting a column with an integer
Sub ColumnSelection() ' Selecting a column with an integer ' Please show me how to eliminate the use of Cells(1, 1) Dim r As Integer Dim c As String Dim numericcolumn As Integer Dim alphabetcolumn As String numericcolumn = 4 ' in practice 4 is the resultant of an equation alphabetcolumn = "=CHAR(" & numericcolumn + 64 & ")" Cells(1, 1) = alphabetcolumn ' I like to eliminate the use of Cells(1, 1) c = Cells(1, 1).Value ' I like to eliminate the use of Cells(1, 1) Cell...

BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would like to see visible on the lookup. For example, when looking up an item only item number and item description are visible fields. I would like to configure the lookup to show additional fields, like the vendor name. ...

Prevent copy and paste in one column
I am having trouble trying to prevent copying and pasting in one specific column. The code refers to the specific range, but yet it prevents copying and pasting on the whole worksheet. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns("H:H")) Is Nothing Then Application.CellDragAndDrop = False Application.CutCopyMode = False Else Application.CellDragAndDrop = True End If End Sub Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 11:42:01...

Command Button to Sort and Filter by Selection
Is it possible to create a command button on a form that will allow you to sort records or to use the Filter by Selction option? They are not choices I can select while using the wizard or the macro builder. However, both of these would be extremely useful as I'm building a database for someone who has never used a database before and isn't all that comfortable with computers in general. I am using Access 2003 and I am NOT familiar with Access Visual Basic, Java or building macros from scratch. I'm sure there is a way to use these tools to create what I want, ...

copying column width in tables
Hi, I created a table in Publisher with 6 columns. I would columns #1, 2, 4 and 5 to have the same width and columns 3 and 6 to have a different width. I'd like to set the width of column 1 and then paste that size to the other four so they all have exactly the same width. Presently, I am eyeballing it, but it takes too much time and is not exact. Any ideas on how to do this? The only way is to create guides. Arrange, ruler guides. You could try creating a table in Word, copy/paste. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsof...

Conditional formatting a date range
If I have a column of dates that are manual entered what is the formula to conditionally format them based on a date range of three months before the current date to the current date and another three months after the current date to the current date? Assume the dates are in column A, starting with A1. Highlight all the dates, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box you should select Formula Is rather than Cell Value Is and then enter this formula: =3DAND(A1>=3DTODAY()-91,A1<=3DTODAY()) Click on the Format button and choose the ...

Outlook 2003
Hi, Anyone know if it is possible to save multiple calendar selection views in Outlook 2003 yet ? For example a receptionist may have the ability to view say twenty or so users calendars. When she closes Outlook and opens it up again she has to select them all over again. I see lots of people wanting to do this and being told that it's not possible...or at least the posts I've read thus far. Jim. ...

Update a radio button by combobox selection
Hi, In a VS2008 VB project, I have a combobox that gets its value from a table with three columns. I want to display one column in the combobox, write the value of the second column back to a different table, and I want to change the value of a radio button (on the same form as the combobox) based on the value of the third column of the selected record. I'm setting the ComboBox.Text value to be the first column, the ComboBox.SelectedValue to be the second column, but how would I change the radio buttons in the most efficient way? Thanks in advance, Steve Wrong g...