Quotation marks (") in tab delimeted file causes loss of forma

Cindy,

Sorry for the delay in responding but it seems that every time I try and get
to this site it is unavailable - for weeks at a time!

This is a regular process that runs from a non-windows database. It writes
the information to the tab delimited file and then initiates the Word
document and prints it. This is a fully automated process and I do not see
how I could automate writing the record, running Excel, saving as an Excel
document and then running Word using the Excel file as the datasource.

I have tried creating the datasource with single quotes and it made no
difference to word. Excel reads it just fine.

This problem has been around since at least Word97 and it is a right royal
pain in the rectum.

I need a method of writing the datasource (I have FULL control over this
process) in such a way that Word can handle a string of data with a " in it.
I do not care how much I have to "fiddle" the datasource as that part is very
easy for me. But I do NOT want to have to run Excel to convert the data into
an Excel spreadsheet and then run Word. Too many steps!

Please Note: If I import into Excel and export as a comma or tab delimited
file I still have the same problem in Word!

What I cannot understand is why Excel imports the data perfectly but Word
cannot. The routine that handles that sort of file should be common to both
routines but it obviously is totally independant between the two products.

This is a Microsoft problem and I am truly sick of doing work-arounds to fix
a problem in someone elses software, especially when the problem has been
around for 10 years or more!

Please, please, please can someone either fix the problem in Word or come up
with a work-around that does not involve a complicated multistep process.

"Cindy M -WordMVP-" wrote:

Hi =?Utf-8?B?Um9zcw==?=,

The process merges data from a database into a Word formatted Invoice
document and I would rather not have to completely rebuild the Invoice in
Excel.

You misunderstood. Import the data into Excel, then use the Excel file for
the Word merge instead of the text file.

That, or you have to put 'single quotes' around each field entry that might
contain a " sign.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

In my experience, which is not particularly strong in this area, the only
way you will get this to work is to double up your quotes and surround the
string by quotes. As long as Word opens your text file using its text
converter and not ODBC then that also has the bonus of allowing you to use
multiline text and tabs (i.e. your field delimiter character) within your
text fields.

In other words, if your data contains

text1tabinches"tabminutes"tabother text

try

"text1"tab"inches"""tab"minutes"""tab"ot her text"

I do not think you have to quote the fields that do not contain
double-quotes but I do not think it does any harm either.

One problem is whether Word uses ODBC to read your text file or not.
Although it may be possible to issue an OpenDataSource command that
definitely does not use ODBC (or OLEDB for that matter)
a. you have already said that you do not have control over that part of the
process
b. I am not sure it is possible, at least in Word 2000.

Another problem may well be that the data in the file is sufficiently
complicated that Word ends up displaying its delimiter dialog box anyway,
which obviously screws up any automated process.

Peter Jamieson

"Ross" wrote in message
...
Cindy,

Sorry for the delay in responding but it seems that every time I try and
get
to this site it is unavailable - for weeks at a time!

This is a regular process that runs from a non-windows database. It writes
the information to the tab delimited file and then initiates the Word
document and prints it. This is a fully automated process and I do not see
how I could automate writing the record, running Excel, saving as an Excel
document and then running Word using the Excel file as the datasource.

I have tried creating the datasource with single quotes and it made no
difference to word. Excel reads it just fine.

This problem has been around since at least Word97 and it is a right royal
pain in the rectum.

I need a method of writing the datasource (I have FULL control over this
process) in such a way that Word can handle a string of data with a " in
it.
I do not care how much I have to "fiddle" the datasource as that part is
very
easy for me. But I do NOT want to have to run Excel to convert the data
into
an Excel spreadsheet and then run Word. Too many steps!

Please Note: If I import into Excel and export as a comma or tab delimited
file I still have the same problem in Word!

What I cannot understand is why Excel imports the data perfectly but Word
cannot. The routine that handles that sort of file should be common to
both
routines but it obviously is totally independant between the two products.

This is a Microsoft problem and I am truly sick of doing work-arounds to
fix
a problem in someone elses software, especially when the problem has been
around for 10 years or more!

Please, please, please can someone either fix the problem in Word or come
up
with a work-around that does not involve a complicated multistep process.

"Cindy M -WordMVP-" wrote:

Hi =?Utf-8?B?Um9zcw==?=,

The process merges data from a database into a Word formatted Invoice
document and I would rather not have to completely rebuild the Invoice
in
Excel.

You misunderstood. Import the data into Excel, then use the Excel file
for
the Word merge instead of the text file.

That, or you have to put 'single quotes' around each field entry that
might
contain a " sign.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Thanks Peter but I have tried surrounding the text with single quotes. I have
not tried double quotes but since that would lead to an odd number of double
quotes I would be surprised if it worked.

The application I am running could not be simpler.

I have an Invoice document, written in Word, and I am using a tab delimited
data source with the first line being the name of the column.

On the invoice I have part number, description, qty, price, ext price but
because the description has a " in it, the data following the ", up to the
next " if there is one or to the end of the row if there are no more "'s in
the row, is lumped into the description.

I have tried prefixing the " with a \ (remember seeing that somewhere) and I
have tried using """ and I have tried using single ' marks on either side of
the description field but all to no avail.

I will check with the person who wrote the process that fires off and prints
the document if he is using ODBC but if he is am I screwed or is there a way
to tell ODBC that the field is valid?

"Peter Jamieson" wrote:

In my experience, which is not particularly strong in this area, the only
way you will get this to work is to double up your quotes and surround the
string by quotes. As long as Word opens your text file using its text
converter and not ODBC then that also has the bonus of allowing you to use
multiline text and tabs (i.e. your field delimiter character) within your
text fields.

In other words, if your data contains

text1tabinches"tabminutes"tabother text

try

"text1"tab"inches"""tab"minutes"""tab"ot her text"

I do not think you have to quote the fields that do not contain
double-quotes but I do not think it does any harm either.

One problem is whether Word uses ODBC to read your text file or not.
Although it may be possible to issue an OpenDataSource command that
definitely does not use ODBC (or OLEDB for that matter)
a. you have already said that you do not have control over that part of the
process
b. I am not sure it is possible, at least in Word 2000.

Another problem may well be that the data in the file is sufficiently
complicated that Word ends up displaying its delimiter dialog box anyway,
which obviously screws up any automated process.

Peter Jamieson

"Ross" wrote in message
...
Cindy,

Sorry for the delay in responding but it seems that every time I try and
get
to this site it is unavailable - for weeks at a time!

This is a regular process that runs from a non-windows database. It writes
the information to the tab delimited file and then initiates the Word
document and prints it. This is a fully automated process and I do not see
how I could automate writing the record, running Excel, saving as an Excel
document and then running Word using the Excel file as the datasource.

I have tried creating the datasource with single quotes and it made no
difference to word. Excel reads it just fine.

This problem has been around since at least Word97 and it is a right royal
pain in the rectum.

I need a method of writing the datasource (I have FULL control over this
process) in such a way that Word can handle a string of data with a " in
it.
I do not care how much I have to "fiddle" the datasource as that part is
very
easy for me. But I do NOT want to have to run Excel to convert the data
into
an Excel spreadsheet and then run Word. Too many steps!

Please Note: If I import into Excel and export as a comma or tab delimited
file I still have the same problem in Word!

What I cannot understand is why Excel imports the data perfectly but Word
cannot. The routine that handles that sort of file should be common to
both
routines but it obviously is totally independant between the two products.

This is a Microsoft problem and I am truly sick of doing work-arounds to
fix
a problem in someone elses software, especially when the problem has been
around for 10 years or more!

Please, please, please can someone either fix the problem in Word or come
up
with a work-around that does not involve a complicated multistep process.

"Cindy M -WordMVP-" wrote:

Hi =?Utf-8?B?Um9zcw==?=,

The process merges data from a database into a Word formatted Invoice
document and I would rather not have to completely rebuild the Invoice
in
Excel.

You misunderstood. Import the data into Excel, then use the Excel file
for
the Word merge instead of the text file.

That, or you have to put 'single quotes' around each field entry that
might
contain a " sign.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Thanks Peter but I have tried surrounding the text with single quotes. I
have
not tried double quotes but since that would lead to an odd number of
double
quotes I would be surprised if it worked.

I tried it before I posted my response and it works here. However, you do
have to /double up/ any double quotes in your data as well, which you may
not have done before.

Obviously I cannot guarantee that it works there for the reasons I have
given, among others. Doubling up "quoted" characters has always been one of
the standard ways to deal with tthis kind of situation as you can see, for
example, if you write a bit of VBA to assign text to a string variable then

mystring = "here is some text with a double quote "" inside it"

it will work.

So why not give it a try?

I have tried prefixing the " with a \ (remember seeing that somewhere) and
I

Yes, among other things there's a curious option in the Compatibility
options within Word to do with \", but I've never got it to do anything
useful.

I will check with the person who wrote the process that fires off and
prints
the document if he is using ODBC but if he is am I screwed or is there a
way
to tell ODBC that the field is valid?

The problem is not so much whether the programmer has chosen to use ODBC. In
some situations Word chooses for you and it's not necessarily
straightforward to choose /not/ to use ODBC.

However, if you have full control over the format of the file you are
creating and can have the Word document changed as well, then there are
other things you can try if none of the above work. I can't say "this or
that particular approach will definitely work whatever you are using, but
a. if you can write any "plain text" type output you could try
- writing an HTML format file containing a table with one cell per
datum. This will probably work for up to around 63 data columns. it doesn't
need to be "standard" HTML with all the right headers for Word to
understand it, e.g. if you have two columns "key" and "text" you can use

HTML
BODY
TABLE
TR
TDKey/TD
TDText/TD
/TR
TR
TD1/TD
TDtext with " quotes/TD
/TR
/TABLE
/BODY
/HTML

You don't get the multiline stuff.

In fact I think you can get away without the HTMLHEAD and if you lay out
the text rather differently

TABLE
TRTDKey/TDTDText/TD/TR
TRTD1/TDTDtext with " quotes/TD/TR
/TABLE

you can see that it is structurally similar to a delimited text file, but
with a slightly more complex system of delimiters.

- writing an RTF file with tab delimiters. I can give you more info on what
that needs to look like if you want.

b. If you are able to use Windows API calls - I notice that automating
Excel, or presumably Word, is a step to far for you - you could consider
using ADO to create a .mdb (Jet) file directly. But I guess that depends
primarily on where the processing is happening and so on.

I don't suppose it can be done using a view in your DBMS and a suitable
ODBC/OLEDB connection at the Workstation end?

Peter Jamieson

"Ross" wrote in message
...
Thanks Peter but I have tried surrounding the text with single quotes. I
have
not tried double quotes but since that would lead to an odd number of
double
quotes I would be surprised if it worked.

The application I am running could not be simpler.

I have an Invoice document, written in Word, and I am using a tab
delimited
data source with the first line being the name of the column.

On the invoice I have part number, description, qty, price, ext price but
because the description has a " in it, the data following the ", up to the
next " if there is one or to the end of the row if there are no more "'s
in
the row, is lumped into the description.

I have tried prefixing the " with a \ (remember seeing that somewhere) and
I
have tried using """ and I have tried using single ' marks on either side
of
the description field but all to no avail.

I will check with the person who wrote the process that fires off and
prints
the document if he is using ODBC but if he is am I screwed or is there a
way
to tell ODBC that the field is valid?

"Peter Jamieson" wrote:

In my experience, which is not particularly strong in this area, the only
way you will get this to work is to double up your quotes and surround
the
string by quotes. As long as Word opens your text file using its text
converter and not ODBC then that also has the bonus of allowing you to
use
multiline text and tabs (i.e. your field delimiter character) within your
text fields.

In other words, if your data contains

text1tabinches"tabminutes"tabother text

try

"text1"tab"inches"""tab"minutes"""tab"ot her text"

I do not think you have to quote the fields that do not contain
double-quotes but I do not think it does any harm either.

One problem is whether Word uses ODBC to read your text file or not.
Although it may be possible to issue an OpenDataSource command that
definitely does not use ODBC (or OLEDB for that matter)
a. you have already said that you do not have control over that part of
the
process
b. I am not sure it is possible, at least in Word 2000.

Another problem may well be that the data in the file is sufficiently
complicated that Word ends up displaying its delimiter dialog box anyway,
which obviously screws up any automated process.

Peter Jamieson

"Ross" wrote in message
...
Cindy,

Sorry for the delay in responding but it seems that every time I try
and
get
to this site it is unavailable - for weeks at a time!

This is a regular process that runs from a non-windows database. It
writes
the information to the tab delimited file and then initiates the Word
document and prints it. This is a fully automated process and I do not
see
how I could automate writing the record, running Excel, saving as an
Excel
document and then running Word using the Excel file as the datasource.

I have tried creating the datasource with single quotes and it made no
difference to word. Excel reads it just fine.

This problem has been around since at least Word97 and it is a right
royal
pain in the rectum.

I need a method of writing the datasource (I have FULL control over
this
process) in such a way that Word can handle a string of data with a "
in
it.
I do not care how much I have to "fiddle" the datasource as that part
is
very
easy for me. But I do NOT want to have to run Excel to convert the data
into
an Excel spreadsheet and then run Word. Too many steps!

Please Note: If I import into Excel and export as a comma or tab
delimited
file I still have the same problem in Word!

What I cannot understand is why Excel imports the data perfectly but
Word
cannot. The routine that handles that sort of file should be common to
both
routines but it obviously is totally independant between the two
products.

This is a Microsoft problem and I am truly sick of doing work-arounds
to
fix
a problem in someone elses software, especially when the problem has
been
around for 10 years or more!

Please, please, please can someone either fix the problem in Word or
come
up
with a work-around that does not involve a complicated multistep
process.

"Cindy M -WordMVP-" wrote:

Hi =?Utf-8?B?Um9zcw==?=,

The process merges data from a database into a Word formatted
Invoice
document and I would rather not have to completely rebuild the
Invoice
in
Excel.

You misunderstood. Import the data into Excel, then use the Excel file
for
the Word merge instead of the text file.

That, or you have to put 'single quotes' around each field entry that
might
contain a " sign.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or
reply in the newsgroup and not by e-mail :-)