Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: perl : number to date conversion in CSV file
Top Forums Shell Programming and Scripting perl : number to date conversion in CSV file Post 302702057 by giridhar276 on Tuesday 18th of September 2012 03:50:39 AM
Old 09-18-2012
perl : number to date conversion in CSV file
I have a CSV file in the below format.
while generating CSV file from excel sheet , date in excel sheet(Format :Mon 8/28/2012) got converted into the below format with numbers 41148,41149 so on.

Could anyone please let me know how to the convert the numbers(41148,41149 so on.) to its actual date(Format :Mon 8/28/2012).

Code:
abc.net1,BW: 1.07 M,,,,,,,,,,,,,,,,,,,,,,,
Hrly Avg (IN / OUT),0:00,1:00,2:00,3:00,4:00,5:00,6:00
41148,,,,,,,,,,,,,,,,,,,,,,,,
41149,,,,,,,,,,,,,,,,,,,,,,,,
41150,,,,,,,,,,,,,,,,,,,,,,,,
41151,,,,,,,,,,,,,,,,,,,,,,,,
41152,,,,,,,,,,,,,,,,,,,,,,,,
xyz1,BW: 1.07 M,,,,,,,,,,,,,,,,,,,,,,,
Hrly Avg (IN / OUT),0:00,1:00,2:00,3:00,4:00,5:00,6:00
41148,,,,,,,,,,,,,,,,,,,,,,,,
41149,,,,,,,,,,,,,,,,,,,,,,,,
41150,,,,,,,,,,,,,,,,,,,,,,,,
41151,,,,,,,,,,,,,,,,,,,,,,,,
41152,,,,,,,,,,,,,,,,,,,,,,,,

Expected format
Code:
abc.net1,BW: 1.07 M,,,,,,,,,,,,,,,,,,,,,,,
Hrly Avg (IN / OUT),0:00,1:00,2:00,3:00,4:00,5:00,6:00
Mon 08/27/2012,,,,,,,,,,,,,,,,,,,,,,,,
Tue 08/28/2012,,,,,,,,,,,,,,,,,,,,,,,,
Wed 08/29/2012,,,,,,,,,,,,,,,,,,,,,,,,
Thu 08/30/2012,,,,,,,,,,,,,,,,,,,,,,,,
Fri 08/31/2012,,,,,,,,,,,,,,,,,,,,,,,,


---------- Post updated at 10:50 PM ---------- Previous update was at 10:34 PM ----------

from CPAN modules, I got that DateTime::Format::Excel can do the work for me.
But not sure how to proceed with this module.
Code:
   use DateTime::Format::Excel;     my $datetime = DateTime::Format::Excel->parse_datetime( 37680 );     print $datetime->ymd();     # prints 2003-02-28

---------- Post updated 09-18-12 at 02:50 AM ---------- Previous update was 09-17-12 at 10:50 PM ----------

Could anyone please help me in solving this issue ?

Thanks in advance...

Regards,
GS
Last edited by giridhar276; 09-18-2012 at 05:03 AM..
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Conversion of .xls file to .csv file

Hi Folks, I've to convert manually couple of *.xls files to *.csv files everyday :( so i was just wondering if anyone could just help me with a shell script or awk script to automate the process :) PS : Problem is that i cannot use any third party software for the conversion. Thanking... (1 Reply)
Discussion started by: chaturvedi
1 Replies

2. Shell Programming and Scripting

Flat file to csv conversion

Hi Guy's can someone help me in converting the following I have a flat text file which has several thousand lines which I need to convert to a csv it's got a consistent format but basically want every time it hit's txt to create a new line with the subsequent lines comma delimited for example ... (6 Replies)
Discussion started by: p1_ben
6 Replies

3. Shell Programming and Scripting

Date string conversion within a file

Hi, I have a log file that contains information along the lines of the following: ========= jobnumber 322761 start_time Tue May 19 19:42:37 2009 end_time Tue May 19 20:11:28 2009 failed 0 ========= jobnumber 322762 start_time Tue May 19 19:39:51 2009 end_time ... (4 Replies)
Discussion started by: chrissycc
4 Replies

4. Shell Programming and Scripting

shell or perl script needed for ldif file to text file conversion

This is the ldf file dn: sdcsmsisdn=1000000049,sdcsDatabase=subscriberCache,dc=example,dc=com objectClass: sdcsSubscriber objectClass: top postalCode: 29600 sdcsServiceLevel: 10 sdcsCustomerType: 14 givenName: Adelia sdcsBlackListAll: FALSE sdcsOwnerType: T-Mobile sn: Actionteam... (1 Reply)
Discussion started by: LinuxFriend
1 Replies

5. Shell Programming and Scripting

Conversion of below Tabs Tex file into CSV format file : shell script needed

Request if some one could provide me shell script that converts the below "input file" to "CSV format file" given Name Domain Contact Phone Email Location ----------------------- ------------------------------------------------ ------- ----- ---------------------------------... (7 Replies)
Discussion started by: sreenath1037
7 Replies

6. Shell Programming and Scripting

Conversion of spaces Text file into CSV format file

Input file (each line is separaed by spaces )given below: Name Domain Contact Phone Email Location ----------------------- ------------------------------------------------ ------- -----... (18 Replies)
Discussion started by: sreenath1037
18 Replies

7. Shell Programming and Scripting

conversion of spaces into CSV format file

INput file attached in thread : Column widths at 24,73,82,87,121 characters (sed 's/./,/24;s/./,/73;s/./,/81;s/./,/87;s/./,/121;s/ *, */,/g' fixedinputfile >output.csv ). The client wants instead of hard coding the column widths as they are not fixed .he has given the hint stating that ( ... (3 Replies)
Discussion started by: sreenath1037
3 Replies

8. Shell Programming and Scripting

Subtract 2 date columns in .csv file and get output as number of days

Hi, I have one .csv file. I have 2 date columns present in file, column 2 and column 3. I need to calculate how many days exist between 2 dates. I am trying to subtract date column 2 from date column 3. Eg: my file look likes s.no, Start_date,End_Date 1, 7/29/2012,10/27/2012 2,... (9 Replies)
Discussion started by: Dimple
9 Replies

9. Shell Programming and Scripting

Date conversion DD-MMM-YY to YYYYMMDD for many columns in a file

Hi, Input Col1|col2|col3|col4|col5|col6-------col26 1|2|3|10-Nov-67|10-Nov-97|4|5-------100 1|2|3|11-Feb-01|01-Dec-15|4|5-------2000 Output Col1|col2|col3|col4|col5|col6-------col26 1|2|3|19671110|19971010|4|5-------100 1|2|3|20010211|20151201|4|5-------2000 I want to convert... (3 Replies)
Discussion started by: onesuri
3 Replies

10. UNIX for Beginners Questions & Answers

Conversion of Binary csv file

Hello, I have a binary csv file that was created on 'Red Hat Enterprise Linux Server release 6.6'. Now we have transferred all files on Ubuntu 16.04.2 LTS/xenial On opening the file in Ubuntu, there are special characters ... (8 Replies)
Discussion started by: nans
8 Replies

LEARN ABOUT DEBIAN

mongoexport

MONGOEXPORT(1)							  Mongo Database						    MONGOEXPORT(1)

NAME

       mongoexport - the Mongo export tool

SYNOPSIS

       mongoexport [OPTIONS]

DESCRIPTION

       mongoexport  is a tool to export a MongoDB collection to either JSON or CSV. The query can be filtered or a list of fields to output can be
       given.

       If the output is CSV, the fields must be specified in order.

EXAMPLES

       mongoexport -d test -c test1 --csv -f name,num
	      export documents from test.test1 in CSV format

OPTIONS

       --help show usage information

       -h, --host HOST
	      server to connect to (default HOST=localhost)

       -d, --db DATABASE
	      database to use

       -c, --c COLLECTION
	      collection to use

       -q, --query QUERY
	      query filter

       -f, --fields FIELDS
	      comma-separated list of field names

       --csv  export to CSV instead of JSON

       -o, --out FILE
	      output file, if not specified, stdout is used

       --dbpath PATH
	      directly access mongod data files in this path, instead of connecting to a mongod instance

COPYRIGHT

       Copyright 2007-2009 10gen

SEE ALSO

       For more information, please refer to the MongoDB wiki, available at http://www.mongodb.org.

AUTHOR

       Kristina Chodorow

10gen								     June 2009							    MONGOEXPORT(1)
All times are GMT -4. The time now is 11:48 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy