Changing Export Delimiter

[Total Hosting 1]

Does any know of any means to change the delimiter when saving as text? I
want to use | (pipes) instead of TAB or CSV. I also want to get rid of
quotes.

I really wish Excel would behave like Access in this regard. Anyone?

Thanks

Pete

[Total Hosting 1]

Ok, I found it here.

http://www.smokeylake.com/excel/text_write_program.htm

Still not 100%, but better than what Microsoft can do on it's own ;-)

Pete

"Total Hosting 1" wrote:

> Does any know of any means to change the delimiter when saving as text? I
> want to use | (pipes) instead of TAB or CSV. I also want to get rid of
> quotes.
>
> I really wish Excel would behave like Access in this regard. Anyone?
>
> Thanks
>
> Pete

[Earl Kiosterud]

Pete,

The Text Write Program can write a text file with no quotes, just leave the
"bracketing (text qualifier)" field blank in the Setup sheet. Be careful,
though, as the program reading the file may improperly parse the fields if
the quote marks aren't used. The details are at that site. If it's still
not 100%, post back with details.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Total Hosting 1" <TotalHosting1@discussions.microsoft.com> wrote in message
news:CDDC5AF7-F518-4CC3-B75A-FAD12EFD6F32@microsoft.com...
> Ok, I found it here.
>
> http://www.smokeylake.com/excel/text_write_program.htm
>
> Still not 100%, but better than what Microsoft can do on it's own ;-)
>
> Pete
>
> "Total Hosting 1" wrote:
>
>> Does any know of any means to change the delimiter when saving as text? I
>> want to use | (pipes) instead of TAB or CSV. I also want to get rid of
>> quotes.
>>
>> I really wish Excel would behave like Access in this regard. Anyone?
>>
>> Thanks
>>
>> Pete

[Total Hosting 1]

Hi Earl.

Thanks. I posted this to the thread where I found your script, but in case
you aren't trolling around, Here is my post since I have your attention ;-)

Hi Earl

Great job. Two things that would be great, if I may.

1) I like the browse button, but that is for opening a program. You might
want to label it as such. I was looking for a way to change the folder the
file saves in. Which, due to proximity, was what I expected. You should put
in a
mention that the output is saved relative to your file, not the source.

2) I would like to have delimiters around empty cells in rows. It would be
nice, especially if you define an explicit area for export. Some rows have
all columns filled, others do not. I see that it puts in delims if there is a
non-empty cell a few columns over to the right. The other idea would be for
the macro to scan the worksheet, determine the right-most column used and
then rip the sheet.

Meaning if I have table like this:

FIELD 1 FIELD 2 FIELD 3 FIELD 4
1 XXX XXX XXX XXX
2 XXX XXX
3 XXX XXX

I would get this output:

XXX|XXX|XXX|XXX
XXX|XXX
XXX|||XXX

When I need:

XXX|XXX|XXX|XXX
XXX|XXX||
XXX|||XXX


Thanks

Pete

"Earl Kiosterud" wrote:

> Pete,
>
> The Text Write Program can write a text file with no quotes, just leave the
> "bracketing (text qualifier)" field blank in the Setup sheet. Be careful,
> though, as the program reading the file may improperly parse the fields if
> the quote marks aren't used. The details are at that site. If it's still
> not 100%, post back with details.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Total Hosting 1" <TotalHosting1@discussions.microsoft.com> wrote in message
> news:CDDC5AF7-F518-4CC3-B75A-FAD12EFD6F32@microsoft.com...
> > Ok, I found it here.
> >
> > http://www.smokeylake.com/excel/text_write_program.htm
> >
> > Still not 100%, but better than what Microsoft can do on it's own ;-)
> >
> > Pete
> >
> > "Total Hosting 1" wrote:
> >
> >> Does any know of any means to change the delimiter when saving as text? I
> >> want to use | (pipes) instead of TAB or CSV. I also want to get rid of
> >> quotes.
> >>
> >> I really wish Excel would behave like Access in this regard. Anyone?
> >>
> >> Thanks
> >>
> >> Pete
>
>
>

[Earl Kiosterud]

Pete,

Thanks for the feedback.

As for the browse button, it's job is to give you a file - open dialog, and
to only put any selected file name into the name box of the Setup sheet. It
shouldn't open any files, or start any programs. If it does, something is
gerwhacko. Let me know.The dialog lists all files, and starts in the
current Excel folder, which you can change while you're in the dialog. If
you change folders while in that dialog, the Excel default path (current
folder) will be changed, which will determine where the file is written, per
Windows file specification rules. I hope, anyway.

a.txt - put file in Excel default path, default drive.
\a txt - put file in root of default drive. Ignore the default path
(current folder).
MyFolder\a.txt - put file in folder MyFolder, which should already be in the
Excel default path.
\MyFolder\a.txt - put file in MyFolder, which should already be in the root
of the default drive. Ignore the default path.
D:\Myfolder\MyDeeperFolder\a.txt - put file in drive and path specified,
ignoring default drive and default path.

As for writing the additional delimiters until a fixed count of fields has
been written to each record, per your example, the program normally does not
do that, but will do so if you use the "Write rectangular" option. You must
manually select the range it's to use, which determines how many fields to
write in each record (as well as how many records to write). You can have it
expand the selection from a single selected cell (as with sorting, charts,
etc.), if your data is contiguous and not adjacent to other data. If this
doesn't work that way, or doesn't meet your needs, let me know.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Total Hosting 1" <TotalHosting1@discussions.microsoft.com> wrote in message
news:8823D7A5-66C7-4AA0-95FB-863026A928E1@microsoft.com...
> Hi Earl.
>
> Thanks. I posted this to the thread where I found your script, but in case
> you aren't trolling around, Here is my post since I have your attention
> ;-)
>
> Hi Earl
>
> Great job. Two things that would be great, if I may.
>
> 1) I like the browse button, but that is for opening a program. You might
> want to label it as such. I was looking for a way to change the folder the
> file saves in. Which, due to proximity, was what I expected. You should
> put
> in a
> mention that the output is saved relative to your file, not the source.
>
> 2) I would like to have delimiters around empty cells in rows. It would be
> nice, especially if you define an explicit area for export. Some rows have
> all columns filled, others do not. I see that it puts in delims if there
> is a
> non-empty cell a few columns over to the right. The other idea would be
> for
> the macro to scan the worksheet, determine the right-most column used and
> then rip the sheet.
>
> Meaning if I have table like this:
>
> FIELD 1 FIELD 2 FIELD 3 FIELD 4
> 1 XXX XXX XXX XXX
> 2 XXX XXX
> 3 XXX XXX
>
> I would get this output:
>
> XXX|XXX|XXX|XXX
> XXX|XXX
> XXX|||XXX
>
> When I need:
>
> XXX|XXX|XXX|XXX
> XXX|XXX||
> XXX|||XXX
>
>
> Thanks
>
> Pete
>
> "Earl Kiosterud" wrote:
>
>> Pete,
>>
>> The Text Write Program can write a text file with no quotes, just leave
>> the
>> "bracketing (text qualifier)" field blank in the Setup sheet. Be
>> careful,
>> though, as the program reading the file may improperly parse the fields
>> if
>> the quote marks aren't used. The details are at that site. If it's
>> still
>> not 100%, post back with details.
>>
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "Total Hosting 1" <TotalHosting1@discussions.microsoft.com> wrote in
>> message
>> news:CDDC5AF7-F518-4CC3-B75A-FAD12EFD6F32@microsoft.com...
>> > Ok, I found it here.
>> >
>> > http://www.smokeylake.com/excel/text_write_program.htm
>> >
>> > Still not 100%, but better than what Microsoft can do on it's own ;-)
>> >
>> > Pete
>> >
>> > "Total Hosting 1" wrote:
>> >
>> >> Does any know of any means to change the delimiter when saving as
>> >> text? I
>> >> want to use | (pipes) instead of TAB or CSV. I also want to get rid of
>> >> quotes.
>> >>
>> >> I really wish Excel would behave like Access in this regard. Anyone?
>> >>
>> >> Thanks
>> >>
>> >> Pete
>>
>>
>>

[Total Hosting 1]

Hi earl,

No, it's not GerWacko. I am just telling you what I *expected* to have
happen. It did in fact open a dialog to open an existing file. But since it's
on the same line as the file name field (and follows immediately after "File
Name to Write" I just expected it to behave differently than it did. Again,
due to it's proximity to a completely different type of function, but one in
which the same interface widget (i.e. "browse button") couild be applicable.
A simple change from "browse" to "open" might make it easier. Prehaps
swapping the "FNtw" and "Browse" might be useful. Trust me, I am no
technophile, but I did get confused.

As far as the rectangular selection, that was perfect. EXACTLY what I
needed. Of course it was right there in the "help" if I had bothered to look.

I am still blown away by the fact that Excel is so limited in it's export
capabilities. The functionality is right there in Access. But not in Excel.

Sheesh!

Well thanks for the superb job. I am just putting finishing touches on my
new site. I am going to throw you a link in my "resources" section.

Pete

"Earl Kiosterud" wrote:

> Pete,
>
> Thanks for the feedback.
>
> As for the browse button, it's job is to give you a file - open dialog, and
> to only put any selected file name into the name box of the Setup sheet. It
> shouldn't open any files, or start any programs. If it does, something is
> gerwhacko. Let me know.The dialog lists all files, and starts in the
> current Excel folder, which you can change while you're in the dialog. If
> you change folders while in that dialog, the Excel default path (current
> folder) will be changed, which will determine where the file is written, per
> Windows file specification rules. I hope, anyway.
>
> a.txt - put file in Excel default path, default drive.
> \a txt - put file in root of default drive. Ignore the default path
> (current folder).
> MyFolder\a.txt - put file in folder MyFolder, which should already be in the
> Excel default path.
> \MyFolder\a.txt - put file in MyFolder, which should already be in the root
> of the default drive. Ignore the default path.
> D:\Myfolder\MyDeeperFolder\a.txt - put file in drive and path specified,
> ignoring default drive and default path.
>
> As for writing the additional delimiters until a fixed count of fields has
> been written to each record, per your example, the program normally does not
> do that, but will do so if you use the "Write rectangular" option. You must
> manually select the range it's to use, which determines how many fields to
> write in each record (as well as how many records to write). You can have it
> expand the selection from a single selected cell (as with sorting, charts,
> etc.), if your data is contiguous and not adjacent to other data. If this
> doesn't work that way, or doesn't meet your needs, let me know.
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------