Text file data source problem. Not recognizing tabs.

[Jelmore]

This is for Word 2003. I have a mail merge document that uses a
tab-separated text file as the data source. The text file looks something
like this:
Id Fname Lname address
01 John Doe 123 Fake St.
02 Joe Six-Pack 123 Fake St.

When creating the merge file, I specify the data source as an OLE DB
Database File (at the Confirm Data Source window), and then indicate that the
field delimiter is Tab. This merge file seems to work fine on all the
computers in the office except for one. On this one computer, Word isn't
breaking the fields up as it should. Instead, it's combining all the fields
in each record into a single field. So instead of seeing 4 fields (Id,
Fname, Lname, and address), it sees a single field named
Id_Fname_Lname_address. Obviously Word can't find that single field in the
data source, and the merge fails. The merge was working on this computer as
recently as a couple weeks ago, and I don't know what could have changed. Is
there some setting that the user could have inadvertently changed that would
cause this? Any ideas at all? Thanks.

 

[macropod]

Hi Jef,

You can do this without a macro. Simply:
.. open the mailmerge main document
.. press Alt-F9 to expose the field codes
.. Use Find/Replace with:
. 'MERGEFIELD *_Perc' for the 'Find' text
. '^& ^92# $,0.00' for the 'Replace' text
. 'Use wildcards' checked
.. press Alt-F9 again to hide the field codes
.. save the mailmerge main document
.. run your mailmerge.

You'll note that I've used '\# $,0.00' as the formatting switch, instead of '\# $#,###.00' as you proposed. The reason for doing
this is that your rendition of the switch pads all values of less than $1,000 with spaces after the '$' and could lead to '$0.50',
for example, being displayed as '$ .50' (ie no '0' before the cents).

 

[macropod]

Hi Jef,

You can do this without a macro. Simply:
.. open the mailmerge main document
.. press Alt-F9 to expose the field codes
.. Use Find/Replace with:
. 'MERGEFIELD *_Perc' for the 'Find' text
. '^& ^92# $,0.00' for the 'Replace' text
. 'Use wildcards' checked
.. press Alt-F9 again to hide the field codes
.. save the mailmerge main document
.. run your mailmerge.

You'll note that I've used '\# $,0.00' as the formatting switch, instead of '\# $#,###.00' as you proposed. The reason for doing
this is that your rendition of the switch pads all values of less than $1,000 with spaces after the '$' and could lead to '$0.50',
for example, being displayed as '$ .50' (ie no '0' before the cents).

 

[macropod]

Hi Jelmore,

Please ignore my previous replies - somehow I managed to send them to thr wrong post.

For your problem computer, have you tried repairing the Office installation (Help|Detect & Repair)?

 

[Peter Jamieson]

A possibility: have a look for a text file called schema.ini in the same
folder as the data source, open it (e.g. using Notepad) and see if there
is a section titled

[your file name]

If so, ensure that you have a line like

Format=TabDelimited

and not
Format=CSVDelimited

or some other "Format=" text.

Typically, the OLE DB method that Word uses does not create these
entries in SCHEMA.INI (although if the user had selected the ODBC text
driver by mistake at some point, such an entry might have been created).
But it will use them if they are already there.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Jelmore]

Thanks for the suggestions. Unfortunately, neither seems to have worked. I
did a Detect & Repair, but the problem persists. And there isn't a
schema.ini file in the data source folder (or anywhere else on the system).
An additional note is that if I open the data source as a Text File (instead
of as an OLE DB Database File), Word separates the field names as it should
(but there are other problems with doing it that way, which is why I use the
OLE DB Database File method).

A possibility: have a look for a text file called schema.ini in the same
folder as the data source, open it (e.g. using Notepad) and see if there
is a section titled

[your file name]

If so, ensure that you have a line like

Format=TabDelimited

and not
Format=CSVDelimited

or some other "Format=" text.

Typically, the OLE DB method that Word uses does not create these
entries in SCHEMA.INI (although if the user had selected the ODBC text
driver by mistake at some point, such an entry might have been created).
But it will use them if they are already there.

Peter Jamieson

http://tips.pjmsn.me.uk

This is for Word 2003. I have a mail merge document that uses a
tab-separated text file as the data source. The text file looks something
like this:
Id Fname Lname address
01 John Doe 123 Fake St.
02 Joe Six-Pack 123 Fake St.

When creating the merge file, I specify the data source as an OLE DB
Database File (at the Confirm Data Source window), and then indicate that the
field delimiter is Tab. This merge file seems to work fine on all the
computers in the office except for one. On this one computer, Word isn't
breaking the fields up as it should. Instead, it's combining all the fields
in each record into a single field. So instead of seeing 4 fields (Id,
Fname, Lname, and address), it sees a single field named
Id_Fname_Lname_address. Obviously Word can't find that single field in the
data source, and the merge fails. The merge was working on this computer as
recently as a couple weeks ago, and I don't know what could have changed. Is
there some setting that the user could have inadvertently changed that would
cause this? Any ideas at all? Thanks.

 

[Peter Jamieson]

OK, I think we have to assume that the problem lies elsewhere. But...
a. Have you tried creating a SCHEMA.INI, just to see what happens?
b. The default expectation of the OLE DB text driver is /probably/ set
in the Windows registry, either at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format
or possibly in an equivalent in the HKEY_CURRENT_USER tree.

It may be worth checking that as well, although I can't imagine why the
value would change.

c. the OLE DB/Jet/ACE provider stuff would probably be a separate
re-install from the Office re-install. I don't have good instructions
for that and it probably depends on your versions of Windows and Office.
d. It may be worth renaming Word's data key and/or Word's normal
template (normal.dot or normal.dotm in Word 2007), although you would
need to restore any standard options/defaults that you set up for your
users afterwards (see
http://word.mvps.org/FAQs/AppErrors/MissingMenusEtc.htm
even though the title suggests it deals with another problem).


Peter Jamieson

http://tips.pjmsn.me.uk

Thanks for the suggestions. Unfortunately, neither seems to have worked. I
did a Detect & Repair, but the problem persists. And there isn't a
schema.ini file in the data source folder (or anywhere else on the system).
An additional note is that if I open the data source as a Text File (instead
of as an OLE DB Database File), Word separates the field names as it should
(but there are other problems with doing it that way, which is why I use the
OLE DB Database File method).

A possibility: have a look for a text file called schema.ini in the same
folder as the data source, open it (e.g. using Notepad) and see if there
is a section titled

[your file name]

If so, ensure that you have a line like

Format=TabDelimited

and not
Format=CSVDelimited

or some other "Format=" text.

Typically, the OLE DB method that Word uses does not create these
entries in SCHEMA.INI (although if the user had selected the ODBC text
driver by mistake at some point, such an entry might have been created).
But it will use them if they are already there.

Peter Jamieson

http://tips.pjmsn.me.uk

This is for Word 2003. I have a mail merge document that uses a
tab-separated text file as the data source. The text file looks something
like this:
Id Fname Lname address
01 John Doe 123 Fake St.
02 Joe Six-Pack 123 Fake St.

When creating the merge file, I specify the data source as an OLE DB
Database File (at the Confirm Data Source window), and then indicate that the
field delimiter is Tab. This merge file seems to work fine on all the
computers in the office except for one. On this one computer, Word isn't
breaking the fields up as it should. Instead, it's combining all the fields
in each record into a single field. So instead of seeing 4 fields (Id,
Fname, Lname, and address), it sees a single field named
Id_Fname_Lname_address. Obviously Word can't find that single field in the
data source, and the merge fails. The merge was working on this computer as
recently as a couple weeks ago, and I don't know what could have changed. Is
there some setting that the user could have inadvertently changed that would
cause this? Any ideas at all? Thanks.

 

[Jelmore]

I created the schema.ini file and it worked. Sweet! I'm still curious to
know why this one computer has a problem, but not so much that I want to
spend any more time on it. Thanks!

Incidentally, I did look at that registry key. Its value was CSVDelimited,
but it's also CSVDelimited on my machine and I wasn't having the merge
problem on my machine, so that must not be the whole story. Anyway, the
schema file worked, so I didn't even bother trying to change the value to
TabDelimited to see what happens. Thanks again.

OK, I think we have to assume that the problem lies elsewhere. But...
a. Have you tried creating a SCHEMA.INI, just to see what happens?
b. The default expectation of the OLE DB text driver is /probably/ set
in the Windows registry, either at

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format
or possibly in an equivalent in the HKEY_CURRENT_USER tree.

It may be worth checking that as well, although I can't imagine why the
value would change.

c. the OLE DB/Jet/ACE provider stuff would probably be a separate
re-install from the Office re-install. I don't have good instructions
for that and it probably depends on your versions of Windows and Office.
d. It may be worth renaming Word's data key and/or Word's normal
template (normal.dot or normal.dotm in Word 2007), although you would
need to restore any standard options/defaults that you set up for your
users afterwards (see
http://word.mvps.org/FAQs/AppErrors/MissingMenusEtc.htm
even though the title suggests it deals with another problem).


Peter Jamieson

http://tips.pjmsn.me.uk

Thanks for the suggestions. Unfortunately, neither seems to have worked. I
did a Detect & Repair, but the problem persists. And there isn't a
schema.ini file in the data source folder (or anywhere else on the system).
An additional note is that if I open the data source as a Text File (instead
of as an OLE DB Database File), Word separates the field names as it should
(but there are other problems with doing it that way, which is why I use the
OLE DB Database File method).

A possibility: have a look for a text file called schema.ini in the same
folder as the data source, open it (e.g. using Notepad) and see if there
is a section titled

[your file name]

If so, ensure that you have a line like

Format=TabDelimited

and not
Format=CSVDelimited

or some other "Format=" text.

Typically, the OLE DB method that Word uses does not create these
entries in SCHEMA.INI (although if the user had selected the ODBC text
driver by mistake at some point, such an entry might have been created).
But it will use them if they are already there.

Peter Jamieson

http://tips.pjmsn.me.uk

Jelmore wrote:
This is for Word 2003. I have a mail merge document that uses a
tab-separated text file as the data source. The text file looks something
like this:
Id Fname Lname address
01 John Doe 123 Fake St.
02 Joe Six-Pack 123 Fake St.

When creating the merge file, I specify the data source as an OLE DB
Database File (at the Confirm Data Source window), and then indicate that the
field delimiter is Tab. This merge file seems to work fine on all the
computers in the office except for one. On this one computer, Word isn't
breaking the fields up as it should. Instead, it's combining all the fields
in each record into a single field. So instead of seeing 4 fields (Id,
Fname, Lname, and address), it sees a single field named
Id_Fname_Lname_address. Obviously Word can't find that single field in the
data source, and the merge fails. The merge was working on this computer as
recently as a couple weeks ago, and I don't know what could have changed. Is
there some setting that the user could have inadvertently changed that would
cause this? Any ideas at all? Thanks.