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,

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

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.

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

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,

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

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

---- Andy

There is a great need for a sarcasm font.

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

• 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

Thanks Skip

---- Andy

There is a great need for a sarcasm font.

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

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.

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

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.

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.

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

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.