Alternate delimiters 
Author Message
 Alternate delimiters

I need to export a file from Excel and need to use delimiters other
than comma or tab.  I need to use a pipe (|) or some other keyboard
character that is not in the data (I do not own/control the actual
data).

This should be idiotically simple, but it is not.  The import of such
a file into Excel is idiotically simple but the export out is not.

How can I accomplish this??

I have tried to use a macro that I found on the Internet called "Text
Write Program-1" but could not get it to work for me.

I have also tried to use another macro that I found on the Internet at
http://www.*-*-*.com/ #Export but it only exports
the first 4 rows (out of about 2,600).

Admittedly, I am a total novice regarding macros (took me all day to
figure out how to even successfully copy the above macro into my
spreadsheet).

Do I need to just switch to StarOffice or is there actually a way to
do something as simple as a delimited export from Excel?

Thanks for your help.

--
Knowledge Seeker



Thu, 08 Jul 2004 06:42:04 GMT  
 Alternate delimiters
K.S.,

In your Excel file, you could change all the commas to | characters
(with Edit / Replace), save the file comma-delimited, then use a text
editor to change all the | characters back to commas in the saved
file.

If that won't work for you, please give a more detailed description of
your Excel data, and I'll try to figure out something else.

David

Quote:

> I need to export a file from Excel and need to use delimiters other
> than comma or tab.  I need to use a pipe (|) or some other keyboard
> character that is not in the data (I do not own/control the actual
> data).

> This should be idiotically simple, but it is not.  The import of such
> a file into Excel is idiotically simple but the export out is not.

> How can I accomplish this??

> I have tried to use a macro that I found on the Internet called "Text
> Write Program-1" but could not get it to work for me.

> I have also tried to use another macro that I found on the Internet at
> http://www.cpearson.com/excel/imptext.htm#Export but it only exports
> the first 4 rows (out of about 2,600).

> Admittedly, I am a total novice regarding macros (took me all day to
> figure out how to even successfully copy the above macro into my
> spreadsheet).

> Do I need to just switch to StarOffice or is there actually a way to
> do something as simple as a delimited export from Excel?

> Thanks for your help.



Thu, 08 Jul 2004 11:47:50 GMT  
 Alternate delimiters
If the process is going to be carried out once only, David's solution will
be fine.  However, if you're going to be doing this on a regular basis, it's
going to be a complete pain.  It could pay you to write some VBA code to
export the data for you.

If VBA isn't one of your skills, post a response in this newsgroup, and I
(or somebody else if they get to it first) will give you enough information
to be able to do the necessary.

HTH

Alan P.


Quote:
> K.S.,

> In your Excel file, you could change all the commas to | characters
> (with Edit / Replace), save the file comma-delimited, then use a text
> editor to change all the | characters back to commas in the saved
> file.

> If that won't work for you, please give a more detailed description of
> your Excel data, and I'll try to figure out something else.

> David




Quote:
> > I need to export a file from Excel and need to use delimiters other
> > than comma or tab.  I need to use a pipe (|) or some other keyboard
> > character that is not in the data (I do not own/control the actual
> > data).

> > This should be idiotically simple, but it is not.  The import of such
> > a file into Excel is idiotically simple but the export out is not.

> > How can I accomplish this??

> > I have tried to use a macro that I found on the Internet called "Text
> > Write Program-1" but could not get it to work for me.

> > I have also tried to use another macro that I found on the Internet at
> > http://www.cpearson.com/excel/imptext.htm#Export but it only exports
> > the first 4 rows (out of about 2,600).

> > Admittedly, I am a total novice regarding macros (took me all day to
> > figure out how to even successfully copy the above macro into my
> > spreadsheet).

> > Do I need to just switch to StarOffice or is there actually a way to
> > do something as simple as a delimited export from Excel?

> > Thanks for your help.



Thu, 08 Jul 2004 20:20:49 GMT  
 Alternate delimiters
Hi Seeker,

have a small vba function that reads in some csv file with pipes |

download the "read from external formatted text file" .bas file, which is
fully documented...

you can easily change the code to make it write your sheets.

if not, please let me know... I will publish an export to csv with
selectable delimiter...

Guido

--
HoloGuides VBA
We are proud to be of service.
http://www.geocities.com/allaboutVBA



Quote:
> I need to export a file from Excel and need to use delimiters other
> than comma or tab.  I need to use a pipe (|) or some other keyboard
> character that is not in the data (I do not own/control the actual
> data).

> This should be idiotically simple, but it is not.  The import of such
> a file into Excel is idiotically simple but the export out is not.

> How can I accomplish this??

> I have tried to use a macro that I found on the Internet called "Text
> Write Program-1" but could not get it to work for me.

> I have also tried to use another macro that I found on the Internet at
> http://www.cpearson.com/excel/imptext.htm#Export but it only exports
> the first 4 rows (out of about 2,600).

> Admittedly, I am a total novice regarding macros (took me all day to
> figure out how to even successfully copy the above macro into my
> spreadsheet).

> Do I need to just switch to StarOffice or is there actually a way to
> do something as simple as a delimited export from Excel?

> Thanks for your help.

> --
> Knowledge Seeker



Thu, 08 Jul 2004 22:00:50 GMT  
 Alternate delimiters
Thanks for the response.  I am going to have to be able to go back and
forth quite frequently.  However, I did use a variation on David's
suggestion to get me through the first iteration.

I am not a VBA programmer but do not mind giving it a shot.  I tried
to read the procedure that I found at
http://www.cpearson.com/excel/imptext.htm#Export and it seemed
straightforward.  However, like I said, it only exports a couple of
lines before stopping (may be something in my data but I do not see
anything "funny" in the data).  And I do not understand why it stops
short.

Any help that you can provide would be appreciated.

As a follow-up question, can you tell me if I get a procedure to work
is there a way to make that procedure global to all of my spreadsheets
(kinda like a library type function)??  Or do I have to
create/export-import the procedure in each individual spreadsheet
manually??

[Posted and emailed].

On the day of our Lord Sun, 20 Jan 2002 12:20:49 -0000 "Alan Perkins"

Quote:

>If the process is going to be carried out once only, David's solution will
>be fine.  However, if you're going to be doing this on a regular basis, it's
>going to be a complete pain.  It could pay you to write some VBA code to
>export the data for you.

>If VBA isn't one of your skills, post a response in this newsgroup, and I
>(or somebody else if they get to it first) will give you enough information
>to be able to do the necessary.

>HTH

>Alan P.



>> K.S.,

>> In your Excel file, you could change all the commas to | characters
>> (with Edit / Replace), save the file comma-delimited, then use a text
>> editor to change all the | characters back to commas in the saved
>> file.

>> If that won't work for you, please give a more detailed description of
>> your Excel data, and I'll try to figure out something else.

>> David



>> > I need to export a file from Excel and need to use delimiters other
>> > than comma or tab.  I need to use a pipe (|) or some other keyboard
>> > character that is not in the data (I do not own/control the actual
>> > data).

>> > This should be idiotically simple, but it is not.  The import of such
>> > a file into Excel is idiotically simple but the export out is not.

>> > How can I accomplish this??

>> > I have tried to use a macro that I found on the Internet called "Text
>> > Write Program-1" but could not get it to work for me.

>> > I have also tried to use another macro that I found on the Internet at
>> > http://www.cpearson.com/excel/imptext.htm#Export but it only exports
>> > the first 4 rows (out of about 2,600).

>> > Admittedly, I am a total novice regarding macros (took me all day to
>> > figure out how to even successfully copy the above macro into my
>> > spreadsheet).

>> > Do I need to just switch to StarOffice or is there actually a way to
>> > do something as simple as a delimited export from Excel?

>> > Thanks for your help.

--
Knowledge Seeker


Fri, 09 Jul 2004 01:43:18 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Alternate delimiters

2. Alternate Delimiters for text? (Access 97)

3. Alternate delimiters

4. mail merge Field delimiter,record delimiter prompt problem

5. Alternating colors for alternating rows

6. Importing quoted text with embedded comma delimiter

7. Opening Excel Document with Comma Delimiter with AppleScript

8. Using chars as delimiter when substringing

9. Position of the n-th delimiter (Split)

10. Split on the n-th delimiter


 
Powered by phpBB® Forum Software © phpBB Group