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.

 

[Ron Rosenfeld]

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.

If you have access to the Text file prior to import, you can add a non-printing character to the word TRUE, such as <space> or <nbsp>
If not, you'll probably need to format the column as TEXT before Excel imports the file.

 

[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.

 

[Ron Rosenfeld]

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.

I guess I was unclear. I thought you would understand that I meant that ADDing a non-printing character to True implied placing the character AFTER True, not before.

e.g. True + <non-printing character> = True<space> or True<nbsp> or something similar.

 

[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.

 

[Ron Rosenfeld]

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

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

 

[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?

 

[Ron Rosenfeld]

Garry and Ron,

Thanks for the input.

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

If you are really "importing" a "text file", I don't understand why, when the Data Import wizard appears, you can't designate the appropriate column as Text. When you wrote that you couldn't, I think we all assumed that you are not "importing" a "text file", as that terminology is used in Excel. So what, exactly, are you doing?

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.

You don't put a character "around" True. What you should be doing is putting a character "after" True in your text file. If that character is a <space>, you can simply change your formula to replace your cell_reference with TRIM(cell_reference). If the character is something else, e.g. <nbsp>, you can change to SUBSTITUTE(cell_ref, char(160),""). That should be simple to make the change.

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.

If it is only how it sorts that is the problem, you could consider a custom sort order.

 

[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.

 

[Ron Rosenfeld]

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.

If that is the case, I cannot reproduce your problem.

I created a Text file containing:

Doe
Ford
True
Gaines

I then followed your procedure and the Text Import Wizard opened

At step 3 I declared the column as Text

The result shows True entered as Text and not as a Boolean. It sorts as text and is seen in a Pivot Table as text.

 

[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.

 

[Ron Rosenfeld]

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.

In order to address this new issue, we first have to figure out why your procedure is causing True to be interpreted as a Boolean, and not Text, even though you presumeably set that column as Text in the Data import wizard at Step 3. So let's get that figured out, and then we can handle the automatic sorting issue.