Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-07-2019, 06:46 AM   #1
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Import Errors

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.
Attached Files
File Type: accdb MRRA 5-3 Gary lite.accdb (832.0 KB, 12 views)

gakiss2 is offline   Reply With Quote
Old 05-07-2019, 06:56 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,808
Thanks: 57
Thanked 1,272 Times in 1,253 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import Errors

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-07-2019, 07:21 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Import Errors

it might be that a header row in the spreadsheet is being rejected, as not being valid data. Would that make sense?

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-07-2019, 07:53 AM   #4
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Import Errors

Quote:
Originally Posted by theDBguy View Post
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
gakiss2 is offline   Reply With Quote
Old 05-07-2019, 07:59 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,808
Thanks: 57
Thanked 1,272 Times in 1,253 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Import Errors

Quote:
Originally Posted by gakiss2 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-07-2019, 09:03 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Import Errors

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-08-2019, 04:01 AM   #7
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Import Errors

Quote:
Originally Posted by gemma-the-husky View Post
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.

gakiss2 is offline   Reply With Quote
Old 05-08-2019, 11:56 AM   #8
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Import Errors

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.
Cronk is offline   Reply With Quote
Old 05-08-2019, 12:15 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,557
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Import Errors

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-09-2019, 03:29 AM   #10
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Import Errors

Quote:
Originally Posted by The_Doc_Man View Post
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 is offline   Reply With Quote
Old 05-09-2019, 03:34 AM   #11
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Import Errors

Quote:
Originally Posted by Cronk View Post
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.
gakiss2 is offline   Reply With Quote
Old 05-09-2019, 04:03 AM   #12
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Import Errors

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-09-2019, 04:42 AM   #13
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Import Errors

Quote:
Originally Posted by gemma-the-husky View Post
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.
gakiss2 is offline   Reply With Quote
Old 05-09-2019, 06:31 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,557
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Import Errors

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Tags
import , import error , table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Errors markcrobinson Modules & VBA 1 11-30-2017 08:09 AM
Question Import Errors chrisguk General 2 07-07-2011 06:52 AM
import with/without errors LB79 Tables 2 11-26-2009 08:31 AM
Import Errors!! robk5787 Queries 6 09-11-2003 05:31 AM
Import errors hammerva General 2 11-08-2002 05:21 AM




All times are GMT -8. The time now is 03:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World