Solved

Using Powershell to convert a .csv to .xlsx

Posted on 2010-11-21
18
2,866 Views
Last Modified: 2012-05-10
When I double-click a .csv to open it in Excel, I'm losing data (details here, for interest's sake: http://www.experts-exchange.com/Programming/System/Windows__Programming/Q_26427029.html )

To resolve this, I want to find a different way of getting my .csv's open in Excel efficiently. I found this forum thread:
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/370ee470-f2cd-4f30-a167-b106dd51d47a
which describes how to use Powershell to convert a .csv to .xlsx format. I've never used Powershell, so I don't know it's limitations.

I work on a Windows XP Professional machine, (with full access to Debian Linux should it be helpful, though it's probably not relevant to this question) but I also have some access to Windows 2003 and Windows 2000 servers if necessary.

Can someone please guide me through setting up the script described in the forum above?

I'd need to start right from the basics - can I run Powershell on XP, do I need to download it, how can I set up and run a script etc...

Thanks!
0
Comment
Question by:Terry Woods
18 Comments
 

Assisted Solution

by:Melakh
Melakh earned 25 total points
ID: 34184524
You can download Powershell from microsoft, pretty sure in XP it will be in the optional part of Windows Update, but if not try the link at: http://technet.microsoft.com/en-gb/scriptcenter/dd772288.aspx

However, if I can offer an alternative...

Instead of opening the csv file, import it into Excel using the in-built text import feature.

This allows you to tell Excel what data types each field refers to, if you set it to text then it wont do things like dropping a leading zero or trying to guess what dates are.

If you're planning to do lots of these you can automate the import using VBA, if not then it's pretty quick anyway.

If you have several columns, as most people do in csv files, you can shift-click to select them all in step 3 of the text import wizard before changing the data type to text, so it's really quite quick to do.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34185131
Thanks for the suggestions, Melakh.

I've actually pursued the file-import technique in the EE question I linked to above. The process itself is a bit too slow to do manually on a regular basis, and when using a macro I also have the issue that it is difficult setting up the macro for other users in my organisation (I need to set up their "PERSONAL.XLS" workbook, then add the macro, and then educate them on how to use it). I can't see it being scalable at all

I can't use Windows Update at all as our windows installations are standardised for a corporate environment, but I will try your link and see if I can get it working.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34188630
What are the actual problems when opening the CSV in Excel?

Are you losing data?

Is data being converted to the wrong data type?

You don't really seem to have mentioned that here or in the other thread.

In the other thread the errors you mention seem to be with the actual data itself and you are opening the CSV file in Excel so you can fix them.

Is that right, or am I missing something?

By the way the PowerShell suggestion looks to me as though it's doing basically the same as some other suggestions - reading the datat from the CSV file, and populating an Excel worksheet/workbook with it.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34191663
Here's a description of the issue, as quoted from the question I linked to:

* 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?!).


The point is that I'm dealing with .csv data that must not be modified except where the user chooses to modify it. Simply opening the .csv file in Excel and saving it causes data changes to any fields matching the data formats described above (and probably others too), which is problematic. If the data field contains 001.000 then it needs to stay that way when the file is saved.

The existing solution I have got using VBA is too complicated for other users to install, which is why I'm looking at using Powershell instead.

I've done some reading on Powershell since I posted the question, but I still don't know how to create a Powershell script. Is it similar to .bat scripts maybe, with a .ps extension or something?
0
 
LVL 33

Expert Comment

by:Norie
ID: 34192513
Terry

If VBA is too complicated I don't quite see how Powershell would be simpler, I've used it a bit but I've never considered using it for this sort of thing.

If you were to use VBA to open the file then you could specify all the data to be text and save it as you want.

You could set the whole thing up to require minimal user input.

Did any of the suggestions in the other thread work at all?

You seem to have said matthewpatrick's solutiion worked but you wanted a Windows context menu?

Couldn't you just have the user open an Excel workbook, hit a button and run that code?

If you wanted a context menu that's totally different topic, you would have to change the user's Windows environment.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34193176
Thanks for your comments

The VBA code that I have already does work how you describe it, with a shortcut key to open a file, and it assigns text as the data type for all fields. But you're right that it doesn't seem possible to get that into a context menu.

It's not the VBA itself that has the problem with being complicated. It's the fact that to set up a macro for a user, they need to create a personal.xls workbook, create a macro, put the VBA code into it, save it, assign a shortcut key, then hide the personal.xls workbook. After that point, they need to remember to use the shortcut they assigned.

Our organisation has quite a number of users who should be using this tool, and they aren't IT staff - I guess I'm trying to reduce the chance of things going wrong, as I can't look over users' shoulders to see everything's working ok. Also, I'd like the tool I provide to the users to fit as neatly as possible into the existing business process that is used, which is why I'm very keen to get a context-menu tool working. There would be one key change to the way they do things - instead of double-clicking a .csv file to open it, right-click and choose something like "Open .csv data file safely".

I suppose one problem remaining with that idea is that it's also common practise to open a .csv file from Outlook, which they receive as an attachment (but that's an issue with the macro technique too, as you can't browse to an Outlook email attachment to open it). Maybe that problem will have to be fixed by educating the users to save their files first.

As for the Powershell scripts, it appears I may have trouble getting the script "signed" so that it can run on someone else's PC. The options seem to be either configuring the script on every PC to run unsigned, instructing the user for every PC on how to create a "self-signed certificate" for the script, or paying money to get the script officially signed (which may be an option, depending on cost). That's as far as I've managed to get so far.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34193296
Terry

Wouldn't there be little chance of getting something like a change in context menus approved?

If you could get that I suppose it would be solve the problem though.

Also why would the code need to go in the user's personal workbook?

Just created a 'template' type workbook with the code and distribute it.

That probably wouldn't prevent the user from just automatically opening the CSV but perhaps with a little persuasion you could get them to use the template.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34193357
The personal workbook opens (hidden, generally) when the user opens Excel, so the macros defined in it are always available. Presumably any other template workbook would need to be manually opened?

As for the context menu, my context menu already contains a number of custom items for applications I've installed, and users don't need approval to gain administrator access to their PCs, so I can't imagine it being a problem once I learn how to do it.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34193420
Terry

You are right about the personal workbook, but would it not be an idea to just ask the user's nicely to use the template workbook.

You could also consider creating an add-in.

Did you add those custom items yourself?

I don't think that you can easily add items or remove.

It would probably involve changing the registery and probably a fair bit of code, or purchasing 3rd party software.

Here's something I found with a quick search Customizing a Shortcut Menu Using Dynamic Verbs.

That might not be entirely relevant and it was a quick search.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 35

Author Comment

by:Terry Woods
ID: 34193847
I didn't add the custom items myself. It just seemed intuitive that it wouldn't be too complicated, given that a lot of custom items are simply "Open with blah..." - I figured it would just be a registry entry pointing to a program.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34193911
Terry

I'll admit I've never tried it, and I do sometimes 'dabble' with the registery for other things.

I'm pretty sure it's not as simple as changing ne registery entry.

If it possible to do it that way it would be more than one entry and it probably wouldn't be that easy to find.

It might also affect other things, for example other CSV/text files.

Like I said there are commercial programs that will do it but in my experience they aren't really reliable.

It's quite novel having custom  items on context menus but you might find that you end up not actually using them.

I added Copy to... and Move to... about a month ago as I thought it would be a great idea.

I've used them about twice since then.

When an application adds something to one of these menus I'm pretty sure there's quite a bit going on.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34193965
I found this link:
http://msdn.microsoft.com/en-us/library/aa753589%28VS.85%29.aspx
The context menu option seems easy enough at first glance.

Still, Powershell seems to be by no means an idea solution, as it would require installation on each users machine (and then there's the issue with scripting needing to be signed).

I wonder if I could write a program in some other language instead, and just create an .exe file... easier said than done? I don't know.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34194013
I think that's what I was looking for when I searched earlier.

Was going to mention that you might find something more relevant a bit further up the tree.

I still don't get why you think PowerShell would be a solution at all.

The code you posted in your link is basically doing the same thing as the code you said worked in the other thread.

It could probably be written with some other script language like VBScript or JavaScript.

You could write code in another lanuage and actually doing that part probably wouldn't be too difficult.

The difficult part would probably be turning it into a stable application that you can distribute with the minimum of fuss and errors.

All the factors you would need to take into account are too many to list but just think of all the possible combinations of versions of Excel, OSes, hardware, security....
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34194053
In our corporate environment, everyone has the same version of windows and office (except during upgrades, which are done office by office), and security is all the same too. Hardware differs, but we rarely have an issue with that with existing software.

I looked at powershell because I found a script for it when I was looking for a way of converting a .csv to .xlsx. The code to do it is actually fairly elegant, because it links to excel very smoothly - maybe VBScript can do that too? I don't know. What about C#.NET? Something else? I don't know much about the capabilities of MS languages.

I know that if I ask users to go through a complicated process to create an Excel macro, they won't all do it, and I'm responsible for ensuring our data is the best quality we can get - this is the problem with the solution that resulted in the previous thread.

If I can create an .exe file, I can put it on every office server in our organisation without too much fuss, and the context menu option could point to it.

At this stage, it's looking like this question is no longer appropriate for it's zone, and it's probably time to start a new question. Do you have any final suggestions? I really appreciate the time you've put into this, so thank you very much!
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34194063
ps: My "Master" certificate in C#.NET is a big red herring, just in case you noticed it.... I just help people with their regular expressions, and have never even written "hello world" with C#.NET! :-)
0
 
LVL 12

Assisted Solution

by:Alan3285
Alan3285 earned 50 total points
ID: 34194169
Jumping in here, I would just say that adding a shortcut to the right-click context menu (windows explorer, or on the desktop) is trivial.

The difficult bit is what you would send the CSV file to (PowerShell, Excel, a custom program of some sort etc).

To add a shortcut, you just create it and drop it into either the user's setup (C:\Documents and Settings\Username\SendTo off the top of my head - that might be XP but Win7 is similar).

HTH,

Alan.
0
 
LVL 33

Accepted Solution

by:
Norie earned 425 total points
ID: 34196608
Terry


You could automate Excel from C# I suppose, or just use some of the standard methods of reading, outputting files etc.

As for the Powershell script I sort of see what you mean, but I can also see that script being quite easily translated into C#.

Most of the C# stuff I've done with Excel has been using Visual Studio and it's pretty straightforward to automate MS Office applications from there.

Is that an option for you?
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 34243665
Thanks all of you for your help!

Right now I'm crossing my fingers and hoping that Office 2010 no longer has the issue with .csv files, but my intuition says it will still be a problem.

I will probably have a play with Visual Studio and see how I go, but unfortunately I've run out of time to continue looking at the problem for now.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now