Data Web Extracts
The Formatting options has key settings for efficiently importing information into Microsoft Access:
If the number of rows will fit into Excel and you do NOT have any fields longer than 255 characters or "memo"-style text fields, Excel is probably the easiest choice.
Otherwise, use the following settings:
| Format: | ASCII |
| Delimiter: | Tab |
| Write nulls as: | . |
| Field names on first line? | YES |
| ASCII file extension: | TXT |
| Terminate lines with: | Carriage return and line feed |
| Replace special characters? | YES (very important!!!) |
| (Replaces carriage returns, line feeds and tabs with spaces) |
| Enclose all values with quotes? | YES (very important!!!) |
Programmer's Guide
Add 1st line containing field names with Perl or PHP script.
Use tab-delimiters and add "optionally enclosed by quote," which tells Access which fields are text and which are numeric. (Only the text fields are quote-enclosed.)
The root part of the file name must be 8 characters or less in length or Access will display multiple errors.
Save file with .TXT so Access sees it in default list of ASCII files.
select * INTO OUTFILE '/data/tmp/temp.txt' FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"' from scratch.temp;
Replace N with nothing so as to avoid thousands of errors in the ASCII import.
User's Guide
In Microsoft Access:
- Choose the Table tab
- Choose Import Table
- Choose File Type "Text files (*.txt...)"
- Choose Delimited
- Choose Tab as delimiter
- AND (on left side of same screen) check box that says "First row contains field names"
- AND (on right side of same screen) set the Text Qualifier from "{none}" to quotation mark (")
- Change the field type of long text fields such as P3ORG_PUR or P3ACHV from "Text" to "Memo" to accomodate field lengths longer than 255 characters.
- If a "Type Conversion" error is listed in the Import Errors table after completing the import, and the variables with problems are numbers, delete the old Access files, reimport, and change the types of the problem variables from the default "Integer" or "Long Integer" to "double" (double-precision real numbers). The problem occurs when the DataWeb writes a number in scientific notation (e.g, "5.25383e+06") which Access will not process unless the field is defined as a real number. These tend to be large numbers and they will be DROPPED altogether or TRUNCATED so this can lead to substantial errors in totals unless the problem is corrected.
- Let Access add the primary key or specify the NCCSKEY or EIN variable, depending on the type of file.
Errors on importing:
The DataWeb assigns "N" for null numeric values. These produce an error in MS Access.