File Data Minipulation - Macro Help

[Asm0deus]

Hello.

Been using macro express for quite a while and fairly knowledgable. I wanted to see if anyone had a macro idea for something I made up but takes to much processing time. I am creating this for work

---

Create a macro that reads a text document (or excel) and compiles a new file on the following conditions:

 

Example of text file entry: USER 987654321 6:04:54

 

Find each unique user and find the earliest time stamp and the latest. In other words find out when he first logged on and then left.

Now examine a file with 30,000 entries like this with on average 1000-2000 duplicate entries of each user with different time stamps.

 

I got a macro to work however it has to many 'temp' work files that it has to use in order to process the data. It takes forever and crashes. There has to be an easier way im just not seeing it. Please post coding ideas, I would greatly appreciate it.

[randallc]

Hi,

Here's an idea (example too to run to check if you want?...)

1. Sort the file.

2. AScii process the file to check first and last entry for each ID

// Sort file first

// Initialise Dos Sort

// Set Filename (and here, make example)

// Shut DOS in background if still running

// Set SORT switches

// Sort to Answertxt file

// Change spaces to commas, to use Ascii file process of csv file later

// process FIRST file lines

  // Write FIRST line of ID file

// process file lines till second field (T2) not the same

  // if it's a new ID

    // Write LAST line of previous ("Current") ID file for that ID, and FIRST of next ID

  // THEN; Re-assign the latest TIME for that Current ID

// Write LAST line of previous ("Current") ID file for that ID; at END

Best, Randall

SortIDandTIMES2.mxe

Edited October 16, 2005 by randallc