[AccessD] Import Bad data?

Charlotte Foust cfoust at infostatsystems.com
Mon Feb 9 13:31:44 CST 2009


Does the record contain a memo field, perhaps?  If so, it sounds like
it's been corrupted.  One option is to import all the other fields but
not the memo field into your database maintaining the table structure.
Then do an append of the memo fields on the key joins between the two
tables.  You should be able to get everything except the bad fields.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Monday, February 09, 2009 8:42 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Import Bad data?


I can't even pull this record in with a select statement...as a query or
in vba as a recordset?
 
I'm trying to avoid having the dba export to a text file...as they are
not fans of this process to begin with.
 
Thanks,
 
Mark

----------------------------------------
> Date: Mon, 9 Feb 2009 10:24:11 -0600
> From: garykjos at gmail.com
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Import Bad data?
>
> You could Export to a Text file and then read through that with vba 
> text handling code to parse through and scrub the offending characters

> and then save the corrected file and import that. I've done that when 
> someone provided me with several huge exported data files that didn't 
> have the correct combination of CR & LF at the end of records and so 
> it fouled up a direct import for me.
>
> Longtime AccessD contributor Seth Galitzer has some code samples on 
> his website for "FILE I/O in VB" that you can download and have a look

> at.
>
> http://seth.galitzer.net/node/11
>
> Once you get the file open parsing through the text and either 
> ignoring characters or replacing them with something else isn't too 
> tough. It's been a long time since I've done it but I could MAYBE hunt

> up my code if you are really stumped by that.
>
> GK
>
> On 2/9/09, Mark A Matte wrote:
>>
>> Hello All,
>>
>> I'm not expecting an answer I'll like...but I'll try.
>>
>> I'm importing a large number of records from Informix to Access via
odbc. There is a large text/memo field...that apparently has some
characters access does not like. I do not get an error message...access
just says "an error has occurred...do you want to tell MS"...then
closes.
>>
>> I'm trying to think of a way to detect/program around this. So
far...its a very manual process. I loop through 2 days worth at a
time...that way I don't have to start over from the beginning everytime.
When it crashes...I pull in just the PKs for that date range...loop
through them...imort the text field 1 record at a time...store in temp
table...when it crashes...I compare the PKs to the temp table...the next
PK not in the temp is the offender.
>>
>> This is very manual and time consuming...any ideas/suggestions?
>>
>> Thanks,
>>
>> Mark A. Matte
>>
>>
>> _________________________________________________________________
>> Windows Live(tm): E-mail. Chat. Share. Get more ways to connect.
>> http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_
>> 022009
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
>
> --
> Gary Kjos
> garykjos at gmail.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Windows Live(tm): E-mail. Chat. Share. Get more ways to connect. 
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_allup_explore_022009
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list