[AccessD] Curious Import Problem

[McGillivray, Don]

You may already have thought of this, but . . .

While it seems likely that unexpected non-printable characters would cause a reportable failure in the Access import - not the loss of the entire row - in addition to the CRLFs,  it may be worth looking for tabs or other non-printable characters in your input file - especially in columns where you might not expect to find them.  

I recently had to generate a large (3 million+ rows of 300+ columns) tab delimited text file from an Oracle database, some columns of which contained free-form text.  In addition to CRLF pairs, I had to find and convert embedded tab characters from those columns.  In the course if my travails with my Oracle data set, I found one record where the value in the Middle Name column (not a column where I expected to find extraneous junk) from a single person record was followed by SIX tab characters.  WTF?! Of course every row after that one was hosed, but the size of the output file was so large I couldn't load it into my text editor to find it.  In the end I wrote a script to count the tabs in the header row and compare that number to the number of tabs in each row of the file, writing to a text file any records where the tab count differed.  That also helped me to find extraneous CRLFs in unexpected places.

Moral of the story: Don't assume that a column that shouldn't have junk doesn't have junk.  Users are endlessly creative in coming up with ways to mess up your data.

Don

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, July 14, 2016 6:15 AM
To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Cc: 'ACCESS-L Email (ACCESS-L at PEACH.EASE.LSOFT.COM)' <ACCESS-L at PEACH.EASE.LSOFT.COM>
Subject: Re: [AccessD] Curious Import Problem

Jim, John and Guss:

Thanks for your interest. 

No, there appears to be absolutely nothing different in the data for the rows not loaded. The source file is a CSV file coming out of COGNOS. Don't know why the system insists on calling it "CSV", because the data is Tab delimited, not Comma Separated. The text is not quoted in any way and it is perfectly normal for some columns to be null. 

Each line is terminated with just a Line Feed character, ASCII code 10. The only thing I do to the file before importing it is to remove CR/LF pairs from a freeform text field - changing them to spaces.

Row order is not important. I always assume that the order of rows in a table is undefined. 

So it's still a mystery, although linking to the text file instead of importing it is an efficient workaround. Saves on file bloat too.

Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, July 13, 2016 4:32 PM
To: Access Developers discussion and problem solving
Cc: ACCESS-L Email (ACCESS-L at PEACH.EASE.LSOFT.COM)
Subject: Re: [AccessD] Curious Import Problem

I would think your right.   I've seen this happen when some text fields are quoted and some not and the import spec doesn't  have the quote set as a delimiter for text fields.

Jim

Sent from my iPhone

> On Jul 13, 2016, at 4:07 PM, John Colby <jwcolby at gmail.com> wrote:
> 
> If I had to guess I would say there was bad data in the rows. Missing fields, bad data for the column type etc.
> 
> 
>> On 7/13/2016 10:21 AM, Heenan, Lambert wrote:
>> X-Posted to Access-L and Access-D
>> 
>> I'm posting this just as an FYI. Not seeking an explanation (though if anyone has one please post it).
>> 
>> Here is the story.
>> 
>> I'm loading a large (130,000+ row) CSV file into a staging table, where some pre-processing is done on the data, and then the whole lot is copied to the final table.
>> 
>> Recently the users notice some rows of data were missing.  I confirmed that they were indeed present in the original source table, but on investigating I found that they simply never made it into the staging table.
>> 
>> Importing was done by using
>> 
>> DoCmd.TransferText acImportDelim, "Name Of Import Spec", "Name of Destination Table", "Full path to CSV file", True
>> 
>> No error were thrown, and no Import Error tables were generated to explain the missing data.
>> 
>> After pondering this for a while I changed the initial import process. Instead of using DoCmd.TransferText I simply *linked* to the CVS text file, using the same import spec as I used for DoCmd.TransferText. Then I created a straightforward Append query based on the linked text file to append all the data to the staging table.  That did the trick. All rows were loaded.
>> 
>> Very odd.
>> 
>> Lambert
> 
> -- 
> John W. Colby
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com