Import Errors (1 Viewer)

gakiss2

Registered User
Joined
Nov 21, 2018
Messages
92
Seems like more of a nuisance but wanted to check with the experts. I import data from a source that I have no control over and all seems to work well in terms of database function. The issue is that I get an Import Error table added each time I import. I look at them but I don't get any useful information. There doesn't seem to at least be an important error as I have yet to find anything going wrong. I can easily just delete them and could probably run a vba procedure to clean them up after the import operation. I just wanted to make sure there isn't something going horribly wrong waiting to bite me later. Data base attached. It has some mildly private data, please don't share.
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,693
Hi. What values does the column "charfld1" contains? What is the data type in your table? Are you importing/appending the data to an existing table? Or, are you creating a new table each time you import the data?
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
it might be that a header row in the spreadsheet is being rejected, as not being valid data. Would that make sense?
 

gakiss2

Registered User
Joined
Nov 21, 2018
Messages
92
Hi. What values does the column "charfld1" contains? What is the data type in your table? Are you importing/appending the data to an existing table? Or, are you creating a new table each time you import the data?
I don't use "charfld1" but I was able to get to that data at the source and get it changed(deleted). It was just a couple of records with some randomn data so I am thinking now the problem will go away.

I am appending to the table. The overall idea is to use the form you have been helping with in other threads to add data such as Decision and Category to each of the records that are in the table that is appended. There is another program (database but not access) that these records are generated in when a part gets rejected but it is clumsy and the IT gods in charge of it don't like to share power so I pull out the data I need then get the actual work done in my database. No offense if you yourself are an IT god. Some of my best friends are...

I think this solves my issue. Thank You
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,693
I don't use "charfld1" but I was able to get to that data at the source and get it changed(deleted). It was just a couple of records with some randomn data so I am thinking now the problem will go away.

I am appending to the table. The overall idea is to use the form you have been helping with in other threads to add data such as Decision and Category to each of the records that are in the table that is appended. There is another program (database but not access) that these records are generated in when a part gets rejected but it is clumsy and the IT gods in charge of it don't like to share power so I pull out the data I need then get the actual work done in my database. No offense if you yourself are an IT god. Some of my best friends are...

I think this solves my issue. Thank You
Hi. No worries. Glad to hear you got it sorted out. Good luck with your project.
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
actually, I prefer to import csv's because you have more control. When you import a spreadsheet, access/excel decide what type the columns are from the first few columns. If they decide that are numbers, then entries that are text get ignored (import errors). It can be quite hard to actually make them bring the column in as the correct data type.
 

gakiss2

Registered User
Joined
Nov 21, 2018
Messages
92
actually, I prefer to import csv's because you have more control. When you import a spreadsheet, access/excel decide what type the columns are from the first few columns. If they decide that are numbers, then entries that are text get ignored (import errors). It can be quite hard to actually make them bring the column in as the correct data type.
The data we are allowed to pull is in Excel so I would have to first convert to csv then import. but I'll keep it in mind if I have more troubles. For now I think I've worked around it.
 

Cronk

Registered User
Joined
Jul 4, 2013
Messages
2,235
I'm late to the party.


When I encounter problems with field type errors in importing from Excel, I will import the data into a temp table with only character (string) fields, check and fix any problems, then import into the main table.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,594
So... if you did a fussy count of what was imported, did you come up two records short?
Or did two records contain blanks in the offending fields? I would expect to see that in the case that all of the other fields contained some number but perhaps because of the format used, two records contained a non-numeric character. For instance a trailing DB (meaning DEBIT), which was used in some business software to highlight negative numbers.

You say it was solved by getting an adjustment. But for future reference, when you see those errors, you don't ignore them because they represent data losses. You of course DID not ignore them, so this becomes an academic discussion. But you never totally ignore import error tables when you get them.
 

gakiss2

Registered User
Joined
Nov 21, 2018
Messages
92
So... if you did a fussy count of what was imported, did you come up two records short?
Or did two records contain blanks in the offending fields? I would expect to see that in the case that all of the other fields contained some number but perhaps because of the format used, two records contained a non-numeric character. For instance a trailing DB (meaning DEBIT), which was used in some business software to highlight negative numbers.

You say it was solved by getting an adjustment. But for future reference, when you see those errors, you don't ignore them because they represent data losses. You of course DID not ignore them, so this becomes an academic discussion. But you never totally ignore import error tables when you get them.
Thank You for your advice. I understand in principle what you are saying. It just turned out in this case that that data was in fact useless and, also, I was able to get rid of them (now all data in the field is null) so the errors don't happen anymore.

Like most of Access, the information in the error table is very cryptic so I imagine I will be back on these boards if I encounter again in another project.
 

gakiss2

Registered User
Joined
Nov 21, 2018
Messages
92
I'm late to the party.


When I encounter problems with field type errors in importing from Excel, I will import the data into a temp table with only character (string) fields, check and fix any problems, then import into the main table.
So the issue is now officially a non-issue since I got rid of the offending data. To your point, the difficulty would be that I would, at least ultimately, want to do it automatically without interaction from the user. Or, maybe I misunderstand and there is a way to do what you suggest automatically??

At any rate I thank you for your input.
 

gemma-the-husky

Super Moderator
Staff member
Joined
Sep 12, 2006
Messages
13,873
The thing is, unless the errors are expected (such as a header row), it's important to realise there has been an error, and identify the cause. I often show a total, or a row count after an import so users can double check that all the data has been imported.
 

gakiss2

Registered User
Joined
Nov 21, 2018
Messages
92
The thing is, unless the errors are expected (such as a header row), it's important to realise there has been an error, and identify the cause. I often show a total, or a row count after an import so users can double check that all the data has been imported.
Understood.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,594
There is another factor here.

You, as the developer & administrator & manager & chief-cook-and-bottle-washer of this database are the one who got the errors. So you (quite properly) investigated and came up with a solution.

As long as this remains the case, then all the advice we offer is, in fact, being followed. That is, you ARE investigating and fixing errors.

Where the problem suddenly becomes more acute is on databases where it is possible for a user to run into this problem and immediately panic that they have somehow broken the database. At this point, a different standard ensues. Then you have to be more proactive in how this importation process occurs because there, you probably cannot afford to allow that kind of disruption to your user base.

Therefore, from a pragmatic point of view, I would say it isn't time to change anything in your operational procedures UNLESS that problem could occur for a user of less technical acumen. Just trying to clarify where and when it is time to opt for change.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom