|
||
|
9th December 2015, 15:09 | #1 |
Banned
I'm a spamaholic! Join Date: Dec 2015
Location: Nashville
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
MS Excel 2007
I've become aware of the limitations of opening .csv data files into Excel 2007 (earlier versions have the same issue). Various changes are made to the data as the file is opened, including at least the following:
* Fields that appear to be numeric and have leading or trailing zeroes have the leading/trailing zeroes removed * Values that Excel thinks match a date such as 5-10 get assigned a data type of date. Reformatting the field's data type to text gives a crazy value such as 40308 (probably the number of days from 1/1/1970 to the date 5/10 in the current year) Saving changes to the original .csv file causes permanent data loss as the above changes are saved back to the file (What were they thinking?!). This issue can be worked around by using the Data -> Get External Data From Text tool, but I deal with .csv files so frequently that I want an easier way of getting my data opened without corruption. I came up with the below set of actions: When right clicking on a .csv file in Windows Explorer, a context menu appears with various options. It would be useful to have an option “Open without corruption using Excel” or similar, which does the following: 1. Opens Excel 2. Creates a blank spreadsheet 3. Imports the select .csv file similar to the following commands: a. Select Data -> From Text b. Select the .csv file to be imported c. Choose the “Delimited” option d. Use comma as the delimiter character e. Select all columns (hold shift and click the right-most column header) f. Change the “Column data format” to Text g. Click Finish h. Put the data into cell A1 4. And possibly even alter the currently open Excel file name from “Book1” (or whatever it is) to the name of the .csv file that was opened. I have a colleague trying to learn to program with .NET that can attempt this, but the key questions are: 1. Will it be possible to perform the above actions with .NET? 2. What is a general programming plan (perhaps which modules to use, etc.?) that I can pass on to my colleague for development? |
10th December 2015, 05:06 | #2 | |
This is my second home
No more Rover Join Date: Apr 2009
Location: Barnsley, South Yorkshire.
Posts: 5,881
Thanks: 1,625
Thanked 1,308 Times in 998 Posts
|
Quote:
Ken. |
|
12th December 2015, 17:22 | #3 |
Banned
I'm a spamaholic! Join Date: Dec 2015
Location: Los Angeles
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
Manual solution in 6 steps:
1. Rename the file and remove the .xlsx or .xls extension etc. e.g. if my file is called test.xlsx I remove the '.xlsx' - you will get a warning message saying this could make the document unstable - accept it 2. The document icon should now have turned white (the document won't look like an excel document anymore) 3. Open a blank excel document by hitting start -> all programs -> Microsoft office -> excel (this may be different if you’re not on windows 7) 4. In your blank excel document hit file -> open 5. Navigate to the document you renamed above and select it, now click open 6. It should now open the document I hope this helps someone |
13th December 2015, 11:38 | #4 |
Banned
I'm a spamaholic! Join Date: Dec 2015
Location: Nashville
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
Branden, thanks for solutions, I'm very appreciated to you for help.
|
13th December 2015, 19:03 | #5 |
Gets stuck in
Rover 75 Saloon Join Date: Apr 2012
Location: Oxford
Posts: 767
Thanks: 141
Thanked 204 Times in 141 Posts
|
Just a thought but are the spaces already in the csv before the import?
__________________
The Story So Far: Austin A35, Morris Oxford, Triumph Herald, Mini 850, Mini 1000, Austin Allegro, MG Midget, MGB GT, Rover SD1 2600, Austin Maxi, Rover 200, Rover 825, Rover 800, Bedford TK, Range Rover 3.9 efi, Rover 400, Rover 100, MGF, Rover 25, Rover 75 1.8, Rover 75 Connoisseur SE 2.0 V6, MGF 1.8i, Rover 75 Connoisseur 2.0 V6 Auto, Morris Eight Series E, Morris Minor 1275. |
13th December 2015, 19:14 | #6 |
Regional Secretary
MGTF, MG ZS EV Exclusive Join Date: Sep 2007
Location: Kilwinning
Posts: 14,052
Thanks: 1,460
Thanked 3,563 Times in 1,857 Posts
|
The two gentlemen who had posted about this tread would appear to have joined the wrong forum and as such have, for now, left the building.
|
|
|