fork download
copy 
  1. ####################################################################################################
  2. ## Program_Name     ## Sales_Channel_Merge.pl                                                     ##
  3. ## Written By       ## Martin Roberts                                                             ##
  4. ## Date             ## 5th January 2010                                                           ##
  5. ## Parameters       ##                                                                            ##
  6. ## Description      ## Checks the input directory for all Sales Channel files.  Converts each     ##
  7. ##                  ## file into a series of csv files for each worksheet, suffixed by a unique   ##
  8. ##                  ## identifier.  Concatenates each resulting file of the same class to         ##
  9. ##                  ## produce a series of uniquely named files for each worksheet that           ##
  10. ##                  ## represents a concatenation of all data from all Sales Channel workbooks    ##
  11. ##                  ## Orgininal Excel files are moved to an Archive directory.                   ##
  12. ####################################################################################################
  13. ##                                       Change Log                                               ##
  14. ####################################################################################################
  15. ## Date     ##       Author        ##  Change Ref.   ##          Change Description               ##
  16. ##          ##                     ##                ##                                           ##                        
  17. ##          ##                     ##                ##                                           ##                        
  18. ##          ##                     ##                ##                                           ##                        
  19. ####################################################################################################
  20.  
  21. # INCLUDE NON_STANDARD PACKAGES
  22. use strict "vars";
  23. use Spreadsheet::ParseExcel;
  24. use File::Copy;
  25.  
  26. # DEFINE DIRECTORIES AND FILES FOR USE IN PROCESS#
  27. my $log_dir = "C:\Documents and Settings\459964\Desktop\Test";
  28. my $log_file = "EXCEL_TO_CSV_Merge.log";
  29. my $base_dir = "C:\Documents and Settings\459964\Desktop\Test";
  30.  
  31. # DECLARE GLOBAL VARIABLES
  32. my $log_text = "";
  33. my $file_text = "";
  34. my $current_file = "";
  35. my $script_name = "EXCEL_TO_CSV_Merge.pl";
  36. my @Now = localtime(time());
  37. my @file_contents;
  38. my @worksheet_names;
  39. my @file_list;
  40. my $sheet_no = 0;
  41.  
  42.  
  43. # RECORD DETAILS OF REQEST FOR LOGGING
  44. my $date = &getDate();
  45. $log_text = "$date\nExcel File List :\n";
  46.  
  47. # RETRIEVE AND VALIDATE INPUT PARAMETERS
  48. if ($ARGV[0] eq null || length($ARGV[0]) == 0 || $ARGV[1] eq null || length($ARGV[1]) == 0
  49. || $ARGV[2] eq null || length($ARGV[2]) == 0  || $ARGV[3] eq null || length($ARGV[3]) == 0)	{
  50. 	error("Invalid Input Parameters to $script_name");
  51. }
  52. my $file_pattern = $ARGV[0];
  53. my $source_dir = "$base_dir\\$ARGV[1]";
  54. my $target_dir = "$base_dir\\$ARGV[2]";
  55. my $delimiter = $ARGV[3];
  56. $log_text = $log_text . "$script_name Called with Parameters\nFile Pattern - $ARGV[0]\nSource Directory - $ARGV[1]\nTarget Directory - $ARGV[2]\nDelimeter - $ARGV[3]\n";
  57. my $temp_dir = $source_dir . "\\Temp";
  58. my $archive_dir = $source_dir . "\\Archive";
  59.  
  60. # SCAN SOURCE DIRECTORY FOR FILES
  61. chdir("$source_dir") or error("Unable to change to the Sales Channel Source Directory");
  62. my @files_found = <$file_pattern*>;
  63.  
  64. # CHECK TEMP DIRECTORY EXISTS, OTHERWISE CREATE IT
  65. if (! -d "$temp_dir")	{
  66. 	mkdir("Temp");
  67. }
  68.  
  69. # CHECK WORKSHEET ARCHIVE DIRECTORY EXISTS, OTHERWISE CREATE IT
  70. if (! -d "$archive_dir")	{
  71. 	mkdir("Archive");
  72. }
  73.  
  74. # CONVERT EACH EXCEL FILE INTO ITS COMPONENT WORKSHEET CSV FILES
  75. my $file_id = 1;
  76. foreach my $file (@files_found)	{
  77. 	$log_text = $log_text . $file . "\n";
  78. 	convertExcelToCsv($file, $file_id);
  79. 	# MOVE EXCEL FILE FROM SOURCE TO ARCHIVE
  80. 	move("$source_dir\\$file","$archive_dir\\$file") or error("Unable to move Excel File $archive_dir\\$file to Archive Directory");
  81. 	$file_id++;
  82. }
  83.  
  84. # MERGE WORKSHEETS OF SAME NAME
  85. my $ws_no = 0;
  86. foreach my $worksheet_name (@worksheet_names)	{
  87. 	open (OUTPUT, ">$target_dir\\$worksheet_name.csv") or error("Unable to open Output File - $target_dir\\$worksheet_name");
  88. 	close OUTPUT;
  89. 	my $file_string = @file_list[$ws_no];
  90. 	my (@file_string_components) = split(/\|\|/,$file_string);	
  91. 	my $sheet_ind = 0;
  92. 	foreach my $file_found (@file_string_components)	{
  93. 		concatenateFiles("$source_dir\\$worksheet_name.csv","$temp_dir\\$file_found",$sheet_ind);
  94. 		#DELETE TEMP FILES
  95. 		unlink("$temp_dir\\$file_found") or error("Unable to Delete Temp File, $temp_dir\\$file_found");
  96. 		$sheet_ind++;
  97. 	}
  98.  
  99. 	$ws_no++;
  100. }
  101.  
  102. sub concatenateFiles	{
  103.  
  104. 	my($target_file, $source_file, $sheet_id) = @_;
  105.  
  106. 	# CONCATENATE FILES
  107. 	open (SOURCE,"<$source_file") or error("Unable to open Source File - $source_file");
  108. 	open (TARGET, ">>$target_file") or error("Unable to open Target File - $target_file");
  109. 	flock (TARGET,2);
  110. 	my $line = "";
  111. 	my $line_id = 0;
  112. 	while ($line = <SOURCE>)	{
  113. 		# ONLY PRINT HEADER ROW FOR FIRST WORKSHEET FILE
  114. 		if ($line_id != 0 ||($line_id == 0 && $sheet_id == 0))	{
  115. 			print TARGET $line; 
  116. 		}
  117. 		$line_id++;
  118. 	}
  119. 	close SOURCE;
  120. 	close TARGET;
  121.  
  122. }
  123.  
  124. sub convertExcelToCsv	{
  125.  
  126. 	my($excel_file, $file_id) = @_;
  127.  
  128. 	# OPEN EXCEL FILE 
  129. 	my $Excel = Spreadsheet::ParseExcel::Workbook->Parse("$source_dir/$excel_file") or error ("Unable to read File"); 
  130.  
  131. 	my $line = "";
  132.  
  133. 	# ITERATE THROUGH WORKSHEETS
  134. 	foreach my $sheet (@{$Excel->{Worksheet}}) { 
  135. 		my $sheet_name = $sheet->{Name};
  136. 		my $csv_file =  $sheet_name . "_" . $file_id . ".csv";
  137. 		if (!existsWorksheetName($sheet_name))	{
  138. 			@worksheet_names[$sheet_no] = $sheet_name;
  139. 			@file_list[$sheet_no] = $csv_file;
  140. 			$sheet_no++;
  141. 		}
  142. 		else	{
  143. 			my $existing_sheet_no = getExistingSheetNo($sheet_name);
  144. 			@file_list[$existing_sheet_no] = @file_list[$existing_sheet_no] . "||" . $csv_file;
  145. 		}
  146. 		# OPEN CSV FILE
  147. 		open( fileS, ">$temp_dir/$csv_file"); 
  148. 		flock(fileS, 2);
  149. 		$sheet->{MaxRow} ||= $sheet->{MinRow};
  150. 		# ITERATE THROUGH ROWS
  151.  
  152. 		my $is_blank_row = 1;
  153.  
  154. 		foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow})	{
  155. 			$sheet->{MaxCol} ||= $sheet->{MinCol};
  156. 			# ITERATE THROUGH COLUMNS
  157. 			foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol})	{
  158. 				# ADD DELIMITER BEFORE EACH VALUE, EXCEPT FIRST
  159. 				if ($col > 0)	{
  160. 					$line = $line . $delimiter;
  161. 				}
  162. 				my $cell = $sheet->{Cells}[$row][$col];
  163. 				# ENCLOSE THE CELL VALUE IN DOUBLE QUOTES AND ADD TO THE DELIMITED ROW
  164. 				if ($cell)	{
  165. 					# INDICATE IF THE ROW CONTAINS NON BLANK CELLS
  166. 					if (length($cell->{Val}) > 0)	{
  167. 						$is_blank_row = 0;
  168. 					}
  169. 					$line = $line . "\"" . $cell->{Val} . "\"";
  170. 				}
  171.  
  172. 			}
  173. 			# IF THE NON BLANK ROW INDICATOR IS SET THEN PRINT THE ROW
  174. 			if ($is_blank_row == 0)	{
  175. 				print fileS "$line\n";
  176. 			}
  177. 			$line = "";
  178. 			$is_blank_row = 1;
  179. 		}
  180. 		close(fileS);
  181. 	}
  182. }
  183.  
  184.  
  185. writeLogFile();
  186.  
  187. sub existsWorksheetName	{
  188.  
  189. 	my($ws_name) = @_;
  190. 	my $found = 0;
  191.  
  192. 	foreach my $ws_id (@worksheet_names)	{
  193. 		if ($ws_id eq $ws_name)	{
  194. 			$found = 1;
  195. 		}
  196. 	}
  197. 	return $found;
  198. }
  199.  
  200. sub getExistingSheetNo	{
  201.  
  202. 	my($ws_name) = @_;
  203. 	my $id = 0;
  204. 	my $sheet_id = 0;
  205.  
  206. 	foreach my $ws_id (@worksheet_names)	{
  207. 		if ($ws_id eq $ws_name)	{
  208. 			$sheet_id = $id;
  209. 		}
  210. 		$id++;
  211. 	}
  212.  
  213. 	return $sheet_id;
  214. }
  215.  
  216.  
  217. sub getDate	{
  218. 	my @Weekdays = ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');
  219. 	my @Months = ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
  220. 	my $Month = $Months[$Now[4]];
  221. 	my $Weekday = $Weekdays[$Now[6]];
  222. 	my $Hour = $Now[2];
  223. 	my $AMPM = "";
  224. 	if ($Hour > 12) {
  225. 		$Hour = $Hour - 12;
  226. 		$AMPM = "PM";
  227. 	} else {
  228. 		$Hour = 12 if $Hour == 0;
  229. 		$AMPM ="AM";
  230. 	}
  231. 	my $Minute = $Now[1];
  232. 	$Minute = "0$Minute" if $Minute < 10;
  233. 	my $Year = $Now[5]+1900;
  234. 	return "$Weekday, $Month $Now[3], $Year $Hour:$Minute $AMPM";
  235. }
  236.  
  237. sub error	{
  238. 	my($err_id) = @_;
  239. 	$file_text = "";
  240. 	$log_text = $log_text . "Exception Found - $err_id\n";	
  241. 	writeLogFile();
  242. 	exit;
  243. }
  244.  
  245. sub writeLogFile	{
  246. 	open( fileLOG, ">>$log_dir/$log_file"); 
  247. 	flock(fileLOG, 2);
  248. 	print fileLOG "$log_text\n";
  249. 	close(fileLOG);
  250. }
  251.  
  252.  
Compilation error #stdin compilation error #stdout 0s 0KB
comments (?)
stdin
copy 
Standard input is empty
compilation info 
===SORRY!===
Unable to find module 'strict' in the @*INC directories.
(@*INC contains:
  /home/Rj9bkm/.perl6/lib
  /usr/lib/parrot/2.7.0/languages/perl6/lib
  .)
stdout
copy 
Standard output is empty
https://ideone.com/BI0hO
language:
Perl (perl 2018.12)
created:
14 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!