如何将多行文本及PDF表格数据导入数据库（MS Access/SQL Server Express）

Got it, let's walk through exactly how to get that tricky PDF table (with those multi-line, cross-row entries) into either MS Access or SQL Server Express, and make sure those multi-line text fields are handled correctly.

Databases can't read PDF tables directly—you need to convert the data into something they understand, like CSV or Excel. The biggest challenge here is fixing those cross-row entries (like John Doe having 3 education lines tied to his name/ID).

• Tools to use:
  • Free options:
    • LibreOffice Calc: Open the PDF in LibreOffice Draw, select the table, copy-paste into Calc, then manually fix any merged/cross-row cells (fill in the name/ID for the rows that are missing them).
    • Tabula: A dedicated PDF table extractor that’s great at identifying cross-row relationships. It lets you select the table area, preview the extraction, and export to CSV/Excel with proper row associations.
  • Paid option: Adobe Acrobat Pro: Its built-in table extractor is super accurate, and you can manually tweak any misidentified rows before exporting.
• Critical check: Make sure every education/year entry is linked to the correct name and ID. For example, John Doe’s 3 education lines should all have "Doe, John" and "123" in their respective columns—no blank name/ID fields for the subsequent rows.

Once you have a clean CSV/Excel file, importing is straightforward, and handling multi-line text is easy with Access’s field types.

2.1 Import the Cleaned Data #

1. Open your Access database (or create a new one).
2. Go to the External Data tab, then pick either Excel or Text File (for CSV).
3. Follow the import wizard:
   • Select your file, confirm the delimiter (for CSV, use comma—just make sure text with commas is wrapped in quotes).
   • Check the box for "First row contains field names" if your file has headers.
   • Map each field to the right data type:
     • Name/Education: Use Long Text (perfect for multi-line entries) or Short Text if you don’t need line breaks.
     • ID: Number (set to Integer type).
     • Year: Number (Integer works here).
4. Finish the wizard—Access will create a new table with your data.

2.2 Handling Multi-Line Text #

• If your text has line breaks (like an address split across lines), set the field type to Long Text in the table design.
• When importing, Access will preserve line breaks as long as they’re included in your CSV/Excel file.
• To display the multi-line text properly in forms or reports, set the control’s Can Grow property to Yes—this lets the field expand to show all lines.

SQL Server has a couple of solid options for importing, and handling multi-line text just requires using the right data types.

3.1 Import the Cleaned Data #

Method 1: SQL Server Import and Export Wizard #

1. Open SQL Server Management Studio (SSMS) and connect to your Express instance.
2. Right-click your target database, go to Tasks > Import Data.
3. Configure the source:
   • Pick Excel or Flat File Source (for CSV). For CSV, set the delimiter, text qualifier (use double quotes to handle commas in text), and confirm the header row.
   • Map the fields to your target table (you can create a new table here or use an existing one):
     • Name/Education: Use NVARCHAR(MAX) (supports Unicode and multi-line) or VARCHAR(MAX) if you don’t need Unicode.
     • ID: INT.
     • Year: INT.
4. Run the import and verify the data looks correct.

Method 2: T-SQL Bulk Insert (For CSV Files) #

If you prefer using code, here’s a sample bulk insert command:

BULK INSERT EducationRecords
FROM 'C:\Your\File\Path\cleaned_data.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2, -- Skip the header row
    CODEPAGE = '65001', -- Use UTF-8 encoding
    TEXTQUALIFIER = '"' -- Ensures commas inside text don't break the import
);

3.2 Handling Multi-Line Text #

• SQL Server uses VARCHAR(MAX) or NVARCHAR(MAX) to store multi-line text—these types natively support line breaks (\n or \r\n).
• To see line breaks in SSMS query results, go to Tools > Options > Query Results > SQL Server > Results to Grid and check "Retain CR/LF in results".
• When exporting from your PDF extract tool, make sure multi-line text is wrapped in double quotes so the import process recognizes the line breaks as part of the text (not new rows).

• Cross-row entries are misaligned: If some rows are missing name/ID, go back to your Excel/CSV and use the fill handle to drag the name/ID down to the associated rows.
• Truncated text (like "Washin..."): Either open the original PDF in a viewer that shows the full content, or add a Notes field in your database to fill in the missing info later.
• Data type errors: If the import fails because of type mismatches, double-check that numeric fields (ID, Year) aren’t set to text types in your database.

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