Changing Export Delimiter

[Guest]

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

 

[Guest]

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

 

[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.

 

[Guest]

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]

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.

 

[Guest]

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]

Pete,

I'm still a little confused. The dialog box title says "Open" but it
doesn't, and shouldn't, open a file. So the button should not say "Open" as
you suggested. It's just to pick a name of an existing file. The Browse
button is directly related to "File name to write." I can change the title
of the dialog -- something like "Select file name." I'll put that in a
subsequent release. Or have I totally missed your point.

As for Excel's limited text capabilities, they're legend. That's why I
wrote a simple little program, and it got popular, so I expanded it over the
years. I remember once finding some strangeness in the way Excel reads a
text file, the details of which escape me at the moment, where Access
handled it perfectly.

Let us know when your site is up.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

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