×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel - get data from text file
2

Excel - get data from text file

Excel - get data from text file

(OP)
Microsoft Office 365 ProPlus, Excel

I have a comma delimited text file I want to bring into Excel.
Some values are 0001, 0002, etc. and I want those values to stay that way.
So, in Excel I do: Data – Get Data – From File – From Text/CSV, I can find my text file, no problem, and click Import.
Excel gives me the preview of what the data will look like in Excel, but all the values of 0001, 0002, etc. are now 1, 2, 3, etc. And I don’t see any place where I can specify HOW my data should be ‘transferred’ into Excel.

Previous versions of Excel allowed me to format columns to whatever I wanted to. I could set them all to be text and the values would stay 0001, 0002.

What am I doing wrong?


---- Andy

There is a great need for a sarcasm font.

RE: Excel - get data from text file

Hi,

Quote:


Some values are 0001, 0002, etc.

Do you mean...
1) there are some COLUMNS where all the numeric digits have leading zeros or
2) there are, scattered in columns, a mixture of numbers and numeric digits with leading zeros

???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - get data from text file

(OP)
Number 1) - My file looks something like this:

94,0100,21,01,0923,000,10000, , ,E
94,0400,21,02,0951,000,10000, , ,E
94,0800,21,02,1482,000,00015, , ,E
94,0800,21,02,1482,004,00016, , ,E
94,0800,21,02,1482,004,00021, , ,E


Excel shows it as this:



---- Andy

There is a great need for a sarcasm font.

RE: Excel - get data from text file

So, in your IMPORT wizard, go thru the process of selecting DELIMITED, and select COMMA and then in the next step, select TEXT for the columns you need leading zeros.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - get data from text file

my IMPORT of your data-- ALL columns declared as TEXT...

A	B	C	D	E	F	G	H	I	J
94	0100	21	01	0923	000	10000	 	 	E
94	0400	21	02	0951	000	10000	 	 	E
94	0800	21	02	1482	000	00015	 	 	E
94	0800	21	02	1482	004	00016	 	 	E
94	0800	21	02	1482	004	00021	 	 	E

 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - get data from text file

(OP)
Here are the steps and the outcome available to me:

text file:

94,0100,21,01,0923,000,10000, , ,E
94,0400,21,02,0951,000,10000, , ,E
94,0800,21,02,1482,000,00015, , ,E
94,0800,21,02,1482,004,00016, , ,E
94,0800,21,02,1482,004,00021, , ,E


Excel: Data – Get Data – From File – From Text/CSV
gives me this:



And in Excel, I get this:



There is no place in Excel (that I know of) where I can set the columns to be Text.
I used to have this capability in previous versions of Excel, but I don't have it now sad


---- Andy

There is a great need for a sarcasm font.

RE: Excel - get data from text file

What choices do you have in Data Type Detection?

First 200 rows, is NOT what you want!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - get data from text file

(OP)
  • Based on first 200 rows
  • Based on entire dataset
  • Do not detect data types
If I choose the last option (Do not detect data types), I do get what I want thumbsup2

Thanks Skip


---- Andy

There is a great need for a sarcasm font.

RE: Excel - get data from text file

Microsoft Office 365 ProPlus, Excel

Is that the on-line subscription version?

Good to know what those options are!

In addition to my 2013 Office version of Excel on my PC laptop, I have recently been using a FREE version on my iPad. It is definitely pared down but at least I can set stuff up on my laptop full Excel and then manipulate stuff on my iPad.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - get data from text file

(OP)
This is my 'at work' set up, feels like it is loaded on my PC, but who knows.... It may be on-line subscription version. How can you tell? PC Support people deal with it.

One additional; question about the Import.
If I add the header row as first row of my text file, and select 'Based on first 200 rows', Excel gives me row 1 with my header row (but format the data the way Excel likes sad, my original issue)

If I choose any other way, either 'Based on entire dataset' or 'Do not detect data types', my header row is the second row (first row of data), Excel makes its own 'header' with "Column1', 'Column2', 'Column3', etc.



Is there some way to say:" My file HAS a header row"?


---- Andy

There is a great need for a sarcasm font.

RE: Excel - get data from text file

Don't know in this unknown (to me) dualog.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel - get data from text file

(OP)
That's OK, I can always copy what I need and paste it into another sheet the way I want it to be. pc1


---- Andy

There is a great need for a sarcasm font.

RE: Excel - get data from text file

For CSV files, many times I go to the DATA tab and choose the From Text, that way it brings up the Text Import Wizard and I can choose my data types for each column.

RE: Excel - get data from text file

Andy, you've got your data through Get&Transform query (Power Query built-in com addin). The query here is a series of transformations, with no output (for the links in other queries for instance) or output to excel table. You can edit the query and when you are in the query desktop, you can see a list of query steps (should be on the right), delete the second and last action ("Change Type" item in basic view of query steps, should be on the right). Next you can either close and load data or continue transformations in the addin and return required data shape to the worksheet.
Power Query ia a new (built-in excel feature since 2016) and really powerful tool worth to learn, with programme-like data transformation, line by line, in a single query ztructure.

combo

RE: Excel - get data from text file

(OP)
Thanks combo, that was it.
Just to clarify, the "Get&Transform query (Power Query built-in com addin)" is accessed in Preview by clicking Edit:



and then messing with different settings here:


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close