[JPGaudreau]

Hi,

I have an Access database which contains my raw data. With C#, I'm doing a
lots of calculations on these data.

I'm exporting these calculated data into a CSV file. But before exporting
these data I'm formatting them (in the language required for each record).
Once my calculated data have been exported, I doing a mailmerge with Word
2003. I'm using CSV because it's also easy to open it with Excel.

If I select OLE DB data source, I have a screen popping up twice which ask
me to choose the type of separated value. Once the merge is done, I have the
bug that all the 0s in my data source file are 12:00:00 AM ! However, I did
the same thing with same Word mailmerge file and the same data source on a
colleague's computer and I do not have this "bug". Also when I'm switching
of records, it's really fast compared to the 2 other data connections I
tried.

If I'm trying to format the mergefield in word (\# #,##0.00), I'm getting
the value 12 instead of 12:00:00 AM.
Also, when I'm doing the merge with the data source DDE or Text files, I'm
not getting this bug. However, like I said it's really slower than with OLE
DB.

Is that could a be driver problem ?

I'm also using CSV because all the data is preformatted and the CSV format
is a text format so I could use the OLE DB data connection and be much more
faster* ...

Anybody could help me please ?

Thanks

Jean-Philippe

[Doug Robbins - Word MVP]

For the problem fields, the data in the first records(s) must be being
detected by Word as Date fields an on your colleague's computer, the Control
Panel Date format is probably set to not include the time in addition to the
date.

The switch should be \@ "hh:MM:ss AM/PM" to get 12:00:00 AM

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"JPGaudreau" wrote in message
...
Hi,

I have an Access database which contains my raw data. With C#, I'm doing a
lots of calculations on these data.

I'm exporting these calculated data into a CSV file. But before exporting
these data I'm formatting them (in the language required for each record).
Once my calculated data have been exported, I doing a mailmerge with Word
2003. I'm using CSV because it's also easy to open it with Excel.

If I select OLE DB data source, I have a screen popping up twice which ask
me to choose the type of separated value. Once the merge is done, I have
the bug that all the 0s in my data source file are 12:00:00 AM ! However,
I did the same thing with same Word mailmerge file and the same data
source on a colleague's computer and I do not have this "bug". Also when
I'm switching of records, it's really fast compared to the 2 other data
connections I tried.

If I'm trying to format the mergefield in word (\# #,##0.00), I'm getting
the value 12 instead of 12:00:00 AM.
Also, when I'm doing the merge with the data source DDE or Text files, I'm
not getting this bug. However, like I said it's really slower than with
OLE DB.

Is that could a be driver problem ?

I'm also using CSV because all the data is preformatted and the CSV format
is a text format so I could use the OLE DB data connection and be much
more faster* ...

Anybody could help me please ?

Thanks

Jean-Philippe

[Peter Jamieson]

If I select OLE DB data source, I have a screen popping up twice
which ask
me to choose the type of separated value. Once the merge is done, I
have the
bug that all the 0s in my data source file are 12:00:00 AM ! However,
I did
the same thing with same Word mailmerge file and the same data source
on a
colleague's computer and I do not have this "bug". Also when I'm
switching
of records, it's really fast compared to the 2 other data connections I
tried.

If
Word Office Button-Word Options-Advanced-General-Confirm File Format
Conversion on Open
is checked, you see different behaviour when opening a text file than
when it is unchecked. When it is unchecked, you don'tget to choose the
connection type and if Word opens the file using its internal text
reader, you probably won't suffer from this particular problem. If it is
checked, you have to choose, and Word 2007 may only display "OLE DB
Database Files". You can check "Shw ALl" and try to select the Text
converter.

However, once you have successfully connected to the data source, Word
will /probably/ use the same method when it re-opens the mail merge main
document (not necessarily, because there are limitations on the OLE DB
method that may cause Word to use the text converter.

When Word opens the file using OLE DB, it uses the Jet (or ACE) OLE DB
provider (well, in earlier versions of Word it might use the Jet ODBC
driver). That tries to assign one of 5 data types to each column, and
coerce all values in the column to that type. My page at
http://tips.pjmsn.me.uk/t0003.htm describes what happens for Excel
files, but the situation is similar for delimited text files.

Programmatically, you can influence Word's behaviour when it opens the
file using OLE DB by using a SCHEMA.INI file in the same folder as the
data file to specify column type information, a field delimiter
character and a character set. I don't have time to post that stuff now
but a google groups search for Peter Jamieson schema.ini may help

And yes, you are right, Mailmerge typically works much faster with OLE
DB connections than connections via text file converters.

You might be better off loading your calculated data into an Access
table, which is actually likely to be more reliably read by both Word
and Excel (not so sure about the implicaitions for Excel though - I
think when you open such a table in Excel you will probably end up with
an Excel "table" which can be harder to work with than regular worksheets)

Peter Jamieson

http://tips.pjmsn.me.uk

On 20/01/2010 17:53, JPGaudreau wrote:
Hi,

I have an Access database which contains my raw data. With C#, I'm doing a
lots of calculations on these data.

I'm exporting these calculated data into a CSV file. But before exporting
these data I'm formatting them (in the language required for each record).
Once my calculated data have been exported, I doing a mailmerge with Word
2003. I'm using CSV because it's also easy to open it with Excel.

If I select OLE DB data source, I have a screen popping up twice which ask
me to choose the type of separated value. Once the merge is done, I have the
bug that all the 0s in my data source file are 12:00:00 AM ! However, I did
the same thing with same Word mailmerge file and the same data source on a
colleague's computer and I do not have this "bug". Also when I'm switching
of records, it's really fast compared to the 2 other data connections I
tried.

If I'm trying to format the mergefield in word (\# #,##0.00), I'm getting
the value 12 instead of 12:00:00 AM.
Also, when I'm doing the merge with the data source DDE or Text files, I'm
not getting this bug. However, like I said it's really slower than with OLE
DB.

Is that could a be driver problem ?

I'm also using CSV because all the data is preformatted and the CSV format
is a text format so I could use the OLE DB data connection and be much more
faster* ...

Anybody could help me please ?

Thanks

Jean-Philippe

[JPGaudreau]

Thanks Doug,

However, on my colleague's computer, the fields are showing 0s and not 12. I
guess I was not clear in my original message.

The value in the fields are 0s. For example, the values in the fields
"Contributions" are 0,00 for all except records 43, 56 and 61. In Word, I
have the following:
{ IF { MERGEFIELD "Contributions" } = 0 "" { MERGEFIELD "Contributions" \#
"# ##0,00}"}

When I'm merging I get 12s. My colleague get nothing which is what I want. I
already verify all
the Control Panel Regionnal Settings and they are the same.

Also, I have the following problem (found on the MVP Word site) :

Dates: day/month reversed
On Windows installations where the short date format in the Regional
settings is not the North American standard month/day/year, Excel and Access
users will find that, in cases where the day is 12 or less, the month and
day will come across the link reversed.

This is a bug; and comes from the OLEDB driver Microsoft provides for these
two applications. Not only Word Mail merge users struggle with this
problem - it also affects database programmers.

In this case, simply adding a formatting switch to the merge fields will not
resolve the problem.

You have two choices: The data will have to be pre-formatted, as text, as
described in the general Mail merge section; or use a DDE or ODBC
connection.


Again, my colleague can do the all the same operations as me but without
having this problem. She open the same word merge document and connect to
the same CSV data source thru OLE DB and don't get the problems that I get
(12s showing every where values a 0 and day/month reversed dates).

Can I update my OLE DB driver ? Could it be the my colleague has more up to
date OLE DB drivers ? How and wher can I verify which version of the OLE DB
drivers I'm using ?

Thanks again

Jean-Philippe




"Doug Robbins - Word MVP" a écrit dans le message
de news: ...
For the problem fields, the data in the first records(s) must be being
detected by Word as Date fields an on your colleague's computer, the
Control Panel Date format is probably set to not include the time in
addition to the date.

The switch should be \@ "hh:MM:ss AM/PM" to get 12:00:00 AM

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"JPGaudreau" wrote in message
...
Hi,

I have an Access database which contains my raw data. With C#, I'm doing
a lots of calculations on these data.

I'm exporting these calculated data into a CSV file. But before exporting
these data I'm formatting them (in the language required for each
record). Once my calculated data have been exported, I doing a mailmerge
with Word 2003. I'm using CSV because it's also easy to open it with
Excel.

If I select OLE DB data source, I have a screen popping up twice which
ask me to choose the type of separated value. Once the merge is done, I
have the bug that all the 0s in my data source file are 12:00:00 AM !
However, I did the same thing with same Word mailmerge file and the same
data source on a colleague's computer and I do not have this "bug". Also
when I'm switching of records, it's really fast compared to the 2 other
data connections I tried.

If I'm trying to format the mergefield in word (\# #,##0.00), I'm getting
the value 12 instead of 12:00:00 AM.
Also, when I'm doing the merge with the data source DDE or Text files,
I'm not getting this bug. However, like I said it's really slower than
with OLE DB.

Is that could a be driver problem ?

I'm also using CSV because all the data is preformatted and the CSV
format is a text format so I could use the OLE DB data connection and be
much more faster* ...

Anybody could help me please ?

Thanks

Jean-Philippe

[JPGaudreau]

Thank you very much Peter.

It now works with the SCEMA.INI file.

JP
"Peter Jamieson" a écrit dans le message de
news: ...
If I select OLE DB data source, I have a screen popping up twice
which ask
me to choose the type of separated value. Once the merge is done, I
have the
bug that all the 0s in my data source file are 12:00:00 AM ! However,
I did
the same thing with same Word mailmerge file and the same data source
on a
colleague's computer and I do not have this "bug". Also when I'm
switching
of records, it's really fast compared to the 2 other data connections I
tried.

If
Word Office Button-Word Options-Advanced-General-Confirm File Format
Conversion on Open
is checked, you see different behaviour when opening a text file than when
it is unchecked. When it is unchecked, you don'tget to choose the
connection type and if Word opens the file using its internal text reader,
you probably won't suffer from this particular problem. If it is checked,
you have to choose, and Word 2007 may only display "OLE DB Database
Files". You can check "Shw ALl" and try to select the Text converter.

However, once you have successfully connected to the data source, Word
will /probably/ use the same method when it re-opens the mail merge main
document (not necessarily, because there are limitations on the OLE DB
method that may cause Word to use the text converter.

When Word opens the file using OLE DB, it uses the Jet (or ACE) OLE DB
provider (well, in earlier versions of Word it might use the Jet ODBC
driver). That tries to assign one of 5 data types to each column, and
coerce all values in the column to that type. My page at
http://tips.pjmsn.me.uk/t0003.htm describes what happens for Excel files,
but the situation is similar for delimited text files.

Programmatically, you can influence Word's behaviour when it opens the
file using OLE DB by using a SCHEMA.INI file in the same folder as the
data file to specify column type information, a field delimiter character
and a character set. I don't have time to post that stuff now but a google
groups search for Peter Jamieson schema.ini may help

And yes, you are right, Mailmerge typically works much faster with OLE DB
connections than connections via text file converters.

You might be better off loading your calculated data into an Access table,
which is actually likely to be more reliably read by both Word and Excel
(not so sure about the implicaitions for Excel though - I think when you
open such a table in Excel you will probably end up with an Excel "table"
which can be harder to work with than regular worksheets)

Peter Jamieson

http://tips.pjmsn.me.uk

On 20/01/2010 17:53, JPGaudreau wrote:
Hi,

I have an Access database which contains my raw data. With C#, I'm doing
a
lots of calculations on these data.

I'm exporting these calculated data into a CSV file. But before exporting
these data I'm formatting them (in the language required for each
record).
Once my calculated data have been exported, I doing a mailmerge with Word
2003. I'm using CSV because it's also easy to open it with Excel.

If I select OLE DB data source, I have a screen popping up twice which
ask
me to choose the type of separated value. Once the merge is done, I have
the
bug that all the 0s in my data source file are 12:00:00 AM ! However, I
did
the same thing with same Word mailmerge file and the same data source on
a
colleague's computer and I do not have this "bug". Also when I'm
switching
of records, it's really fast compared to the 2 other data connections I
tried.

If I'm trying to format the mergefield in word (\# #,##0.00), I'm getting
the value 12 instead of 12:00:00 AM.
Also, when I'm doing the merge with the data source DDE or Text files,
I'm
not getting this bug. However, like I said it's really slower than with
OLE
DB.

Is that could a be driver problem ?

I'm also using CSV because all the data is preformatted and the CSV
format
is a text format so I could use the OLE DB data connection and be much
more
faster* ...

Anybody could help me please ?

Thanks

Jean-Philippe