Access fails to import one field from a Text File

[rccline]

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.

 

[dhookom]

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

 

[Andrzejek]

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.

 

[rccline]

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

 

[rccline]

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

 

[dhookom]

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

 

[rccline]

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