How to Display Real-Time Progress Bar for Product Import in WooCommerce?

I am building a custom product import page for WooCommerce where users can upload a CSV file to update product stock or price. I want to show a real-time progress bar that displays the percentage of records updated, e.g., how much needs to be updated and how much has already been updated. I am using PHP for the backend processing and AJAX for handling the import asynchronously. The import process works, but I am struggling to display the progress dynamically to the user while the import is happening.

Additionally, I’m fetching products by their SKU, because when I try to fetch them by product ID, I encounter issues where the product ID seems to exist, but when I try to use it, I get errors saying that the product is invalid or cannot be found. Sometimes, Excel converts my SKU values to scientific notation, which causes problems when using them for product lookup.

How can I resolve both the issues of using the product ID correctly and displaying the progress bar in real-time?

// Add the custom menu for the import page
add_action('admin_menu', 'custom_import_menu');

function custom_import_menu() {
    add_submenu_page(
        'woocommerce',
        'Custom Product Import',
        'Product Import',
        'manage_options',
        'custom-product-import',
        'custom_product_import_page'
    );
}

function custom_product_import_page() {
    ?>
    <div class="wrap">
        <h1>Custom Product Import</h1>
        <form method="post" enctype="multipart/form-data" id="product_import_form">
            <label for="import_file">Choose CSV file (.csv):</label>
            <input type="file" name="import_file" id="import_file" accept=".csv">
            <input type="hidden" name="update_type" value="stock">
            <input type="submit" name="import_products" class="button button-primary" value="Import Products">
        </form>
        <div id="progress-bar-container"></div>
    </div>
    <?php
    if (isset($_POST['import_products']) && !empty($_FILES['import_file']['tmp_name'])) {
        $uploadedFile = $_FILES['import_file']['tmp_name'];

        // Move file to a temporary location
        $tempFilePath = wp_upload_dir()['basedir'] . '/temp_import_file.csv';
        move_uploaded_file($uploadedFile, $tempFilePath);

        custom_import_products_csv_preview($tempFilePath);
    }
}

// Function to show CSV preview
function custom_import_products_csv_preview($file) {
    if (($handle = fopen($file, "r")) !== FALSE) {
        $header = fgetcsv($handle, 1000, ",");
        $previewRows = [];
        for ($i = 0; $i < 10 && ($row = fgetcsv($handle, 1000, ",")) !== FALSE; $i++) {
            $previewRows[] = $row;
        }
        fclose($handle);

        $updateColumn = null;
        
        // Check for column names
        if (strtolower($header[3]) == 'stock') {
            $updateColumn = 'stock'; // Use stock column for update
        } elseif (strtolower($header[2]) == 'price') {
            $updateColumn = 'price'; // Use price column if it exists
        }

        if (!$updateColumn) {
            echo '<div class="error notice"><p>Second column should be "Price" or "Stock" to indicate the update type. Ensure the correct column is used for the update.</p></div>';
            return;
        }

        echo '<div class="notice notice-info"><p>Detected ' . ucfirst($updateColumn) . ' column. Here is a preview of the data:</p></div>';
        echo '<table class="wp-list-table widefat fixed striped">';
        echo '<thead><tr><th>SKU</th><th>' . ucfirst($updateColumn) . '</th></tr></thead>';
        echo '<tbody>';
        foreach ($previewRows as $row) {
            echo '<tr><td>' . esc_html($row[2]) . '</td><td>' . esc_html($row[3]) . '</td></tr>';
        }
        echo '</tbody></table>';
        ?>
        <form method="post" action="">
            <input type="hidden" name="confirm_import" value="1">
            <input type="hidden" name="update_type" value="<?php echo esc_attr($updateColumn); ?>">
            <input type="hidden" name="file_path" value="<?php echo esc_attr($file); ?>">
            <input type="submit" name="confirm_update" class="button button-primary" value="Confirm Update">
        </form>
        <?php
    } else {
        echo '<div class="error notice"><p>Failed to open the CSV file. Please check the file format.</p></div>';
    }
}

add_action('wp_ajax_process_product_import', 'process_product_import');

function process_product_import() {
    if (!isset($_FILES['import_file'])) {
        wp_send_json_error(['message' => 'No file uploaded']);
    }

    $file = $_FILES['import_file'];
    $updateType = $_POST['update_type'];

    // Process the file
    $uploadedFile = $file['tmp_name'];
    if (($handle = fopen($uploadedFile, "r")) !== FALSE) {
        fgetcsv($handle, 1000, ","); // Skip header row
        $totalRows = count(file($uploadedFile)) - 1; // Total rows excluding header
        $updatedCount = 0;

        // Loop through the rows and update products
        while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $sku = sanitize_text_field($row[2]);
            $value = floatval($row[3]);
            $product = get_product_by_sku($sku);

            if ($product) {
                if ($updateType == 'stock') {
                    $product->set_stock_quantity($value);
                    $product->set_manage_stock(true);
                    $product->save();
                    $updatedCount++;
                }
            }

            // Send progress update (percentage)
            $progress = ($updatedCount / $totalRows) * 100;
            wp_send_json_success(['progress' => $progress]);
        }
        fclose($handle);
    }

    wp_send_json_success(['message' => 'Import completed successfully.']);
}

// Function to retrieve the product by SKU
function get_product_by_sku($sku) {
    global $wpdb;

    $product_id = $wpdb->get_var( $wpdb->prepare( "SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $sku ) );

    if ( $product_id ) {
        return new WC_Product( $product_id );
    }

    return null;
}

I tried using the wp_send_json_success() function to send progress updates during the import process, and I also included a progress bar element in the HTML. However, the progress bar does not update in real time, and the page reloads instead of dynamically updating the progress. I expected that the progress bar would show the ongoing progress as the product stock or price is updated based on the CSV file’s content.