如何上传csv文件，然后用Ajax解析其内容？

Question

I already have a PHP script to upload a CSV file: it's a collection of tweets associated to a Twitter account (aka a brand). BTW, Thanks T.A.G.S :)

I also have a script to parse this CSV file: I need to extract emojis, hashtags, links, retweets, mentions, and many more details I need to compute for each tweet (it's for my research project: digital affectiveness. I've already stored 280k tweets, with 170k emojis inside).

Then each tweet and its metrics are saved in a database (table TWEETS), as well as emojis (table EMOJIS), as well as account stats (table BRANDS).

I use a class quite similar to this one: CsvImporter > https://gist.github.com/Tazeg/b1db2c634651c574e0f8. I made a loop to parse each line 1 by 1.

$importer = new CsvImporter($uploadfile,true); 
        while($content = $importer->get(1)) {
            $pack = $content[0];
            $data = array();
            foreach($pack as $key=>$value) {
                $data[]= $value;
            }
            $id_str = $data[0];
            $from_user = $data[1];

...

After all my computs, I "INSERT INTO TWEETS VALUES(...)", same with EMOJIS. The after, I have to make some other operations

1. update reach for each id_str, if a tweet I saved is a reply to a previous tweet)
2. save stats to table BRAND

All these operations are scripted in a single file, insert.php, and triggered when I submit my upload form. But everything falls down if there is too many tweets. My server cannot handle so long operations.

So I wonder if I can ajaxify parts of the process, especially the loop

1. upload the file
2. parse 1 CSV line and save it in SQL and display a 'OK' message each time a tweet is saved
3. compute all other things (reach and brand stats)

I'm not enough aware of $.ajax() but I guess there is something to do with beforeSend, success, complete and all the Ajax Events. Or maybe am I completely wrong!?

Is there anybody who can help me?

Answer 1

As far as I can tell, you can lighten the load on your server substantially because $pack is an array of values already, and there is no need to do the key value loop.

You can also write the mapping of values from the CSV row more idiomatically. Unless you know the CSV file is likely to be huge, you should also do multiple lines

$importer = new CsvImporter($uploadfile, true);
// get as many lines as possible at once...
while ($content = $importer->get()) {
    // this loop works whether you get 1 row or many...
    foreach ($content as $pack) {
        list($id_str, $from_user, ...) = $pack;
        // rest of your line processing and SQL inserts here....
    }
}

You could also go on from this and insert multiple lines into your database in a single INSERT statement, which is supported by most SQL databases.