Process only LAST record from CSV?

theSteve · May 4, 2010

Does anyone know a way to get just the LAST record from a CSV text file?

(The CSV file is generated when the DATE/TIME is appended to a text file at the time of another macro's execution.)

So my CSV will look like this:

04/28/2010 4:22:15 PM, 04/30/2010 1:15:02 PM, 05/01/2010 7:45:50 AM,

I want to store the last entry to a variable.

paul · May 4, 2010

Just read the file; when the file is closed, your variable will contain the value of the last row.

Text File Begin Process: YourFilePathAndName
Text File End Process
Text Box Display:

paul · May 4, 2010

Just read the file; when the file is closed, your variable will contain the value of the last row.

Text File Begin Process: YourFilePathAndName
Text File End Process
Text Box Display:

<TEXT FILE BEGIN PROCESS Filename="YourFilePathAndName" Start_Record="1" Process_All="TRUE" Records="1" Variable="%tData%"/>
<TEXT FILE END PROCESS/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang3081{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 This should be the last line of your file!\r\n\\par \r\n\\par %tData%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="2" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

theSteve · May 4, 2010

Just read the file; when the file is closed, your variable will contain the value of the last row.

Text File Begin Process: YourFilePathAndName
Text File End Process
Text Box Display:

<TEXT FILE BEGIN PROCESS Filename="YourFilePathAndName" Start_Record="1" Process_All="TRUE" Records="1" Variable="%tData%"/>
<TEXT FILE END PROCESS/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang3081{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 This should be the last line of your file!\r\n\\par \r\n\\par %tData%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="2" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

Hm, that makes sense.

I was trying to use CSV files and ASCII Text File Process command, which ONLY accepts an ARRAY instead of a single string variable.

But I suppose I can change the text file to a line-by-line format instead of csv.

Thanks Paul,

theSteve

kevin · May 5, 2010

I was trying to use CSV files and ASCII Text File Process command, which ONLY accepts an ARRAY instead of a single string variable.

You can use the ASCII File Process commands in the exact same manner:

ASCII File Begin Process: YourFilePathAndName
ASCII File End Process
Text Box Display:

theSteve · May 5, 2010

You can use the ASCII File Process commands in the exact same manner:
ASCII File Begin Process: YourFilePathAndName
ASCII File End Process
Text Box Display: 

Really? Am I incorrect in my belief that ASCII File processing needs to be fed an ARRAY variable?

Under the understanding that an array IS mandatory here:

If I process to %T%, it stores the FIRST value of the file in %T[1]%, second to %T[2]%, and so on...

... but I have no way of knowing how many values are in the text file... so how would I get the FINAL value?

paul · May 5, 2010

Really? Am I incorrect in my belief that ASCII File processing needs to be fed an ARRAY variable?

Under the understanding that an array IS mandatory here:

If I process to %T%, it stores the FIRST value of the file in %T[1]%, second to %T[2]%, and so on...

... but I have no way of knowing how many values are in the text file... so how would I get the FINAL value?

Let's assume a comma-delimited file contains these values:

abc,def,g

123,456,789,012,345

qwe,rty,uio,pqw

The command

<ASCII FILE BEGIN PROCESS Filename="YourFilePathAndName" Format="CSV" Start_Record="1" Process_All="TRUE" Records="1" Variable="%tData%" Start_Index="1"/>

will place each comma-delimited value into %tData[1]%, %tData[2]%, etc. Obviously you must set up %tData% as an array containing at least as many elements as the row in your file with the most fields or columns (number of commas plus one)

Once you have finished reading the file, then %tData% will contain these values:

%tData[1]% qwe

%tData[2]% rty

%tData[3]% uio

%tData[4]% pqw

%tData[5]%

terrypin · May 5, 2010

I'm following along here, to learn more about these text file commands, which I've rarely used.

Paul: I made a file from your text example and ran your suggested macro, correctly getting the result you described. Then I amended it as follows to see how I would get the individual lines:

ASCII File Begin Process: "C:\Docs\SUNDRY\Macro Express\PaulCSVExample.csv" (Comma Delimited Text (.csv))
 Text Box Display:  // Shows each line (set of fields).
ASCII File End Process

<ASCII FILE BEGIN PROCESS Filename="C:\\Docs\\SUNDRY\\Macro Express\\PaulCSVExample.csv" Format="CSV" Start_Record="1" Process_All="TRUE" Records="1" Variable="%tData%" Start_Index="1"/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Current result\r\n\\par Field 1 = \\f1 %tData\\f0 [1\\f1 ]%\r\n\\par \\f0 Field 2 = \\f1 %tData\\f0 [2\\f1 ]%\r\n\\par \\f0 Field 3 = \\f1 %tData\\f0 [4\\f1 ]%\r\n\\par \\f0 Field 4 = \\f1 %tData\\f0 [4\\f1 ]%\r\n\\par \\f0 Field 5 = \\f1 %tData\\f0 [5\\f1 ]%\r\n\\par \\f0 Field 6 = \\f1 %tData\\f0 [6\\f1 ]%\r\n\\par \\f0 Field 7 = \\f1 %tData\\f0 [7\\f1 ]%\r\n\\par \\f0 Field 8 = \\f1 %tData\\f0 [8\\f1 ]%\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0" _COMMENT="Shows each line (set of fields)."/>
<ASCII FILE END PROCESS/>

(If you saw any earlier versions of this post, about inconsistent results, ignore them - my mistake.)

But, even assuming I knew that the maximimum number of fields in any record was 8, as I have above, this is obviously a very clumsy way of displaying all the data. I'm hoping you might point out a better method please?

--

Terry, East Grinstead, UK

paul · May 5, 2010

I'm following along here, to learn more about these text file commands, which I've rarely used.

.........

But, even assuming I knew that the maximimum number of fields in any record was 8, as I have above, this is obviously a very clumsy way of displaying all the data. I'm hoping you might point out a better method please?

Unfortunately, the file processing commands in MEP (like so much else) are shockingly slow with larger files (unlike ME3 where they were astonsihingly quick).

Here's some code to display the results of each row in your .csv file. You could also derive the maximum number of fields by first reading the file with the Text File commands and counting the number of commas in each row - prohibitively slow in MEP for large files!

Variable Set to ASCII Char 13 to %tCr%
Variable Set to ASCII Char 10 to %tLf%
ASCII File Begin Process: "d:\temp\test.csv" (Comma Delimited Text (.csv))
 Variable Set String %tData% to ""
 Variable Set Bool %bDataFound% to "False"
 Repeat Start (Repeat 100 times)
   If Variable %tDataIn[%nIndex%]% Equals ""
     And
   If Variable %bDataFound% Equals "False"
     Continue
   Else
     Variable Set Bool %bDataFound% to "True"
     Variable Set String %tData% to "Field %nIndex% = %tDataIn[%nIndex%]%%tCr%%tLf%%tData%"
   End If
 End Repeat
 Text Box Display: 
ASCII File End Process

<VARIABLE SET TO ASCII CHAR Value="13" Destination="%tCr%"/>
<VARIABLE SET TO ASCII CHAR Value="10" Destination="%tLf%"/>
<ASCII FILE BEGIN PROCESS Filename="d:\\temp\\test.csv" Format="CSV" Start_Record="1" Process_All="TRUE" Records="1" Variable="%tDataIn%" Start_Index="1"/>
<VARIABLE SET STRING Option="\x00" Destination="%tData%"/>
<VARIABLE SET BOOL Destination="%bDataFound%" Command="263" Value="FALSE"/>
<REPEAT START Start="100" Step="-1" Count="100" Save="TRUE" Variable="%nIndex%"/>
<IF VARIABLE Variable="%tDataIn[%nIndex%]%" Condition="\x00" IgnoreCase="FALSE"/>
<AND/>
<IF VARIABLE Variable="%bDataFound%" Condition="\x00" Value="False" IgnoreCase="FALSE"/>
<CONTINUE/>
<ELSE/>
<VARIABLE SET BOOL Destination="%bDataFound%" Command="263" Value="TRUE"/>
<VARIABLE SET STRING Option="\x00" Destination="%tData%" Value="Field %nIndex% = %tDataIn[%nIndex%]%%tCr%%tLf%%tData%"/>
<END IF/>
<END REPEAT/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang3081{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\ul\\f0\\fs16 Current Result\r\n\\par \\ulnone %tData%\\ul\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="2" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<ASCII FILE END PROCESS/>

Notes:

- We process each record backwards so that we can locate the last field in each record (too bad if the last field contains no data!)

- Even though we're processing backwards, we can still display forwards!

- The boolean flag is needed to display any "empty" fields before the last field in the current record

- Repeat 100 times is obviously an arbitrary guess at the likely longest record

Cory · May 5, 2010

Really? Am I incorrect in my belief that ASCII File processing needs to be fed an ARRAY variable?

You are indeed incorrect. You 'feed' it a file, you store the results in an array. And the result is not one element for each line, it's one element for each delimited value (think columns) per line. Experiment a little with it and you will quickly see. Don't fret, it won't bite.

terrypin · May 5, 2010

Unfortunately, the file processing commands in MEP (like so much else) are shockingly slow with larger files (unlike ME3 where they were astonsihingly quick).

Here's some code to display the results of each row in your .csv file.

etc

Many thanks Paul, I'll play around with that.

--

Terry, East Grinstead, UK

theSteve · May 7, 2010

You are indeed incorrect. You 'feed' it a file, you store the results in an array. And the result is not one element for each line, it's one element for each delimited value (think columns) per line. Experiment a little with it and you will quickly see. Don't fret, it won't bite.

Oh I've played with it a LOT. And I love it. Just seems weird it NEEDS an array.

My problem was that I only want ONE value ... the LAST one.

So an array is useless to me. Because in my situation, I don't know how many delimited values are in the text file.

I changed my macro to use CR after the value when building the text file (instead of commas) and now use TEXT FILE PROCESS instead of ASCII to process the built text file. Problem solved.

Cory · May 10, 2010

My problem was that I only want ONE value ... the LAST one. So an array is useless to me. Because in my situation, I don't know how many delimited values are in the text file.

This comment proves you do not understand how this command works. TO use an Excel analogy I believe you are thinking of rows instead of columns. I know you have solved your need in the way I suggested already but you might want to take some time to understand this command as well as it is very useful. I just don't want you to miss out on a cool command because you don't understand how it works.

theSteve · May 10, 2010

This comment proves you do not understand how this command works. TO use an Excel analogy I believe you are thinking of rows instead of columns. I know you have solved your need in the way I suggested already but you might want to take some time to understand this command as well as it is very useful. I just don't want you to miss out on a cool command because you don't understand how it works.

I think you're right. I'm just not seeing something.

Let me put this into an example.

I have a macro that performs an action on a series of items, all with serial numbers. When the action is complete, a folder is named after that serial number. Inside that folder, a text file is also named after the serial number. Inside the text file, I am placing a date/time stamp.

For example:

Serial number 10001

folder created: 10001

file created: 10001.txt

contents of file: 05/10/2010 11:20:45 AM

My macro then goes through all the serial numbers and a large series of these folders/files exist.

The NEXT time the macro processes the SAME serial number, 10001, another time stamp is appended, and the contents of the file 10001.txt are as follows:

05/10/2010 11:20:45 AM, 05/11/2010 11:01:20 AM

My processing macro will act differently if the CURRENT date is less than 30 days later than the LAST PROCESSED date in the text file.

So basically, I always want to compare my current date against the FINAL value of the text file.

Cory · May 10, 2010

The NEXT time the macro processes the SAME serial number, 10001, another time stamp is appended, and the contents of the file 10001.txt are as follows:

05/10/2010 11:20:45 AM, 05/11/2010 11:01:20 AM

In your example the date stamps in the file are not on separate lines but rather separated with a comma and and a space? And your goal here is to get the last date stamp? If that's the case I think you sent us all on a tangent. You see normally when a person mentions a CSV file they're talking about a file with multiple values per row and multiple rows. You only have one row in this example. If so your understanding of ASCII File Process might be correct.

You see you said "Last Record". In data parlance with regards to text files a record is synonymous with a row. What you should have said was "Last Value" or "Last field of a record."

So let me give my advice to your problem. To get the last time stamp in your example I would read the whole file in as a string variable, get the length of the string, then count backward until I found the first comma, add two and copy that chunk. Like so:

Variable Set String set %Time Stamp% to the contents of c:\mylogfile.txt
Variable Set Integer %Length% to the length of variable %Time Stamp%
Repeat Start (Repeat %Length% times)
 Variable Modify String: Copy a substring in %Time Stamp%, starting at %Position% and 1 characters long to %Char%
 If Variable %Char% Equals ","
   Break
 End If
End Repeat
Variable Modify Integer %Position%: Increment
Variable Modify String %Time Stamp%: Delete a substring starting at 1 and %Position% characters long

Or this you can copy and paste:

<VARIABLE SET STRING Option="\x03" Destination="%Time Stamp%" Filename="c:\\mylogfile.txt" Strip="TRUE"/>
<VARIABLE SET INTEGER Option="\x0D" Destination="%Length%" Text_Variable="%Time Stamp%"/>
<REPEAT START Start="%Length%" Step="-1" Count="%Length%" Save="TRUE" Variable="%Position%"/>
<VARIABLE MODIFY STRING Option="\x09" Destination="%Char%" Variable="%Time Stamp%" Start="%Position%" Count="1"/>
<IF VARIABLE Variable="%Char%" Condition="\x00" Value="," IgnoreCase="FALSE"/>
<BREAK/>
<END IF/>
<END REPEAT/>
<VARIABLE MODIFY INTEGER Option="\x07" Destination="%Position%"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%Time Stamp%" Start="1" Count="%Position%"/>

You might also want to consider keeping them on seperate lines. Also might want to consider using decimal time vars to make teh 30 day calculation easy.

theSteve · May 13, 2010

You see you said "Last Record". In data parlance with regards to text files a record is synonymous with a row. What you should have said was "Last Value" or "Last field of a record."

Oops! Sorry.

So let me give my advice to your problem. To get the last time stamp in your example I would read the whole file in as a string variable, get the length of the string, then count backward until I found the first comma, add two and copy that chunk.

I'm a little embarassed that I didn't think of that myself...

Also might want to consider using decimal time vars to make teh 30 day calculation easy.

Another great suggestion but I made a date comparison macro... oh well, at least it was good for practice.

Thanks a lot for your help, Cory.

TheSteve

Process only LAST record from CSV?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation