contents of fields in '0' records were lost and '0' records were lost

[scottian]

im trying to import files into a database but every time i try only 24 records are imported and i get the error " The contents of fields in 0 records(s) were deleted, and 0 record(s) were lost due to key violations."
It offers possible reasons such as 'the data doesnt match the field data types or the fieldsize property'
or ' records you pasted contain a primary key or violate referential integrity rule'
Non of this is true, there is no primary key, there are no references, fieldsize is way and above what is needed field types are all set as text.
Can anyone help.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[SkipVought]

Chances are the DATA that being rejected in your TEXT field is NUMERIC. Convert your NUMERIC data to TEXT in the source data file.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[maxhugen]

How are you importing - with the Import Wizard, TransferText, TransferSpreadsheet? What type of source file... text, spreadsheet?

I recently had fun with a file dumped by IT... it was called something like my.file.xls, and I wanted to import it using vba.

Problems:
1. It wasn't a spreadsheet file really, but a tsv (tab separated values) text file. Sure, it opened in Excel, but Access would not import it as a spreadsheet.
2. It wouldn't import using TransferText either, with the .xls extension, so had to change that
3. It wouldn't import if the filename used dot notation - very common in *nix, but not always supported by Windows apps.
4. The line terminator IT used added an extra 'column' at the end (no idea why)
5. In one of the text fields, half the records used double-quotes, half didn't.

After renaming the file to my_file.txt, I finally managed to automate the import. One might almost think that the IT people were being deliberately difficult...

Max Hugen
Australia

 

[scottian]

SkipVought, I dont want to have to change the column formats. Im trying to build a database for a team of ordinary bods, and i want to automate it as much as i can, i dont want them to haf to open files and muck with the format of columns.

Maxhugen,
Problems:
1.Nope, its a workbook, and im using transferspreadsheet.
2. Transfer text doesnt work, it just imports one record full of unreadable characters
3. No dots in the filename, but there are underscores
4. No extra columns
5. No quotes used.

I cant understand why 25 records are imported,


"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[maxhugen]

Have you tried importing the data using the Import Wizard to test the import? It might provide a clue to the problem.

Max Hugen
Australia

 

[SkipVought]

Check out

http://support.microsoft.com/kb/281517.

This refers to the registry setting that determines the number of rows that the import manager uses to GUESS the data type.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[scottian]

sorry for resurecting this post. Messing with the registry is not an option. Also another bug, it will only import a small number of records. Out of the file of over 2000 records only 55 are imported. I dont have any primary keys set up cos if i copy and paste i dont have any problems, all records are accepted.

I hope someone can help, cos im stuck

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[maxhugen]

Have you tried importing the spreadsheet with the Import Wizard? Can you make that work OK?

Max Hugen
Australia

 

[scottian]

The import wizard has the same problem, i cant understand it. As i said when i cut copy and paste all the records are accepted.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[SkipVought]

"...I dont want to have to change the column formats..."

It is NOT the column formats.

It is the mixture of data within the column.

I have a similar problem in s family of worksheets that I query regularly. They have sporatic NUMERIC data in a column and lots of EMPTY CELLS. Guess what! The scan of the first 100 rows might indicate EMPTY CELLS, so the import manager ASSUMES TEXT.

I have a Fix routine that updates any EMPTY CELL to ZERO in the first row of a column that is a NUMERIC datatype in the SOURCE DATA prior to import/query.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[MichaelintheUK]

Hello ScottIan

I had a problem importing a file when one field contained part numbers. For about the first 75% part numbers were numeric digits only. Later part numbers preceded the digits with a single character. It accepted the digit only part numbers and rejected the ones starting with an alpha character.

It seems as if Access does a scan of the first so many records to determine the data type. I fudged this by preceding the first 2 part numbers with a 'z' - the import worked with no errors. I then went into the Access table and removed the temporary 'z's.

Also with an import from Excel the end of continuous rows of data may not be interpreted as the end - if the rows below once contained data it may also try and bring in the rows.

 

[MichaelintheUK]

Hello ScottIan

I had a problem importing a file when one field contained part numbers that were for about 75% of the part numbers were numeric digits only. Later part numbers preceded the digits with a single character. It accepted the digit only part numbers and rejected the ones starting with an alpha character.

It seems as if Access does a scan of the first so many records to determine the data type. I fudged this by preceding the first 2 part numbers with a 'z' - the import worked with no errors. I then went into the Access table and removed the temporary 'z's.

Also with an import from Excel the end of continuous rows of data may not be interpreted as the end - if the rows below once contained data it may also try and bring in the rows.

 

[SkipVought]

MichaelintheUK,

if Isnumeric(fld.value) then _
   fld.value = "'" & fld.value

works in Excel without having to change stuff back.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[maxhugen]

The import wizard has the same problem, i cant understand it.

The next step I'd try would be to create a table, with the fields properly specified, and then try to import the spreadsheet into that. Does that work for you?

Max Hugen
Australia