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?
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
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
RE: Excel - get data from 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 shows it as this:
---- Andy
There is a great need for a sarcasm font.
RE: Excel - get data from text file
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel - get data from text file
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel - get data from text file
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.
RE: Excel - get data from text file
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
RE: Excel - get data from text file
- 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 wantThanks Skip
---- Andy
There is a great need for a sarcasm font.
RE: Excel - get data from text file
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
RE: Excel - get data from text file
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
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
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel - get data from text file
---- Andy
There is a great need for a sarcasm font.
RE: Excel - get data from text file
RE: Excel - get data from text file
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
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.