[AccessD] Curious Import Problem

[Heenan, Lambert]

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