Solved

Report from CSV file Using AppleScript or Automator

Posted on 2013-01-04
21
1,301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

How can this article save you time AND money?  In just a few minutes you may discover something you didn't know existed that is easy enough for you to fix yourself!
Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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