Importing txt or csv file as text and only text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mom - 03 Mar 2007 19:24 GMT

How come, after all these years and versions of Excel, an option has
not been added that will allow the user to "turn off" the default
conversion of certain text strings to numbers and dates when importing
text files?

All there seem to be are annoying work-arounds, the multi-step text-
import wizard, techniques to undo a conversion that wasn't requested
in the first place.

Reply to this Message

Earl Kiosterud - 03 Mar 2007 20:07 GMT

Mom,

Probably more confounding than Excel's automatically formatting stuff (often
inappropriately) is that often the user doesn't know it's done it. Words like
control-freak, anal, over-achiever come to mind. But we must make the best of it -- there
are no switches to turn it off.

Some ways of dealing with it are to record a macro while stepping through the text import
wizard, setting all the fields to text. Then just run the macro.

If you import the same stuff regularly, setting up a query is great. Data, Import External
Data - Import data. Point it to your text file, step through the text wizard, and it's
done. Now right click in your worksheet where it's imported the data, and you'll see you
can cause the import to happen again, same settings (Refresh Data). You can change the
import (Edit text import), or change the way it behaves when it does the import (Data Range
Properties). Pretty cool. Look Mom, no macro.

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> How come, after all these years and versions of Excel, an option has
> not been added that will allow the user to "turn off" the default
[quoted text clipped - 4 lines]
> import wizard, techniques to undo a conversion that wasn't requested
> in the first place.

Reply to this Message

*alan* - 04 Mar 2007 01:18 GMT

> Mom,
>
[quoted text clipped - 14 lines]
> import (Edit text import), or change the way it behaves when it does the
> import (Data Range Properties). Pretty cool. Look Mom, no macro.

Perhaps I'm either a bit dense or partially blind, but after having imported
data, no matter where I rightclick, I am unable to find what you refer to as
"Refresh Data". Likewise, features you refer to as "Edit Text Import" and
"Data Range Properties", do not appear to be available. I agree that it
seems like it would be pretty cool, but only if I could find the functions
you're referring to. I'm using Excel 2003 (perhaps that's the problem?) . .
.

>> How come, after all these years and versions of Excel, an option has
>> not been added that will allow the user to "turn off" the default
[quoted text clipped - 4 lines]
>> import wizard, techniques to undo a conversion that wasn't requested
>> in the first place.

Reply to this Message

Dave Peterson - 04 Mar 2007 02:26 GMT

Did you use:

> If you import the same stuff regularly, setting up a query is great.
> Data, Import External Data - Import data.

to import the data--or did you just use file|Open?

> > Mom,
> >
[quoted text clipped - 35 lines]
> >> import wizard, techniques to undo a conversion that wasn't requested
> >> in the first place.

Signature

Dave Peterson

Reply to this Message

*alan* - 04 Mar 2007 14:33 GMT

> Did you use:
>
>> If you import the same stuff regularly, setting up a query is great.
>> Data, Import External Data - Import data.
>
> to import the data--or did you just use file|Open?

Aha . . . I see. Thank you.

>> > Mom,
>> >
[quoted text clipped - 47 lines]
>> >> import wizard, techniques to undo a conversion that wasn't requested
>> >> in the first place.

Reply to this Message