如何使用Ajax上传CSV文件并解析其内容？

Hey there! Let’s walk through getting your Ajax CSV upload and parsing pipeline up and running for that massive Twitter tweet dataset (280k tweets, 170k emojis—wow, that’s a solid foundation for your digital sentiment research!). I’ll break this down into manageable steps, building on the PHP scripts you already have.

First, you’ll need a simple frontend to select the CSV file, plus JavaScript to send it via Ajax without page reloads.

HTML File Input #

<div>
  <input type="file" id="tweetCsvUpload" accept=".csv">
  <button id="submitUpload">Upload & Process Tweets</button>
  <div id="uploadStatus"></div>
</div>

JavaScript Ajax Logic #

Use FormData to handle the file upload—it plays nicely with PHP’s file handling:

document.getElementById('submitUpload').addEventListener('click', function() {
  const fileInput = document.getElementById('tweetCsvUpload');
  const file = fileInput.files[0];
  
  if (!file || !file.name.endsWith('.csv')) {
    document.getElementById('uploadStatus').textContent = 'Please select a valid CSV file.';
    return;
  }

  const formData = new FormData();
  formData.append('tweet_csv', file);

  const xhr = new XMLHttpRequest();
  xhr.open('POST', 'upload_process.php', true);
  
  xhr.onload = function() {
    if (xhr.status === 200) {
      const response = JSON.parse(xhr.responseText);
      if (response.success) {
        document.getElementById('uploadStatus').textContent = `Success! Processed ${response.tweets_processed} tweets.`;
      } else {
        document.getElementById('uploadStatus').textContent = `Error: ${response.message}`;
      }
    } else {
      document.getElementById('uploadStatus').textContent = 'Upload failed. Please try again.';
    }
  };

  xhr.send(formData);
});

Update your existing upload script to handle the Ajax request, validate the file, and pass it to your parser. First, tweak your php.ini settings to handle large files:

• upload_max_filesize = 64M (bump this if your CSV is bigger)
• post_max_size = 64M
• max_execution_time = 300 (give it enough time to process the dataset)

PHP Upload Script (upload_process.php) #

<?php
header('Content-Type: application/json');

// Check if file was uploaded successfully
if (!isset($_FILES['tweet_csv']) || $_FILES['tweet_csv']['error'] !== UPLOAD_ERR_OK) {
  echo json_encode(['success' => false, 'message' => 'File upload failed.']);
  exit;
}

$fileTmpPath = $_FILES['tweet_csv']['tmp_name'];
$fileName = $_FILES['tweet_csv']['name'];

// Validate it's a CSV
if (!pathinfo($fileName, PATHINFO_EXTENSION) === 'csv') {
  echo json_encode(['success' => false, 'message' => 'Only CSV files are allowed.']);
  exit;
}

// Move to a temporary processing directory (cleaner than using the system temp folder)
$uploadDir = './temp_uploads/';
if (!file_exists($uploadDir)) mkdir($uploadDir, 0755);
$destPath = $uploadDir . uniqid('tweets_') . '.csv';

if (!move_uploaded_file($fileTmpPath, $destPath)) {
  echo json_encode(['success' => false, 'message' => 'Failed to save uploaded file.']);
  exit;
}

// Pass to your parser function
require 'csv_parser.php';
$processedCount = parseAndStoreTweets($destPath);

// Clean up temporary file
unlink($destPath);

echo json_encode(['success' => true, 'tweets_processed' => $processedCount]);
?>

With 280k tweets, don’t read the entire CSV into memory—that’ll cause memory crashes. Use fopen() to read line by line, and extract the fields you need with regex:

CSV Parser Script (csv_parser.php) #

<?php
function parseAndStoreTweets($csvFilePath) {
  // Connect to your database (use utf8mb4 to support emojis!)
  $db = new PDO('mysql:host=your_host;dbname=your_db;charset=utf8mb4', 'db_user', 'db_pass');
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
  // Prepare bulk insert statement (way faster than inserting one row at a time)
  $insertStmt = $db->prepare("
    INSERT INTO TWEETS (emoji, hashtags, links, retweet_count, mentions, tweet_text, created_at)
    VALUES (:emoji, :hashtags, :links, :retweet_count, :mentions, :tweet_text, :created_at)
  ");

  $processedCount = 0;
  $batchSize = 1000; // Insert in batches to reduce database overhead
  $db->beginTransaction();

  if (($handle = fopen($csvFilePath, 'r')) !== false) {
    $header = fgetcsv($handle); // Skip the header row
    
    while (($row = fgetcsv($handle, 0, ',')) !== false) {
      // Map CSV columns to variables (adjust based on your T.A.G.S. CSV structure)
      $tweetText = $row[array_search('text', $header)];
      $retweetCount = $row[array_search('retweet_count', $header)];
      $createdAt = $row[array_search('created_at', $header)];

      // Extract emoji using Unicode regex
      preg_match_all('/[\x{1F600}-\x{1F64F}\x{1F300}-\x{1F5FF}\x{1F680}-\x{1F6FF}\x{1F1E0}-\x{1F1FF}]/u', $tweetText, $emojiMatches);
      $emojis = implode(',', $emojiMatches[0]);

      // Extract hashtags
      preg_match_all('/#(\w+)/u', $tweetText, $hashtagMatches);
      $hashtags = implode(',', $hashtagMatches[1]);

      // Extract mentions
      preg_match_all('/@(\w+)/u', $tweetText, $mentionMatches);
      $mentions = implode(',', $mentionMatches[1]);

      // Extract links
      preg_match_all('/https?:\/\/\S+/u', $tweetText, $linkMatches);
      $links = implode(',', $linkMatches[0]);

      // Bind parameters and add to batch
      $insertStmt->execute([
        ':emoji' => $emojis,
        ':hashtags' => $hashtags,
        ':links' => $links,
        ':retweet_count' => $retweetCount,
        ':mentions' => $mentions,
        ':tweet_text' => $tweetText,
        ':created_at' => $createdAt
      ]);

      $processedCount++;
      
      // Commit batch every X rows
      if ($processedCount % $batchSize === 0) {
        $db->commit();
        $db->beginTransaction();
      }
    }
    fclose($handle);
    $db->commit(); // Commit any remaining rows
  }

  return $processedCount;
}
?>

• Emoji Support: Ensure your TWEETS table uses utf8mb4_unicode_ci collation—regular utf8 doesn’t support 4-byte emoji characters.
• Error Logging: Add try/catch blocks around database operations to log errors (e.g., error_log($e->getMessage());) so you can debug failed inserts.
• Duplicate Prevention: If you might re-upload the same CSV, add a unique constraint on the tweet ID column (from your CSV) to avoid duplicate entries.
• Progress Tracking: For extra large files, add a progress bar to the frontend using xhr.upload.onprogress to keep users updated.

内容的提问来源于stack exchange，提问作者artxtra