Export Anomaly

[PSULionRP]

Here's a weird one.

We are exporting an Access Table to a pipe delimeted file. We are having
data compatability issues with the file. When we looked at the data more
closely, it seems as though a record is being cut off. So this is what the
data looks like...

12/01/20CLIENT NAME|

Obviously, the date format is messed up by the export routine cutting off
the data. This export routine works for thousands of records and then we get
a record here and there sprinkled in with this anomaly. The "CLIENT RECORD"
is the first data block in the next sequential record. So not only is the
date being cut off, so are another half a dozen fields that should be part of
this record.

We are running Microsoft Access 2003.

Has anyone EVER seen anything like this before???

Thanks in advance for your review and hoping and praying for a reply.

Thanks.

PSULionRP

 

[Jerry Whittle]

Can you identify the problem records and see if there is anything strange
about those records?

Have you tried making a backup first then running a compact and repair?

 

[PSULionRP]

We looked at these records in the Access Table and compared them to records
which are exporting fine and don't see anything that's weird or different.

Could it cossibly be one of the fields behind this date that's messing it
up??? I did Google this and saw a reference to a "CStr" command. Do you think
it has anything to do with a data volume limitation???

Thanks for your reply Jerry!

 

[Jerry Whittle]

I'd make a backup first before trying anything.

Then I'd do a compact and repair. If something is strange with the tables,
that might fix it.

Is the field behind the date field a Memo field by chance? Memos can cause
problems.

Next I would try exporting the data using a query instead of directly from
the table. With a query, you could use functions like CStr to format the
data. BTW: CStr will give an error with nulls. I'd rather use the Format
function on the date fields as you can better define what the date will look
like. It will also convert the date to a string.

TheDate: Format([TheDateField], "D/M/YY")

 

[PSULionRP]

We do a Compact and Repair every time we release a version of the Access
Database application. So we don't think that's it.

The plot thickens....

When we limit the query to just the few records around the anomaly, the data
exports fine!

We are trying to export over 76,000+ records to this .csv file.

Do you think it's reaching a byte limitation in the buffer or something like
that???

Arrrrrrgggggghhhhhhhh

I'd make a backup first before trying anything.

Then I'd do a compact and repair. If something is strange with the tables,
that might fix it.

Is the field behind the date field a Memo field by chance? Memos can cause
problems.

Next I would try exporting the data using a query instead of directly from
the table. With a query, you could use functions like CStr to format the
data. BTW: CStr will give an error with nulls. I'd rather use the Format
function on the date fields as you can better define what the date will look
like. It will also convert the date to a string.

TheDate: Format([TheDateField], "D/M/YY")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

We looked at these records in the Access Table and compared them to records
which are exporting fine and don't see anything that's weird or different.

Could it cossibly be one of the fields behind this date that's messing it
up??? I did Google this and saw a reference to a "CStr" command. Do you think
it has anything to do with a data volume limitation???

Thanks for your reply Jerry!

 

[Jerry Whittle]

Are you using a CSV suffix? If so try a TXT suffix.

Also please show the code that you are using to do the export. If using a
macro, show the arguments and options. If doing it manually, describe.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

We do a Compact and Repair every time we release a version of the Access
Database application. So we don't think that's it.

The plot thickens....

When we limit the query to just the few records around the anomaly, the data
exports fine!

We are trying to export over 76,000+ records to this .csv file.

Do you think it's reaching a byte limitation in the buffer or something like
that???

Arrrrrrgggggghhhhhhhh

I'd make a backup first before trying anything.

Then I'd do a compact and repair. If something is strange with the tables,
that might fix it.

Is the field behind the date field a Memo field by chance? Memos can cause
problems.

Next I would try exporting the data using a query instead of directly from
the table. With a query, you could use functions like CStr to format the
data. BTW: CStr will give an error with nulls. I'd rather use the Format
function on the date fields as you can better define what the date will look
like. It will also convert the date to a string.

TheDate: Format([TheDateField], "D/M/YY")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

We looked at these records in the Access Table and compared them to records
which are exporting fine and don't see anything that's weird or different.

Could it cossibly be one of the fields behind this date that's messing it
up??? I did Google this and saw a reference to a "CStr" command. Do you think
it has anything to do with a data volume limitation???

Thanks for your reply Jerry!

:

Can you identify the problem records and see if there is anything strange
about those records?

Have you tried making a backup first then running a compact and repair?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Here's a weird one.

We are exporting an Access Table to a pipe delimeted file. We are having
data compatability issues with the file. When we looked at the data more
closely, it seems as though a record is being cut off. So this is what the
data looks like...

12/01/20CLIENT NAME|

Obviously, the date format is messed up by the export routine cutting off
the data. This export routine works for thousands of records and then we get
a record here and there sprinkled in with this anomaly. The "CLIENT RECORD"
is the first data block in the next sequential record. So not only is the
date being cut off, so are another half a dozen fields that should be part of
this record.

We are running Microsoft Access 2003.

Has anyone EVER seen anything like this before???

Thanks in advance for your review and hoping and praying for a reply.

Thanks.

PSULionRP

 

[PSULionRP]

SOLUTION!!!!

After further review...

After further review of the data, the Memo column we were
extracting actually had a Carriage Return, CHAR(13), and Line
Feed, CHAR(10), characters in it. When we trimmed these out,
it fixed the problem!

Those damn ASCII characters, Carriage Return/Line Feed, will get you every
time!

Thanks for your help Jerry!

GREATLY appreciated!

Are you using a CSV suffix? If so try a TXT suffix.

Also please show the code that you are using to do the export. If using a
macro, show the arguments and options. If doing it manually, describe.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

We do a Compact and Repair every time we release a version of the Access
Database application. So we don't think that's it.

The plot thickens....

When we limit the query to just the few records around the anomaly, the data
exports fine!

We are trying to export over 76,000+ records to this .csv file.

Do you think it's reaching a byte limitation in the buffer or something like
that???

Arrrrrrgggggghhhhhhhh

I'd make a backup first before trying anything.

Then I'd do a compact and repair. If something is strange with the tables,
that might fix it.

Is the field behind the date field a Memo field by chance? Memos can cause
problems.

Next I would try exporting the data using a query instead of directly from
the table. With a query, you could use functions like CStr to format the
data. BTW: CStr will give an error with nulls. I'd rather use the Format
function on the date fields as you can better define what the date will look
like. It will also convert the date to a string.

TheDate: Format([TheDateField], "D/M/YY")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

We looked at these records in the Access Table and compared them to records
which are exporting fine and don't see anything that's weird or different.

Could it cossibly be one of the fields behind this date that's messing it
up??? I did Google this and saw a reference to a "CStr" command. Do you think
it has anything to do with a data volume limitation???

Thanks for your reply Jerry!

:

Can you identify the problem records and see if there is anything strange
about those records?

Have you tried making a backup first then running a compact and repair?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Here's a weird one.

We are exporting an Access Table to a pipe delimeted file. We are having
data compatability issues with the file. When we looked at the data more
closely, it seems as though a record is being cut off. So this is what the
data looks like...

12/01/20CLIENT NAME|

Obviously, the date format is messed up by the export routine cutting off
the data. This export routine works for thousands of records and then we get
a record here and there sprinkled in with this anomaly. The "CLIENT RECORD"
is the first data block in the next sequential record. So not only is the
date being cut off, so are another half a dozen fields that should be part of
this record.

We are running Microsoft Access 2003.

Has anyone EVER seen anything like this before???

Thanks in advance for your review and hoping and praying for a reply.

Thanks.

PSULionRP