Data Connection with Text Import Problem

[kittronald]

Using Excel 2007, I've configured a data connection using a text
file.

In the text file's first column, there is a value of TRUE (a
company's actual name).

When the data connection imports the text file, Excel sees TRUE not
as a text value, but as a binary value.

The problem is when the first column is sorted in ascending order,
TRUE gets put at the bottom of the column and is centered.

Placing an apostrophe before TRUE in the text file doesn't work,
nor does formatting the first column as text.

What can I do to make TRUE be sorted as a text value ?



- Ronald K.

 

[kittronald]

Ron,

Thanks for the reply.

Formatting the column doesn't affect the imported text and placing
a character in front of TRUE would place it at the top of the column
which is just as bad as being at the bottom.

Thanks anyway.



- Ronald K.

 

[GS]

kittronald was thinking very hard :

Ron,

Thanks for the reply.

Formatting the column doesn't affect the imported text and placing
a character in front of TRUE would place it at the top of the column
which is just as bad as being at the bottom.

Thanks anyway.



- Ronald K.

To have Excel ignore the default behavior regarding the word "true",
place a space character AFTER the name so it positions correctly in
sorted lists.

Also, you state you have "..configured a data connection..." which
implies you're using ADO and importng the contents of your text file as
a recordset. If this is the case then adding the space character should
be easy to do while writing the data to the worksheet.

 

[GS]

Ron,
That's what I thought you meant and so is why I explicitly stated AFTER
in my post.<g>

As you know, though, this creates a false name as far as cell values go
in respect to using a recordset.

 

[GS]

Ron Rosenfeld presented the following explanation :

I don't have experience in using ADO. Thanks for the discussion.

Ron, I suspect what's in place here is an import of External Data
rather than use of ADO recordset and if so then my point is mute!

 

[kittronald]

Garry and Ron,

Thanks for the input.

I'm using an import of External Data with a text file in Excel
2007.

Unfortunately, if I place another character around TRUE, formulas
that use that imported data will produce errors. An undesired way to
deal with this would be to write every formula to check for values
such as TRUE, FALSE and any other value that Excel does not see as
text.

The reason I'm using an import of External Data is because the
imported data can contract or expand and the formulas in the adjacent
columns will also contract and expand.

For the time being, I've filtered out the TRUE value in the
PivotTable that uses the imported data.


- Ronald K.

 

[GS]

kittronald used his keyboard to write :

Garry and Ron,

Thanks for the input.

I'm using an import of External Data with a text file in Excel
2007.

Unfortunately, if I place another character around TRUE, formulas
that use that imported data will produce errors. An undesired way to
deal with this would be to write every formula to check for values
such as TRUE, FALSE and any other value that Excel does not see as
text.

Well, only for names that would be that value. I mean.., what's the
likelyhood of having the name "FALSE"? At this point wrapping your
current formula in a simple IF() function to filter the additional
space if the name is "TRUE " is all that's necessary. In the case of
this company's name can you not include its status (ie: " LTD", " INC",
" CORP")?

Also, this is a fine example of why databases use unique identifiers so
name conflicts asre avoided. Is there something unique about each
company that your formulas can use instead of the name?

 

[kittronald]

Ron,

In Excel 2007, to import a text file through a data connection,
click on the Data tab, under the Get External Data group, click on
From Text (Import data from a text file).

Regarding dealing with TRUE, TRUE is a person's last name and will
be the only value in its cell.

A PivotTable uses a dynamic named range as its source. The first
column in that PivotTable is the LastName column which is where TRUE
resides.

Trimming that cell's added space character would render it the same
as if no space character were added.

Additionally, a custom sort order still wouldn't stop Excel from
seeing TRUE as a binary value.

I appreciate the help though.


- Ronald K.

 

[kittronald]

Ron,

What I'm trying to do is ensure that after the data connection text
file is imported, the first column is always sorted. Sometimes data is
appended to the bottom of the data connection text file, which creates
a non-sorted first column. However, this is only a cosmetic issue and
may only slow down use of VLOOKUP and MATCH functions and make it less
organized for manual searches for a value in the first column.

To correct the problem, I do not attempt to sort the first column
for the data connection text file. I then configure a PivotTable on
another worksheet to automatically sort.

My goal was to get the data connection to automatically sort.
However, in Excel 2007, this appears to not be possible.

Thanks for all the help.


- Ronald K.