• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1408
  • Last Modified:

Report from CSV file Using AppleScript or Automator

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
sharingsunshine
Asked:
sharingsunshine
  • 11
  • 10
1 Solution
 
sharingsunshineAuthor Commented:
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
 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
OK how do we proceed figuring on using some spreadsheet program?
0
 
nxnwCommented:
? You open the csv file in the spreadsheet and print it.
0
 
sharingsunshineAuthor Commented:
I understand that.  What I am saying is what is the grep and the applescript that is needed?
0
 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
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
 
nxnwCommented:
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
 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
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
 
nxnwCommented:
copy the data file to the desktop, as I asked, and use the script verbatim.
0
 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
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
 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
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
 
nxnwCommented:
Can you upload a sample data file?
0
 
sharingsunshineAuthor Commented:
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
 
nxnwCommented:
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
 
sharingsunshineAuthor Commented:
thanks for your help and I did remove the commas for readability I should have made note of that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now