Unix data to excel

You need to either ftp ( ie use cuteftp for example a windows based ftp program ) the files from your AIX box to your PC or map a network drive to the AIX box from your PC. You can use samba to do this.

Once you transfer the flat files to your PC use Excel to open the files. The files will be delimited by a certain character ie | pipe or comma for example.

Excel will ask you the delimiter..and then you can import the file.

I create flat files on our server and also email ( using aix's sendmail and 4gl program )flat files to clients that way they can save them onto there hard drive on their pc and import them into excel.

Can the files you are receiving be emailed to you instead of being created on the aix server??

ie using an sql or 4gl program to create the files you can use AIX's sendmail to create the file and also mail it to someone!

Hi,

dont know if you are familiar with perl/active perl but there are some great modules that can massage your data into excel spreadsheets for you. All you will have to do is get them onto the windows box. If you have a look at the activeperl website there are some examples(in the mailing list) for just what you want - aspn.activestate.com (the website is down just at the moment).

to get the files onto windows, you can use scp (http://www.chiark.greenend.org.uk/~sgtatham/putty/) sftp or samba.

The other thing you could do is to use gnuplot to create some png files with your data and put them into a webpage or other sort of doc as pngs

good luck, sm

I am assuming you want an automated process for this. Here is how I would do it.

First install cygwin on the workstation (assuming it's a windows box - you're using Excel, right?). Perl is important - you could use active perl, or the perl from cygwin - your choice. There is perl module called Spreadsheet::WriteExcel, which you can use to produce the spreadsheet. Check out CPAN. You may find other Excel interface modules that are better suited to your purpose.

You have a couple of ways you could get the data onto the workstation - ftp or scp most likely, either of these you can script in cygwin easily enough. Personally, I would use autoexpect to quickly knock together an expect script using scp or sftp (with public keys, not passwords please).

Once you have the data on the PC, you need to get down and dirty with perl & Spreadsheet::WriteExcel to manipulate it how you want.

Assuming you are using a workstation that is always on the network, it could be handy to have all this done automatically. You could either use the Windoze task scheduler (kahk gahg splutttr) or you could use cygwin's cron facility. You can install cygwin cron as a "service" and control it with crontab just like on a real operating system.

I would use crontab to call a script that calls an expect script to transfer the data to the workstation, then does some simple checking (maybe an md5 sum - use cygwins md5sum utility - to make sure the data is ok, otherwise send an email. Then call a perl script to do the conversion to Excel.

If you don't want to install cygwin you can still get all the components in some other form. ActiveState's Active perl, Windows Task Scheduler. Expect is harder to find, and autoexpect you probably will have to do without. But since I find cygwin so useful for other stuff, I think it's worth the overhead.

I would like to add a couple of points to the previous post. One of the things that you can do is to change the line returns to a DOS file system. Look for either UNIX2DOS commands or try to do a Man (Manual) page on the command. Check with your administrator then see how they recommend the file transfer take place. Perhaps you can use SMB like the previous poster mentioned. Other methods might be to use another protocol like FTP to send the file to another system.

This question was closed by the author