Excel – misrepresenting data 1

[Andrzejek]

I have some text files coming from mainframe, they are pipe delimited.
So, in order to see the data, I use Excel in which I do:
[tt]Data – Get and Transform Data – From Text/CSV –[/tt] I select the mainframe text file
Excel is ‘smart’ enough to ‘see’ the pipe delimiter and shows me nice data in the preview. OK, go ahead and get it into Excel. Life is good. But...

Some columns where there are Numbers in the text files, Excel shows numbers, which is OK. But some values in the columns in the text files are NOT numbers, and Excel decided to display – nothing, empty cell.

So, where in text file I have:
123
234A
345
654B
543

Excel shows:
123
<empty cell>
345
<empty cell>
543

What am I doing wrong?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[kray4660]

I just used file/open, browse. Made sure I was looking for Text Files (*.pm, *.txt.*.csv), then chose my text file, That opens the Text Import Wizard. It allows a lot of customization of the import.

 

[Andrzejek]

Thanks kray4660
It drove me nuts, the missing data in Excel

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

When you do an IMPORT of text data like this, YOU MUST KNOW YOUR DATA!

If a column has both text and numbers, YOU must know that and YOU must be the one to specify that that column is a TEXT type.

In fact you ought to specify each and every column, whether TEXT, YMD, DMY, MDY or General.

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]

Good point, Skip.
The problem is – this data comes from the mainframe and I need to evaluate it before I can attempt to transfer it into Oracle data base. So, I don’t really know this data. That’s why instead of looking at it as text only, I decided to dump it to Excel so I can see what’s going on. Turns out, there were some problems with this data, but ‘open text file’ into Excel does show me all, including the issues.
IMPORT was just wrong approach to this problem.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Under those circumstances TEXT all.

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]

Here's what happens when you import or transform data by allowing Excel (a program) to figure out your data. Excel "sees" number in the first few rows, it assumes a numeric column and, as such, any non-numeric values cannot be converted from Text to Numeric.

Actually the "numbers" Excel "sees" are not numbers at all but numeric characters that must be converted to numbers. Therein lies the problem.

faq68-7375

Skip,

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

 

[mintjulep]

OpenRefine is a tool built for this task.

It doesn't really do anything that you couldn't accomplish by informed use of Excel, but sometimes it makes things easier.

https://openrefine.org/