Sponsored Content
Full Discussion: Format CSV file
Top Forums Shell Programming and Scripting Format CSV file Post 302316342 by _tina_ on Thursday 14th of May 2009 08:44:14 PM
Old 05-14-2009
Quote:
Originally Posted by colemar
I am puzzled.

result.csv above already has no "redundant data" (i.e. the hours are added up over each month-year, it has exactly one line per name-month-year, and the month-year is in the fourth field).
Why can't you import result.csv in Excel?
Because this is a file with thousands of records containing bi-weekly hours, which need to be added up to show the amount of hours per month worked on the same project by the same employee. If I just import into excel as is, then I would have to sift through all those records, add titles... and the month name for each record as well as add up the total hours per month per project, per user.



Quote:
Originally Posted by ghostdog74
so show a proper set of input data, and show your desired output as well...
Okay, thanks for the reply.


This is the raw csv data:
john,project,1/1/09,1/15/2009,misc notes,hours
john,project,1/16/09,1/31/2009,misc notes,hours
john,project,2/1/09,2/15/2009,misc notes,hours
...

I need to output as follows:

Period: January

Username: Project: Start Date: End Date: Notes: Total Hours
John Project 01/01/09 01/31/09 Notes... 55.0


Period: February

Username: Project: Start Date: End Date: Notes: Total Hours
John Project 02/01/09 02/28/09 Notes... 65.0

Period: January

Username: Project: Start Date: End Date: Notes: Total Hours
Mary Project 01/01/09 01/31/09 Notes... 55.0


Period: February

Username: Project: Start Date: End Date: Notes: Total Hours
Mary Project 02/01/09 02/28/09 Notes... 65.0


Sorry about the formatting above, I can't line up the desired outcome text. But basically, the column name with the data underneath.

Total hours per staff member, per month.

Thanks again.

Last edited by _tina_; 05-14-2009 at 09:52 PM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

How to Parse a CSV file into a Different Format

Hi I have a CSV file with me in this format Currency, USD, EUR, USD, 1.00, 1.32, EUR, 0.66, 1.00, How do I transpose the file to get to the format below. currency, currency, rate USD, USD, 1.00 USD, EUR, 1.32 EUR, USD, 0.66 EUR, EUR, 1.00 Thanks for your help We are using... (2 Replies)
Discussion started by: cdesiks
2 Replies

2. Shell Programming and Scripting

AWK CSV to TXT format, TXT file not in a correct column format

HI guys, I have created a script to read 1 column in a csv file and then place it in text file. However, when i checked out the text file, it is not in a column format... Example: CSV file contains name,age aa,11 bb,22 cc,33 After using awk to get first column TXT file... (1 Reply)
Discussion started by: mdap
1 Replies

3. Shell Programming and Scripting

changing the format of CSV file

Hi Experts, Please help me to get the following from script for Unix ENvironment(shell, perl, tr, sed, awk). INPUT FILE: 20K,ME,592971 20K,YOU,2 20K,HE,1244998 50K,YOU,480110 50K,ME,17 50K,HIS,10 50K,HE,1370391 OUTPUT FILE: K,ME,YOU,HE,HIS 20K,592971,2,1244998,0... (5 Replies)
Discussion started by: ashis.tewari
5 Replies

4. Shell Programming and Scripting

Format a date in a csv file

So I have a csv file where the 3rd field is a date string in the format yyyy-mm-dd. I need to change it to mm/dd/yyyy. So each line in the csv file looks like: StringData,StringData,2009-02-17,12.345,StringData StringData,StringData,2009-02-16,65.789,StringData Any idea how I can keep... (5 Replies)
Discussion started by: rpiller
5 Replies

5. Shell Programming and Scripting

Format txt file to CSV

Hi All, I have a file with content FLIGHT PLANS PRODUCED ON 26.08.2008(SORTED BY FPLAN NUMBER) RUN DATED 27/08/08 PAGE 1 -------------------------------------------------------------- FPLAN FPLAN PRE BTCH BATCH POST BTCH BATCH BATCH ... (1 Reply)
Discussion started by: digitalrg
1 Replies

6. Shell Programming and Scripting

format output in csv file

I am sending the output of a file to .csv file. The output should look like this: Total Customers Processed:,8 Total Customers Skipped:,0 Total Customers Added:,8 Total Customers Changed:,0 Total Policies Deleted:,0 Total Policies Failed:,0 total:,8 Now i want this output in... (1 Reply)
Discussion started by: Prashant Jain
1 Replies

7. Shell Programming and Scripting

Convert the below file to csv format

Hi , i want to change this question, i will post soon.. (6 Replies)
Discussion started by: srikanth2567
6 Replies

8. UNIX Desktop Questions & Answers

Format csv file using Unix

Hi All, I have an csv file with three rows, where first containing header deatils. is there any way to make the first row to appear bold using UNIX command. Input File: Name Rank arun 1 babu 2 Expected Output: Name Rank arun 1 babu 2 (7 Replies)
Discussion started by: arunmanas
7 Replies

9. Shell Programming and Scripting

Format csv file

Hi, I need to make some changes in a csv file using awk or perl. Unfortunately, all my attempts have led to nothing so I hope you guys can help me. I have the following example input file including header(original file has 35 fields): ABC: DE Time: 2012/09/07... (3 Replies)
Discussion started by: Subbeh
3 Replies

10. Shell Programming and Scripting

Match list of strings in File A and compare with File B, C and write to a output file in CSV format

Hi Friends, I'm a great fan of this forum... it has helped me tone my skills in shell scripting. I have a challenge here, which I'm sure you guys would help me in achieving... File A has a list of job ids and I need to compare this with the File B (*.log) and File C (extend *.log) and copy... (6 Replies)
Discussion started by: asnandhakumar
6 Replies
holidays(4)							   File Formats 						       holidays(4)

NAME
holidays - prime/nonprime table for the accounting system SYNOPSIS
/etc/acct/holidays DESCRIPTION
The /etc/acct/holidays file specifies prime time hours and holidays. Holidays and weekends are considered non-prime time hours. /etc/acct/holidays is used by the accounting system. All lines beginning with an * are comments. The /etc/acct/holidays file consists of two sections. The first non-comment line defines the current year and the start time of prime and non-prime time hours, in the form of: current_year prime_start non_prime_start Specify prime_start and non_prime_start times in the range of 0000 to 2400. The remaining non-comment lines define the holidays in the form of: month/day company_holiday Of these two fields, only the month/day is actually used by the accounting system programs. The /etc/acct/holidays file must be updated every year. EXAMPLES
Example 1 An Example of the /etc/acct/holidays File The following is an example of the /etc/acct/holidays file: * Prime/Nonprime Table for the accounting system * * Curr Prime Non-Prime * Year Start Start * 1991 0830 1800 * * only the first column (month/day) is significant. * * month/day Company Holiday * 1/1 New Years Day 5/30 Memorial Day 7/4 Indep. Day 9/5 Labor Day 11/24 Thanksgiving Day 11/25 day after Thanksgiving 12/25 Christmas 12/26 day after Christmas SEE ALSO
acct(1M) SunOS 5.11 18 Aug 2008 holidays(4)
All times are GMT -4. The time now is 07:08 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy