phpMyAdmin每次仅导入48条CSV记录问题求助

Hey there, let's work through this annoying CSV import issue you're dealing with. It's so frustrating when you're trying to import a small file and it keeps stopping after 48 rows—especially when the rows look totally fine. Let's break down the most likely fixes:

1. First, Validate Your CSV File Structure (This is Probably the Root Cause) #

The error "Invalid column count" almost always points to a formatting issue in your CSV that phpMyAdmin is misinterpreting. Here's how to check:

• Use a text editor that shows hidden characters: Open your CSV in Notepad++ (or VS Code with the right extensions), then turn on "Show All Characters" (in Notepad++, go to View > Show Symbols > Show All Characters). This lets you see if:
  • Rows are properly separated with consistent line endings (either CRLF for Windows or LF for Unix—mixing them will confuse phpMyAdmin).
  • Any fields contain commas, line breaks, or quotes that aren't wrapped in double quotes. For example, if a hospital name had a comma like COASTAL, HARBOR TREATMENT CENTER, it needs to be "COASTAL, HARBOR TREATMENT CENTER" to avoid splitting into extra columns.
• Check the "bad" line and its neighbors: The error mentions line 1306, but the issue might actually be in the line before it. If the previous row didn't end with a proper line break, it could merge with line 1306, creating a row with twice as many columns (which would trigger the error). Looking at your example data, those two rows are merged into one—this is exactly the kind of problem that causes this error!

2. Tweak phpMyAdmin's Import Settings #

Even if your CSV looks good, phpMyAdmin's default import settings might not match your file's format. When you go to the import page:

• Double-check the Format-specific options:
  • Set "Fields separated by" to , (comma)
  • Set "Fields enclosed by" to " (double quote)
  • Set "Lines terminated by" to Auto (or manually select CRLF if you're on Windows)
• Try enabling Partial import but adjust the "Maximum rows per import batch" to a higher number (like 1000) instead of the default. This can bypass any hidden batch limits in phpMyAdmin without forcing you to split the file manually.

3. Use MySQL's Native LOAD DATA INFILE Command (More Reliable Than phpMyAdmin) #

phpMyAdmin's import tool is convenient, but it can be finicky. The native MySQL command is way more robust for CSV imports. Here's how to use it:

1. Move your CSV file to the data folder of your target database in WAMP (usually C:\wamp64\bin\mysql\mysqlX.X.X\data\your_database_name—replace X.X.X with your MySQL version).
2. Open phpMyAdmin, go to your database, and click the SQL tab.
3. Run this command (adjust it to match your table and CSV structure):

   LOAD DATA INFILE 'hospital_addresses.csv'
   INTO TABLE hospital_addresses
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\r\n' -- Use '\n' if your CSV uses Unix line endings
   -- Add IGNORE 1 LINES; below if your CSV has a header row
   ;
   

This command skips many of phpMyAdmin's parsing quirks and will handle the entire file in one go if the CSV is formatted correctly.

4. Check MySQL's max_allowed_packet Setting #

Sometimes MySQL itself limits the amount of data it can process in one go. To fix this:

• Open your WAMP MySQL config file (my.ini or my.cnf—you can access it via the WAMP tray menu: MySQL > my.ini).
• Find the line max_allowed_packet and set it to a larger value, like:

  max_allowed_packet=256M
  

• Restart the MySQL service from the WAMP tray menu.

5. Fix CSV Encoding Issues #

Mismatched encoding between your CSV and database can cause silent parsing errors. Use Notepad++ to convert your CSV to UTF-8 without BOM:

• Open the CSV in Notepad++
• Go to Encoding > Convert to UTF-8 without BOM
• Save the file and try importing again.

Start with the CSV structure check first—since your error is about column counts, that's the most probable fix. If that doesn't work, the LOAD DATA INFILE command should get you over the hump.

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