# PDF to Access Database: Import Converted Rows

- Author: FastBankConvert (https://fastbankconvert.com)
- Published: 2026-10-17
- Category: CSV, JSON and text output
- Canonical: https://fastbankconvert.com/blog/pdf-to-access-database

> Get PDF data to an Access database: convert to Excel or CSV, import with the right field types, and append next month's rows without duplicates.
Getting a PDF to an Access database takes two steps. Convert the PDF's table to an Excel worksheet or CSV file, then bring it in with Access's import wizard, setting Date/Time, Currency and Short Text fields as you go. Later months append to the same table under a unique index, so no statement is added twice.

## Converting the PDF to Excel or CSV before Access sees it

Access imports spreadsheets, text files and other databases, but it has no importer for PDF files. A PDF stores characters at positions on a page rather than in rows and fields, so something has to rebuild the table first. Convert the PDF to an Excel worksheet or a CSV file, then import that.

Excel is the friendlier middle step for most people. You can see every row, delete page headers and totals, and fix a misplaced column before Access gets involved. A CSV works equally well and is the better choice when the file comes from a script or another system, because it carries no formatting that could confuse the import.

Whichever you choose, the converted file should look like a database table before you import it:

- **One header row** with a name for each column, in the first row of the sheet.
- **One record per row,** with no blank rows, subtotal rows or "continued on next page" lines.
- **One kind of value per column,** so a date column holds only dates and an amount column holds only numbers.
- **No merged cells,** which Access cannot map to fields.

If you are new to the conversion step, [converting a PDF to CSV](/blog/convert-pdf-to-csv) covers picking the right table and checking the file opens cleanly. FastBankConvert is getting ready to open and is built to turn a typical digital statement PDF into Excel or CSV in seconds, telling you before download if the balances do not add up; [Get started](/get-started) puts you on the list for trial conversions.

Some PDFs hold records that are not tables at all, such as letters with an address block on each page. Those need a different extraction before Access can use them. [Extracting addresses from a PDF to Excel](/blog/extract-addresses-from-pdf-to-excel) shows how to split those blocks into name, street and city columns, which then import like any other table.

## Preparing the worksheet so Access guesses field types right

Access decides each field's type by sampling the start of the data. [Microsoft's guide to importing Excel data into Access](https://support.microsoft.com/en-us/office/import-or-link-to-data-in-an-excel-workbook-a1952878-7c58-47b1-893d-e084913cc958) says Access scans the first eight source rows to choose field types. A column whose first eight values look like numbers becomes a number field, even if row nine holds text.

That sampling causes most import failures. Suppose a check number column is empty for the first ten rows and then holds values like 001042. Access may guess a number type from blanks and early rows, strip the zeros, or reject the later values. Or suppose an amount column starts with "N/A" on a few rows. Access may make the whole field text, so nothing sums.

Prepare the worksheet before importing:

1. **Format each column in Excel** with the type you want: dates as dates, amounts as numbers, identifiers as text. Microsoft's guide recommends formatting columns in Excel first for this reason.
2. **Remove placeholder text** such as "N/A" or "-" from number and date columns. Leave the cell empty instead.
3. **Make the first rows representative.** If a column is mostly empty at the top, sort so that rows with values come first, or put the types right in the wizard, described below.
4. **Keep one table per sheet.** Microsoft's guide notes that you can import from only one worksheet at a time, so a workbook with twelve monthly sheets means twelve imports, or one stacked sheet.

Step four is a good reason to stack months onto one sheet before the first import. It also matches how the data will live in Access: one table, many months.

## Which Access field types suit statement data?

Statement data needs three Access field types. Dates go in Date/Time, money in Currency, and everything else, including account and check numbers, in Short Text. That choice keeps sums exact, lets dates sort and filter by time, and stops identifiers losing their leading zeros.

[Microsoft's reference on data types for Access desktop databases](https://support.microsoft.com/en-us/office/data-types-for-access-desktop-databases-df2b83ba-cef6-436d-b679-3418f622e482) describes Currency as monetary data stored with four decimal places of precision, and Short Text as alphanumeric data of up to 255 characters. Currency is the right home for amounts because it is built for money, while a general Number field set to Double stores approximations of decimal values.

Here is an example field mapping for a converted bank statement. Field names are illustrative.

| Converted column (example) | Access field name | Access field type | Notes |
| --- | --- | --- | --- |
| Account | AccountID | Short Text | Keeps leading zeros and masking characters |
| Statement end date | StatementEnd | Date/Time | Identifies the statement each line came from |
| Line | LineNo | Number (Long Integer) | Position on the statement, counted from 1 |
| Date | PostedOn | Date/Time | The posting date |
| Description | Description | Short Text | Raise to Long Text if any exceed 255 characters |
| Amount | Amount | Currency | Signed: debits negative, credits positive |
| Balance | Balance | Currency | Can be empty for statements without a running balance |
| Check number | CheckNo | Short Text | An identifier, not a quantity |

Two rows in that table are there for the duplicates section later: StatementEnd and LineNo. They identify each line by where it appeared, which is what lets you reload a statement safely.

Descriptions rarely pass 255 characters, but some card and wire descriptions get close. If the wizard reports truncation, switch that field to Long Text.

## Running the Access import wizard into a new table

The import wizard turns a worksheet into a new Access table in a few screens. You pick the file and sheet, confirm the first row holds column headings, set each field's name and type, choose a primary key, and name the table. At the end, Access offers to save the steps so next month's import runs with one click.

The steps for the first import:

1. In Access, open the External Data tab and choose New Data Source, From File, Excel (or Text File for a CSV).
2. Browse to the file and choose to import the source data into a new table.
3. Pick the worksheet that holds the data.
4. Tick "First Row Contains Column Headings". Access uses those headings as field names.
5. On the field options screen, click each column and set its name and data type to match the mapping table above. This step overrides the eight-row guess.
6. On the primary key screen, choose "No primary key" if you will build your own key from AccountID, StatementEnd and LineNo, or let Access add an AutoNumber ID.
7. Name the table, for example StatementLine, and finish.
8. Tick "Save import steps" and give the saved import a name.

Microsoft's guide explains the primary key options: if you let Access add a key, it adds an AutoNumber field as the first field and fills it with unique values starting at 1. That ID is handy for forms and relationships, but it does not stop duplicates, because every reimported row gets a fresh number. The duplicates section below covers the key that does.

After the wizard, open the table in Design view and confirm the types took. Then open it in Datasheet view and compare the record count and a few amounts with the source.

## How do you append next month's rows to Access without duplicates?

Append each new month into the same table, protected by a unique index on the fields that identify a statement line. With that index in place, a statement imported twice cannot add its rows twice. Access refuses rows that would repeat an existing key, so the table keeps one copy of each line.

Build the index once, in the table's Design view:

1. Open StatementLine in Design view and choose Indexes on the ribbon.
2. Add a new index named, for example, UniqueLine.
3. Add AccountID, StatementEnd and LineNo as its fields, one per row.
4. Set Unique to Yes for that index and save the table.

Date, amount and description would make a poor key, because two real purchases can share all three, and a reimport of the same statement produces the same line numbers. [Choosing a duplicate-proof key for a SQL load](/blog/pdf-to-sql#stopping-duplicate-statement-rows-on-the-next-sql-load) works through that reasoning in full.

For the monthly import itself, the safest pattern uses a holding table:

1. Import the new month into a separate table, such as StatementLineNew, using your saved import steps.
2. Run an append query that copies rows from StatementLineNew into StatementLine.
3. Check the append's message for rows it could not add. Those are lines already in the main table.
4. Empty StatementLineNew for next month.

Microsoft's import guide adds a warning for appending straight into an existing table: the column names in the file should exactly match the field names in the table. A heading spelled "Posted On" against a field named PostedOn is one of the common reasons an append fails.

If a statement was converted again after a fix, its line numbers may shift. Delete that statement's rows by AccountID and StatementEnd first, then append the new version whole.

## Reading the Access import errors table

When some rows fail during an import, Access still imports the rest and writes the failures to an error table. Its name ends in "_ImportErrors", and it sits next to your new table. Microsoft's guide lists its three fields, Error, Field and Row, which tell you what went wrong, in which field and on which source row.

The usual entries for statement data, and what to do about each:

- **Type conversion failure** in an amount field. A value such as "(61.20)" or "61.20 DR" is not a number to Access. Clean the sign convention in the worksheet and import again.
- **Type conversion failure** in a date field. Text like "Opening balance" sat in the date column, or a date was written in a format Access could not read.
- **Field truncation** in a description. The text was longer than the field allows. Switch the field to Long Text.

The Row value points to the row in the source file, which makes the fix quick: open the worksheet, go to that row and look. Fix the source rather than typing into the Access table, so next month's import of a similar file works too.

An empty error table is not the same as a correct import. A row can import with the wrong value and raise no error at all. After each import, compare the record count with the converted file, and total the Amount field for the statement. The total should equal the closing balance minus the opening balance. If the data will later feed a script or another system as well as Access, [converting a PDF to JSON](/blog/pdf-to-json) explains a format that keeps those statement totals with the rows.