Re: Parsing Excel file

[Trevor Joerges [SendMIME Software]]

Sorry about that Greg. You can try tab delimited and split on \t.   Trevor ----- Original Message ----- From: Greg Wardawy To: Perl-Win32-Users (E-mail) Sent: Tuesday, September 23, 2003 11:30 AM Subject: Re: Parsing Excel file Thanks Trevor, Do you know how to save Excel file using "|" instead of comma? I'm looking for it in Excel Help but no luck so far.   Greg ----- Original Message ----- From: Trevor Joerges [SendMIME Software] To: Greg Wardawy ; Perl-Win32-Users (E-mail) Sent: Tuesday, September 23, 2003 9:57 AM Subject: Re: Parsing Excel file I would just save it from Excel using something other than a comma delimiter, because your NOTE field has commas in the data, and just split each line on the delimiter you choose like a pipe perhaps "|".   You will of course have to do some other regex magic to parse the NOTE fields into DATE, TIME and NOTE.   open FILE, "<myfile"; while (<FILE>) {     my ( $customer, $note, $ph, $email, $fullname, $billing address, $shipaddress) = split( /|/, $_ );     # some regex stuff to further parse $note     ...     # print the whole thing back to another filehandle or to STDOUT. } close FILE;   Trevor ----- Original Message ----- From: Greg Wardawy To: Perl-Win32-Users (E-mail) Sent: Tuesday, September 23, 2003 10:34 AM Subject: Parsing Excel file Hello all, I'm not a big fan of Excel files so I was always saving them as csv files before working on the data. Well, it got me two days ago, I tried my old ways and wasn't able to find the right one and I need your advice. Here is the situation: 1. I have an Excel file in format like below (sorry about that, it doesn't look good pasted into the email) 2. I have to get rid of the last 5 columns (sure, I can delete them in Excel but that wouldn't be the Perl way) 3. I have to split the second column (Note) into three columns (Date, Time and Note) getting rid of *** and having the note in one line, some or all data can be missing 4. The output data should be in the following format:   Customer,Date,Time,Note 18 International,,,, 3 M,8/6/03,,PER S.J. NOTES SD PROMISED PYMT 8/8/03. OT 3 M,9/4/03,12:13P,CLD BUYERS V/ML LMTCB WENT BCK TO OPER AND LFT MESS ON GENERAL MESS GOT V/ML LMTCB. OT 3 M,9/8/03,9:51A,"CLD SPK TO BUYER SD PYMT WAS SMMTD FOR PYMT TO A/P OVER A MNTH AGO AND SD WE ND TO CL ROSA ESPIRIZUTA 512-984-7769 CLD SD CK# 391663 WAS MLD 9/2/03 AND IT'S COMING FROM MN., ADVSD IF DON'T RCVD BY 9/11/03 WLCB." OT 3-D Fire Protection,,,,   Can somebody push me in the right direction? Does saving this file as a csv and fighting with data in this format make sense? I just installed Spreadsheet-ParseExcel and Spreadsheet-ParseExcel-Simple modules. Any advice here? I have no experience at all on using them. Any advice is greatly appreciated. Thanks a lot.   Greg         Customer Note Phone Numbers Email Full Name Billing Address Shipping Address 18 International   Phone: (212) 586-8863 Fax: (212) 262-4030   Mr. Tim Longdale 666 Fifth Avenue New York NY 10103 USA   3 M 8/6/03 PER S.J. NOTES SD PROMISED PYMT 8/8/03. OT*** 9/4/03 (12:13P) CLD BUYERS V/ML LMTCB WENT BCK TO OPER AND LFT MESS ON GENERAL MESS GOT V/ML LMTCB. OT*** 9/8/03 (9:51A) CLD SPK TO BUYER SD PYMT WAS SMMTD FOR PYMT TO A/P OVER A MNTH AGO AND SD WE ND TO CL ROSA ESPIRIZUTA 512-984-7769 CLD SD CK# 391663 WAS MLD 9/2/03 AND IT'S COMING FROM MN., ADVSD IF DON'T RCVD BY 9/11/03 WLCB. OT*** Phone: (512) 984-6513 Fax: (512) 984-6867   Bill McDaniel 6801 Riverplane Blvd. Austin TX 78726 USA   3-D Fire Protection   Phone: (208) 525-8577 Fax: (208) 525-8381   Mr. Lamar Hayward P.O. Box 50845 Idaho Falls ID 83405-0845 USA