INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access fails to import one field from a Text File

Access fails to import one field from a Text File

(OP)
Houston Central Appraisal District publishes a text file that contains 1.6 million rows of data. All data but the key field will import. I tried breaking the field into a smaller size, importing from the text file, 800 rows of data.

The key fails to import. I then saved my smaller sized text file with 800 rows of data into Excel. The key shows up in the Excel file, but if I try to import that file into Access, the key field does not import from the Excel file.

Without the primary key, the data is useless.

What could be happening?

Thank you.


RE: Access fails to import one field from a Text File

Are you attempting to import into an existing table? Do you get any error message? How many fields are there? Are there field names/headers in the text file?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Access fails to import one field from a Text File

"Without the primary key, the data is useless" How the primary key field is defined in your table? Do you import your file with the code you wrote (if so, could you show us the code?) or do you use build-in functionality in Access - if so, what do you use?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Access fails to import one field from a Text File

(OP)
Duane:

Thank you for replying.

I am trying to import into a fresh Access 2016 database. There are no error messages. I broke the file into a smaller file with 800 rows so I could copy/paste into Excel 2016. The key field populates.
But, if I try importing that excel format into a fresh table in Access, I get the same result: the key field fails to import.

I imported the original file into the Excel PowerPivot data model. The data imports into the data model and the key field is visible. But, I cannot create a pivot table from the Data Model. In excel, if I try and create the pivot table I get the error message that there are too many rows of data.

The Access version is Access 365 ProPlus 2016.

Thank you.

Rccline

RE: Access fails to import one field from a Text File

(OP)
Andy. I simply used the import wizard to import the text file. There are no headers in the data.

This is the file layout:

owners acct 13
owners ln_num 4
owners name 100
owners aka 50
owners pct_own 5

The key is called the acct field. I tried importing another public data file from the CAD into Access, it too will import all fields except the data in the acct field.

The CAD Data department does not answer questions. Is there any way for there to be hidden code in that file? I wouldn't even begin to know how to look at it.

Here is a cut and paste of the first few rows:

0021210000019 2 DIOCESE GALVESTON-HOUSTON .000
0021220000001 1 AMEGY BANK NAT ASSOC 1801 MAIN PARKING GARAGE 1.000
0021240000001 1 HORNBERGER BROS PPTIES INC MEGABUS.COM 1.000
0021240000003 1 THOMAS JOE LEE GRAB BAR 1.000
0021240000004 1 JACOBUS CHARLES J & TRUSTEE 1.000
0021240000005 1 JACOBUS CHARLES J & TRUSTEE JOE LEE'S BARBER SHOP 1.000
0021240000006 1 1901 MANAGEMENT LLC 1901 MILAM 1.000
0021240000007 1 1901 MANAGEMENT LLC 1.000



Thank you.

rccline

RE: Access fails to import one field from a Text File

The import attempts to create the first column as long integer but fails for every record since the values are greater than long integer can accept. I get a new table named "Owners_1a_ImportErrors" with the errors. You can change data type to short text or double and the data will import. The first column is not unique. There are about 80+ duplicate values.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Access fails to import one field from a Text File

(OP)
Thank you gentlemen:

I discovered the source of my error! It was a DATA TYPE error.

I ran the import again with the text import wizard. This time, I noticed that the acct field was set to "long integer."
I changed the data type of the acct field to "TEXT." The field imported.

Again, gentlemen. Thank you very much.

rccline

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close