Sort CSV file

[Jerz]

I've looked around for a few hours, but can't find examples of just opening, sorting, & saving.

OK, I've parsed huge txt logfiles, and extracted relevant data to a CSV - User,Computer,Timestamp. I.E.

UserA,Computer1,Mon Nov 22 2004
UserA,Computer1,Mon Nov 22 2004
UserB,Computer2,Mon Nov 22 2004
UserA,Computer2,Mon Nov 22 2004
UserA,Computer2,Mon Nov 22 2004
UserB,Computer3,Mon Nov 22 2004
UserA,Computer1,Mon Nov 22 2004

Unfortunately for me, it's all sorted by Timestamp. I want to read the data, and keep tallies of User & Computer. My plan is/was to open the CSV into excel, Sort accordingly, Save the re-sorted file, which now looks like:

UserA,Computer1,Mon Nov 22 2004
UserA,Computer1,Mon Nov 22 2004
UserA,Computer1,Mon Nov 22 2004
UserA,Computer2,Mon Nov 22 2004
UserA,Computer2,Mon Nov 22 2004
UserB,Computer2,Mon Nov 22 2004
UserB,Computer3,Mon Nov 22 2004

Then read it line by line adding up the matches, And clearing the counter when the user or computer changes, giving

UserA on Computer1 3 Time(s)
UserA on Computer2 2 Time(s)
UserB on Computer2 1 Time(s)
UserB on Computer3 1 Time(s)

Code I have so far is:

Fname = "v:\vbscripts\exceloutput\test.csv"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
objExcel.displayalerts=false
set objwbk = objExcel.Workbooks.Open(fname)
'so far so good - the file opens
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()
wscript.echo "pause"'so I can tell I got this far....
'I guess A:1 defaults, because it autofits the first col
'now how to sort it?

objwbk.worksheets(1).saveas csvfile, fName
'above line gives an "unknown runtime error" 800A03EC
'How to save as CSV?
objwbk.close
objExcel.quit

As I was looking up solutions, it seems ADO may be a better one, but I haven't the first clue how to get what I need from that either. Ideas anyone?

 

[PHV]

Why not simply use the SORT command in a WshShell.Run call ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dilettante]

There are are couple of suggestions in the FAQ here.

 

[spool]

hello,

I know a little about scripts but I think this is over my head.

I need to run three scheduled programs on 20 computers in our office without intervention in succession. I like to schedule it when no one is working around 3:00 AM. I was able to this with our 98 boxes but now all are XP's.

Disk Cleanup>cleanmgr.exe
Error-Checking>chkdisk.exe
Disk Defragmenter>defrag.exe

I used Scheduled Tasks to start Disk Cleanup but it needs someone to choose what drive to scan and then click OK and then click OK to delete the files. I need it to run all the way through on it's own.

I have XP machines so Error Checking can only be run after restart.

Defrag is the same as Disk Cleanup it needs someone to choose what's next to be done.

This is what I'm trying to do. First schedule Disk Cleanup to run then after it is finished restart the computer and run Error Checking after Error Checking I think it brings me back to the logon screen. So I think I will have to bypass logon so the final program Defrag can run then restart or log off to bring the system back to the logon/Welcome screen.

Now this is what I have done/tried. The script I did can get Disk Cleanup to start but I can't get past the OK button so the other OK button can be reached so the program can delete the files.

I did a script that will restart the computer. But I need checkdisk to know that it is next to run.

I got from this forum a vbscript that will run defrag completely.

I need to connect all this to work in one fluid motion.

Any help would be appreciated.

spool


Don't argue with an idiot, he will bring you down to his level and beat you with his experience.

 

[spool]

Sorry I posted the above question here I walked away and came back and forgot I was reading this thread.

spool

Don't argue with an idiot, he will bring you down to his level and beat you with his experience.

 

[Jerz]

PHV,

Thanks. Sort.exe was just what I needed. I built a bubble search, but sort was at least a hundred times faster.

Tip for anyone reading this with the same problem: be sure to close the file in the script before you try to sort it. Sort won't open it if you just created it in the script but haven't closed it yet.