Solved

Report from CSV file Using AppleScript or Automator

Posted on 2013-01-04
21
1,277 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
ID: 38745608
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
ID: 38747217
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
ID: 38747421
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 12

Expert Comment

by:nxnw
ID: 38747475
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
ID: 38747565
OK how do we proceed figuring on using some spreadsheet program?
0
 
LVL 12

Expert Comment

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

Author Comment

by:sharingsunshine
ID: 38747787
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
ID: 38748050
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
ID: 38752155
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
ID: 38752383
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
 
LVL 12

Expert Comment

by:nxnw
ID: 38752783
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
ID: 38752943
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
ID: 38752950
copy the data file to the desktop, as I asked, and use the script verbatim.
0
 
LVL 12

Expert Comment

by:nxnw
ID: 38752961
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
ID: 38752973
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
ID: 38752989
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
ID: 38753023
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
ID: 38753028
Can you upload a sample data file?
0
 

Author Comment

by:sharingsunshine
ID: 38753111
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
ID: 38753125
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
ID: 38753214
thanks for your help and I did remove the commas for readability I should have made note of that.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
In this article we will discuss some EI Capitan Mail app issues and provide some manual process to resolve them.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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