Solved

Report from CSV file Using AppleScript or Automator

Posted on 2013-01-04
21
1,248 Views
Last Modified: 2013-01-07
I need to massage a csv file and create a report.  I only want to print out the report that has values that have parenthesis around them.  There are other rows that come from the csv file and those I don't want printed.  The report just needs to print out all the rows that match the criteria and then have a total of the amounts with parenthesis around them.

If possible I need to confine this to automator and/or applescript because the user isn't able to do command line or anything real complicated.

Thanks,
0
Comment
Question by:sharingsunshine
  • 11
  • 10
21 Comments
 

Author Comment

by:sharingsunshine
Comment Utility
The file looks like this

Account,  Date,      Check#,  Description,          Category,  Tags,  C,   Amount,  Running Total
Chkg      12/12/12               MO. BUDGET IN      Household                     50.00     50.00
Chkg      12/12/13     125       Groceries              Household                     (25.00)    25.00

If the line item is one with a (around the total) then I want to print that record and at the end of the report I just need an amount of all the records that are printed.
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
Do you know GREP?
A one line UNIX command could delete all the lines that have no numbers in parentheses. Then the user could just open up the remaining data in excel and print it out. The UNIX command could then be inserted into an AppleScript app.
0
 

Author Comment

by:sharingsunshine
Comment Utility
I am familiar with GREP and real familiar with regex.  My only issue is that the user doesn't have Excel.  So is there another way to print it out?  AppleScript is something I am learning so that will work great.
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
The right tool for the job of printing out a csv file is a spreadsheet.

To be brutally blunt, your user can spend $100 on MS office. If your user is a super tightwad, he can use neooffice. It makes no sense to code something to columnize and print a csv file, instead of using readily available tools.
0
 

Author Comment

by:sharingsunshine
Comment Utility
OK how do we proceed figuring on using some spreadsheet program?
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
? You open the csv file in the spreadsheet and print it.
0
 

Author Comment

by:sharingsunshine
Comment Utility
I understand that.  What I am saying is what is the grep and the applescript that is needed?
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
the applescript will be something like:
do shell script "<your regex command here>" -- the command should massage the data as required and pipe it into a new file named thefile.csv
tell application "Microsoft Excel"
	open file "Users:admin:Desktop:thefile.csv"
end tell

Open in new window

You save the applescript as an applet. In its simplest form, your user has a file, always with the same name, in the same place (here Users:admin:Desktop:thefile.csv).

I assume you can work out the regex command yourself in the terminal
0
 

Author Comment

by:sharingsunshine
Comment Utility
Here is what I worked out on the grep command.  The negative numbers all had the text string in them so I chose to do it that way.  I also wasn't real clear how to do the parenthesis in grep.

grep -vn "MO. BUDGET IN" rjw_contributions.csv >> thefile.csv

here is the script

do shell script "grep -vn \"MO. BUDGET IN\" rjw_contributions.csv >> thefile.csv" -- the command should massage the data as required and pipe it into a new file named thefile.csv
tell application "Microsoft Excel"
      open file "Users:Downloads:thefile.csv"
end tell

I am getting this error:  error "sh: thefile.csv: Permission denied" number 1
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
One thing at a time. Your command has no grep pattern in it.

The following terminal command should do the job:

egrep -e '[\(\)]' ~/Desktop/test.csv > ~/Desktop/thefile.csv

put a copy of your data file on your desktop and name it "test.csv". After you run the command, it should put massaged data you want in a new file on your desktop called "thefile.csv". The grep pattern should probably be more specific, as this will find a line as long as it has either a left or right parenthesis, but this will do for the moment. Feel free to improve the pattern.

If it works, test it verbatim in your applescript, STILL USING THE DESKTOP FOLDER FOR NOW.

BTW, the double >> will append to thefile.csv and the single > will overwrite it. It seems to me that appending is not appropriate here.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 12

Expert Comment

by:nxnw
Comment Utility
The following works, given a source file on the desktop, and the converted file being saved to the desktop (overwriting the previous copy).

The GREP pattern is also better - an open parenthesis followed by a number between 1 and 9.
do shell script "egrep -e '[(][1-9]' ~/Desktop/test.csv > ~/Desktop/thefile.csv"
tell application "Microsoft Excel" to open file ((path to desktop folder) & "thefile.csv")

Open in new window

I would suggest one or two more things:
- that the working folder be buried a bit, rather than using the desktop
- that the script first makes a copy of the original data file to the working folder, so that there is never user access to that file;
0
 

Author Comment

by:sharingsunshine
Comment Utility
the file thefile.csv is empty when I run this command thru terminal

egrep -e '[(] [1-9]' ~/Downloads/rjw_contributions.csv > ~/Downloads/thefile.csv
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
copy the data file to the desktop, as I asked, and use the script verbatim.
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
also, it looks like you have a space in the grep pattern between the parenthesis and the number. That would cause it to fail (unless there is supposed to be a space there, which I doubt).
0
 

Author Comment

by:sharingsunshine
Comment Utility
here is what I am running from the AppleScript editor

do shell script "egrep -e '[(][1-9]'~/Desktop/rjw_contributions.csv > ~/Desktop/thefile.csv"
tell application "Microsoft Excel" to open file ((path to desktop folder) & "thefile.csv")

it gives this error: error "The command exited with a non-zero status." number 1
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
Now, I see that you are missing a space between the pattern and the file path.

Copy and paste the code I gave you into applescript editor. Make a copy of the data file on the desktop, and name it "test.csv".

I do not want to spend any more time looking for syntax errors you have introduced in this manner.
0
 

Author Comment

by:sharingsunshine
Comment Utility
I used

do shell script "egrep -e '[(][1-9]' ~/Desktop/test.csv > ~/Desktop/thefile.csv"
tell application "Microsoft Excel" to open file ((path to desktop folder) & "thefile.csv") which is what you included above.

It is giving numbers that don't have parenthesis which I don't want.
0
 
LVL 12

Expert Comment

by:nxnw
Comment Utility
Can you upload a sample data file?
0
 

Author Comment

by:sharingsunshine
Comment Utility
it is income related and I can dummy it up but before I do that.  I think I found the problem.  There is a set of parenthesis on the lines that I don't want included.  See below

Account,  Date,      Check#,  Description,          Category,  Tags,  C,   Amount,  Running Total
Chkg      12/12/12               MO. BUDGET IN      Household  (45)                   50.00     50.00
Chkg      12/12/13     125       Groceries              Household (45)                    (25.00)    25.00

Changing it to this it works
do shell script "egrep -v 'MO. BUDGET IN' ~/Desktop/test.csv > ~/Desktop/thefile.csv"
tell application "Microsoft Excel" to open file ((path to desktop folder) & "thefile.csv")

do you see any problem with this solution?
0
 
LVL 12

Accepted Solution

by:
nxnw earned 500 total points
Comment Utility
if the string 'MO. BUDGET IN' is in every single line you don't want, it should be fine.

I am assuming you changed the commas to spaces in your example for readability. Otherwise, it is not a csv file and it won't open correctly.
0
 

Author Closing Comment

by:sharingsunshine
Comment Utility
thanks for your help and I did remove the commas for readability I should have made note of that.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Deploystudio is a system which can be used to deploy OSX clients and servers within the small/medium or large business environments. The system is built onto of the OSX Server NetBoot system and uses images & workflows as its core assets. While work…
The error "There was an error performing the update" occurred on a Mac OS X client workstation running  Symantec AntiVirus for Mac (http://www.symantec.com/business/products/purchasing.jsp?pcid=pcat_security&pvid=825_1) - the Enterprise product vers…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

13 Experts available now in Live!

Get 1:1 Help Now