How to open a csv in stupid mode 1

[xwb]

I have a csv file with hex numbers and fractions that I don't want excel to interpret. I just want to look at them.

1E10 necomes 1.00E+10
52E1 becomes 5.20E+02
3/8 becomes 8th March
7/16 becomes 16th July

Is it possible to load up a csv in stupid mode i.e. do not try to be clever and interpret what is in the file: just show me what it has in columns

 

[xwb]

I can get round the hex stuff by opening excel first, and using the column import wizard but not when I just double click on a csv file. I'm just wondering whether it is possible to somehow tell excel that when it opens csv files, not to interpret anything

 

[SkipVought]

Hi,

No native feature to do that"

However, how about a macro in your personal.xlsb?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

Copy and Paste into Word.

Text to columns, with comma delimiter.

 

[SkipVought]

Well, mint, that's not "Opening" a .csv; that's copy 'n' paste.

Could do the same thing in Excel by 1) changing the Number Format of all cells to TEXT, 2) then copy 'n' paste 3) text to columns - FINISH!

Or copy 'n' paste then text to column -- Delemited -- all column TEXT.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

I just want to look at them.

just show me what it has in columns

Addresses the primary functional requirement, which is so important that it was stated twice.

The additional prescriptive requirements seem unnecessarily restrictive, resulting in an overly complex solution.

 

[mikrom]

Or instead of Excel you could try to use another CSV-Viewer/Editor like

http://csvquickviewer.com/

http://csved.sjfrancke.nl/

...etc

 

[xwb]

Just found out Office Libre has exactly the same problem. I'll try the CSV viewers when I figure out how to get software into the machine. It is one of those "secure machines" which has its own network, no internet access and so locked down that you can't install anything on it or get anything out of it. Have to go through hoops to get updates or to get anything installed on it.

 

[mikrom]

The drawback of the GUI utilities I mentioned above seems to be the absence of command line option where you can define delimiter of your CSV file.

If you only need to view CSV file you could use PowerShell.
It's now typically installed on every Windows Machine.

For example if the CSV file has as delimiter comma (,) you can use

powershell import-csv -Path <path_to_my_csv_file>

or when your file has other delimiter for example I'm using mostly semicolon ( then somethhing like this:

powershell import-csv -Delimiter ';' -Path <path_to_my_csv_file>

See more options

https://technet.microsoft.com/en-us/library/hh849891.aspx

 

[mikrom]

If PowerShell is on your system, then you can create a little BAT file, like:

view_csv.bat

@echo off
powershell import-csv -Delimiter ';' -Path %1
pause

and use it like:

view_csv <path_to_my_csv_file>

 

[xwb]

Yes, we have powershell. Took a while to figure it out - the powershell import-csv doesn't like trailing commas. It worked once the trailing commas were removed.

 

[SirGTek]

XWB

Have you considered using something like a portable version of Sublime Text? I use it to view, edit, and clean .csv files because Excel is a P.I.T.A. due to the automatic conversion it does - like you mentioned in your OP.

If you can download files and unzip them, you're all set. Everything below can be done without admin rights to your computer.

'Install' Sublime Text
- goto

http://www.sublimetext.com/3

- click downloads
- look for the 'portable version' link
- download whichever version you need
- x86 version ::

http://c758482.r82.cf2.rackcdn.com/Sublime Text Build 3083.zip

- x64 version ::

http://c758482.r82.cf2.rackcdn.com/Sublime Text Build 3083 x64.zip

- unzip archive
- open Sublime Text

'Install' Package Control
- press "CTRL"+"~" (tilde)
- copy and paste the 'install' text from

https://packagecontrol.io/installation

- close and reopen Sublime Text

'Install' Advanced CSV Plugin
- press "CTRL"+"SHIFT"+"P"
- type install
- select: "Package Control: Install Package"
- type "Advanced CSV"
- click or press Enter

Restart Sublime Text
- open your csv file
- right click and select CSV from the popup menu

... enjoy the simple, no-fuss, no-mess CSV viewing and editing. I use it on a daily basis and only use Excel when I absolutely have too.

 

[xwb]

I haven't really considered installing other packages because we have no admin rights on those machines. It is one of those secure environments with its own LAN, no admin access and a torturous route to get information in or out. It is a control system which comes with a fixed set of tools so you just work with what you have.

The system works - it is just that looking at the data for analysis purposes is a pain because everything is written in hex. The data cannot be taken off because it is considered "sensitive" (like who's interested in a bunch of hex numbers with no headers).

Thanks for the tip. I'll try it out on less secure environments.

 

[SkipVought]

PITA???

Could you clarify.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SirGTek]

@XWB
Yeah, using a network station without admin rights is enough of a pain. One like you're on... ugg! But, I thought I would mention the option anyway, just in case. Good luck!
ps-ohh, and if you can read and write in HEX - you're a god!

@Skip
P.I.T.A. equates to Pain In The As$

 

[SkipVought]

I thought it was People for the Inequitable Treatment of Animals.

Well I have asteroids, so it means nothing to me! ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SirGTek]

Skip,

Well, yes there is that meaning of PITA... but in reality, they fit my definition as well. In fact they may be more of a P.I.T.A than the csv problem!

 

[SkipVought]

Over the past 25+ years I've used data from many a text file in Excel. Early on I discovered the "Excel is changing my data" problem, but very soon came to understand that you LOGICALLY can't have it both ways. And that lead me to realize that Excel has an IMPORT feature that solves the problem in most cases.

So if i were to have a situation where I'd need NO CONVERSION to take place, I'd IMPORT .csv (comma separated values), formatting each column as TEXT. This is a minor inconvenience, compared to having valid TEXT to NUMBER conversion actually occurring in Excel for dates alone!

IMNSHO, this is a tempest in a teapot! Excel is a fabulous tool. Learn to use it wisely. You assume that Excel OUGHT to do something that it was never designed to do. Living successfully in the world is dealing with what IS.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[xwb]

Yes it is a wonderful tool and it has both helped me out and kept me amused. For instance, I sometimes have to deal with US data where they use fractions like 1/8 and 1/4. This is instantly interpreted as a date so an eighth appears as either 08Jan or 01Aug, which then converts to a number of days and has had me wondering how one eighth became such a big number.

Once you've figured out what has happened, it is quite easy to fix it. For instance, with the hex numbers, I sometimes run a script to put all the numbers in quotes before reading them in.

 

[SkipVought]

Know thy data"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]