Forum Moderators: coopster
SAMPLE CSV FILE (third row has the problem):
DEBIT,2009-01-07,"TELEPHONE SERVICE IN",-5.03
CREDIT,2009-01-06,"ATM CHECK DEPOSIT",75.00
DEBIT,2009-01-05,"COMPANY, INC. MONTHLY SERVICES",-35.96
DEBIT,2009-01-05,"PAYMENT transaction#: 184934",-200.00
MY CODE:
<?php
$csv_data = $_POST['csv_data'];
$csv_array = explode("\n",$csv_data);
// Loop through all CSV data rows and generate separate
$last_data_row = count($csv_array) - 1;//this is necessary because arrays start with position #0
for($counter = 0; $counter < $last_data_row; $counter++)
{
$data_row = explode(",",$csv_array[$counter]);
$TransactionType = $data_row['0'];
$TransactionDate = $data_row['1'];
$TransactionDescription = $data_row['2'];
$TransactionAmount = $data_row['3'];
$query = "INSERT INTO Transactions (TransactionType, TransactionDate, TransactionDescription, TransactionAmount) VALUES('$TransactionType','$TransactionDate','$TransactionDescription','$TransactionAmount'); ";
echo "$query <br />";
}
?>
<?php
$csv_data = $_POST['csv_data'];
$csv_array = explode("\n",$csv_data);
// Loop through all CSV data rows and generate separate
$last_data_row = count($csv_array) - 1;//this is necessary because arrays start with position #0
for($counter = 0; $counter < $last_data_row; $counter++)
{
$data_row = explode(",",$csv_array[$counter]);
//just in case there were commas in the 'description' portion we are taking the first few and the last...then clumping the leftovers (2 + [3, 4, 5...]) in the 'description'
$RowsInArray = count($data_row) - 1;
$TransactionType = $data_row['0'];
$TransactionDate = $data_row['1'];
$TransactionDescription = $data_row['2'];
for( $descriptcounter = 3; $descriptcounter <= ($RowsInArray-1); $descriptcounter++)
{
$TransactionDescription .= ', '.$data_row[$descriptcounter];
}
$TransactionAmount = trim($data_row[$RowsInArray]);
$query = "INSERT INTO Transactions (TransactionType, TransactionDate, TransactionDescription, TransactionAmount) VALUES('$TransactionType','$TransactionDate','$TransactionDescription','$TransactionAmount'); ";
echo "$query <br />";
}
?>
make believe that the whole element is textual
by placing it within escaped double quotes
$address = "\"$address\"";
A little trick I picked up from somewhere :)
wish I had this simple idea first!
$sql = "INSERT INTO $table ($fields) VALUES ("; //Read the post string as csv
$row_count = 0;
while (($data = str_getcsv($_POST['csv_data'])) !== false) {
++$row_count;
foreach ($data as $key => $value) {
$clean_data = mysql_real_escape_string((get_magic_quotes_gpc())? stripslashes($value): $value);
$query_string .= "'" . $clean_data . "',";
}
$query_string = substr($query_string, 0, -4);
$query_string .="),(";
}
$sql = substr($query_string, 0, -2);
print $sql;
I am trying to parse a CSV file
So, I thought I would make it obvious that the exact function exists.
I should clean the dust off my monitor
coopster/bkeep: Thanks for pointing out this function. I had no idea that this existed. I have not yet tried playing around with it, because the scripting I wrote does do the job...however I plan to re-write it with fgetcsv just to learn how it works. You are both correct...the real (eventual) plan is to set up my script to import the actual file and parse that. However, at the moment I have it just parsing an incoming posted string for ease of troubleshooting. THANKS for letting me know about this function, I kinda feel like i just re-invented he wheel. Oh well...I guess it was a good exercise in problem solving :-)
I bet that the built-in CSV functions within PHP (fgetcsv and str_getcsv) will work beautifully, but in the meantime the workaround i wrote (posted above) is also working well.
Thanks!
