fgetcsv - issue with Excel CSV files and missing column

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
skattabrain
Forum Newbie
Posts: 2
Joined: Wed Oct 17, 2007 10:04 am

fgetcsv - issue with Excel CSV files and missing column

Post by skattabrain »

hello,

i'm losing it tryign to deal with MS Excel CSV files. It seems if you have a 5 column file, and a row has only 4 columns of data (the last column being the empty column), MS decides to not include a delimiter for the empty column.

for example ...

row1, data, data, data, last column
row2, data, data, data, last column
row3, data, data, data
row4, data, data, data, last column

row3 will break my MySQL insert process. how can i manipulate my code to add a comma?

this would be easy if this was an import tool designed to work the same process over and over. but all of my users csv files can be set up differently, so i have a match column to db field function and often that means the number of columns change. 1 user may have 3 cols, another has more detailed data and has 5 or 6. but if i set up to handle 5 ... then one row pulls 4 ... the mysql insert fails as there's a column mismatch.

any ideas?

btw ... this mostly isn't my code, i'm trying to hack this tool (http://www.phpcsvimporter.co.uk/)which seems to have gone dead support wise

Code: Select all

//  Get contents, while below preview limit and there's data to be read

				while ($data = fgetcsv($fp, 1024, delimiter_to_char($_SESSION['csv_delimiter']))) {
				
					if ($rc < $start_row) {
					
						//  Incremement counter
						$rc++;
						
						
						//  Go to next loop
						continue;
						
					} else {
				
						//  Array to store data to be inputted
						$values = array();
					
						//  Loop data
						for ($i = 0; $i < count($data); $i++) {
						
							//  If data is wanted, put data into array
							if (array_key_exists($i, $column_index)) {
								$values[$column_index[$i]] = trim($data[$i]);
								
							}
						
						}
	
						//  Sort array into correct order by index
						ksort($values);
						
						//  Join values together and store in array  
							
						$batch[] = '("' . implode('", "', str_replace('"', '\"', $values)) . '")';


					
					}
skattabrain
Forum Newbie
Posts: 2
Joined: Wed Oct 17, 2007 10:04 am

Post by skattabrain »

just in case someone else runs into this problem in the future ... maybe there's a more elegant solution, but instead i jsut push an extra column onto each csv record. for my particular code it fixes Excels shortcomings by adding extra columns ... which my code then ignores.

i guess i should push 2 empty columns ... so if the following SQL is expecting 5 columns and 1 line has only 3 ... it will still work.

Code: Select all

//  Get contents, while below preview limit and there's data to be read
				while ($data = fgetcsv($fp, 1024, delimiter_to_char($_SESSION['csv_delimiter']))) {
				
				array_push($data, "");
Post Reply