Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - get data from text file 2

Status
Not open for further replies.
Andrzejek

Andrzejek

Programmer
Joined
Jan 10, 2006
Messages
8,576
Location
US
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.
 
Sort by date Sort by votes
Hi,

Some values are 0001, 0002, etc.
Click to expand...

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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Upvote 0 Downvote
Number 1) - My file looks something like this:
[tt]
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[/tt]

Excel shows it as this:

Text_vbchsy.png


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Upvote 0 Downvote
my IMPORT of your data-- ALL columns declared as TEXT...
[pre]
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

[/pre]

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Upvote 0 Downvote
Here are the steps and the outcome available to me:

text file:
[tt]
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[/tt]

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

Text_01_qirlxa.png


And in Excel, I get this:

Text_q2zmn6.png


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 :-(


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
What choices do you have in Data Type Detection?

First 200 rows, is NOT what you want!

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Upvote 0 Downvote
[ul]
[li]Based on first 200 rows[/li]
[li]Based on entire dataset[/li]
[li]Do not detect data types[/li]
[/ul]

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.
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Upvote 0 Downvote
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 :-(, 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.

Text_02_bopyfx.png


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


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Don't know in this unknown (to me) dualog.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
AD1D550E-FCB5-4AB6-A3C8-1F86CACE2554_ne1bcz.jpg
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:

Edit_kixzjq.png


and then messing with different settings here:

PQuery_ebvmkx.png



---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Swi
  • Locked
  • Question Question
Excel Webservice
Replies
2
Views
811
mintjulep
M
kwbMitel
  • Locked
  • Question Question
Lost edited data in Excel File
Replies
3
Views
471
Scott24x7
Scott24x7
Kennelbloke
  • Locked
  • Solved
Excel Unique-Filter issue
Replies
3
Views
784
SkipVought
S
Kim296
  • Question Question
Need Genius Help - Finding multiple duplicates between columns - EXCEL
2
Replies
38
Views
3K
mintjulep
M
grnzbra
  • Locked
  • Question Question
Force Data Entry Format in Excel
Replies
10
Views
849
combo
combo

Part and Inventory Search

Sponsor

Back
Top