[AccessD] Import Bad data?

And once it's been exported to text and he's looping through the text file
looking for the bad guys, couldn't he just add a bit of DAO code, sort the
text fields into their proper fields in the record and write the record out
to the table?


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
Sent: Monday, February 09, 2009 8:24 AM
To: Access Developers discussion and problem solving
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 <markamatte at hotmail.com> 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 LiveT: E-mail. Chat. Share. Get more ways to connect.
> http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_0
> 22009
> --
> 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