Excel - get data from text file 2

[Andrzejek]

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.

 

[SkipVought]

Hi,

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

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:

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

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:

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


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

What choices do you have in Data Type Detection?

First 200 rows, is NOT what you want!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

[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

Thanks Skip


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

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.

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


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

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


---- Andy

There is a great need for a sarcasm font.

 

[kray4660]

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.

 

[combo]

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

 

[Andrzejek]

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.