Reading Pasted Excel Data using PHP

[ravoras]

Hey Guys,

So using a webform textarea I am passing some data into PHP.

This data is basically 4 columns of an excel sheet and potentially unlimited rows. Now I want to be able to go through this data, select each of the four individual elements from each row, insert these as a new row into a MySQL database and do this for each row pasted into the original textarea.

I was told that Excel separates new lines with a '\n' character and each column with a '\t' separator. So my approach was to use $rows=explode('\n',$data) and then explode each of the items in the resulting array using $row=explode('\t',$rows[0]). This is however not working, and I can't for the life of me figure out why.

This is a sample code which I found on the internet, and which I modelled my code on.

$arrayCode = array();
$rows = explode("\n", $str);
foreach($rows as $idx => $row)
{
    $row = explode( "\t", $row );

    foreach( $row as $field )
    {
        $arrayCode[$idx][] = $field;
    }
}
print_r( $arrayCode );

If anyone has any inputs on an alternative approach or what I am doing wrong that would be greatly appreciated. Please bear in mind that this data has to be copy pasted into a textarea from an excel document and I cannot import the .xlsx/.xls/.csv file directly into this. Thanks.

Best Regards
Sarovar Chandra

[ravoras]

Alright so heres an update, when I try to explode using \n character, all the data goes into the 0th element. So I am guessing that in the pasted data, new lines aren't separated ny '\n'!!

[Smeagel13]

A simpler solution would be to export from Excel as a CSV and then use PHP's buitlin functions to parse a CSV file : http://php.net/manual/en/function.fgetcsv.php

[LastMitch]

@ravoras

Please bear in mind that this data has to be copy pasted into a textarea from an excel document and I cannot import the .xlsx/.xls/.csv file directly into this

You can used this PHPExcel:

http://phpexcel.codeplex.com/

It imports .xlsx/.xls/.csv

I used PHPExcel before.

[ravoras]

Hey guys,

Thanks for the response. But like I said I wasn't looking to import the excel file. Its a long story but the data needs to be copy pasted.

Anyways I figured the solution incase anyone out there is interested.

So the thing is the data from this textarea was being read using javascript then I was using AJAX and GET to send this data to a PHP file that was going to process this data. This is something I should have mentioned up there, but if I knew it was relevant I wouldn't have needed to post this. Anyways after a lot of tinkering around I figured that data sent to a PHP file as a GET parameter using AJAX, is stripped of its '\n' and '\t' tags. I basically had to use Javascript to replace '\n' and '\t' with something entirely random like '%NL' and '%NT' before sending the data to PHP. After receiving the data on the server side I just edited my commands to explode using the new row/column separators.

Luckily I didn't need to use any additional classes/plugings/or extensions. Which is always good.. no?

Thanks again!

Best Regards
Sarovar Chandra

[Tracy_1]

Hi Ravoras,

I have the same scenario here, can you provide your sample codes for this pasting csv data to textarea get the array and then insert to mysql". Hope for your response thanks.

[skradoslav]

Easy, you are confusing "\n" and '\n', in single apostrophes its not treated like newline character, additionaly it can be separated on some systems by "\r\n"
Radoslav