CustomHow can I create pipe "|" delimited .csv ?

[lokihsu]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello,

For a specific project, I need to send speadsheets with pipe delimited .csv formatted files to my client who is windows based.

For them to customize their .csv from comma delimited to pipe delimited, they go into their Regional settings on their Window's Control Panel. I can't find this option in Mac's System Preferences not Excel for Mac 2008's Save As settings...

Apologies for the long explanation but hopefully the more information the better for you guys to help.

 

[CyberTaz]

Not a subject on which I'm any kind of authority, but it isn't surprising
that the option isn't readily available. After all, CSV does stand for Comma
Separated Values, so what *is* surprising is that Windows so easily permits
what amounts to a perversion of the standard... Well, maybe that's not so
surprising after all

The only suggestion I can offer is to open the CSV in a text editor that
provides a Find & Replace feature in order to swap the | for the , - I
haven't tested it thoroughly but theoretically that should work. I did so
with Word & the only problem is that - although Excel will reopen the file
it doesn't respect the pipe as a delimiter, so each record/row of data is
inserted into a single cell.

Unfortunately an extra step, but that's the best I can suggest.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Carl Witthoft]

I'm not sure I quite understand BobJ's final comment. If you open a
text file from the File/Open menu, you should get a dialog asking
whether the file has delimiters, and then you can specifically tell
Excel to use "|" as the delimiter.
Agreed that this should not be called a 'csv' file because that is
supposed to be purely (hah) for comma-delimited stuff. I forget what
Excel does in European languages which use "," for the decimal point.

 

[CyberTaz]

Hi Carl -

Once you open the file in Word & save your changes it retains the .csv
extension but it's no longer in a .csv format - it's saved as a MS Word Text
Document. Excel opens it directly - bypassing the import options. You need
to use the Data> Get External Data> Import Text File command in order to
activate the Import Wizard & make the spec you're referring to. Once you do
that, however, the pipe delimiter has been discarded.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Jim Gordon MVP]

Hi Carl -

Once you open the file in Word & save your changes it retains the .csv
extension but it's no longer in a .csv format - it's saved as a MS Word Text
Document. Excel opens it directly - bypassing the import options. You need
to use the Data> Get External Data> Import Text File command in order to
activate the Import Wizard & make the spec you're referring to. Once you do
that, however, the pipe delimiter has been discarded.

Regards |:>)
Bob Jones
[MVP] Office:Mac

For me it also works if I use File > Open and change the type of file to
Text.

=Jim

 

[CyberTaz]

Hi Jim -

Yeah, I guess it would although I didn't try that - the OP specified that he
has to submit a CSV. If a creating a .txt using the pipe delimiter were the
issue there wouldn't be an issue The real problem is that he's being
asked to submit a file format whose content is modified to violate the
standard of that very format.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[lokihsu]

thanks all! I was able to figure out how to create what i needed.

First I saved the spreadsheet as a .csv
Then I opened the csv in Word and used the Edit->Replace function to change all "," into "|"

I saved that as a csv and then opened a new Excel spreadsheet and used Data-> Get External Data->Import Text File and opened the .csv I just made in Word

In Step 2 of the Text Import Wizard, I was able to change the delimiters to in the Other checkbox "|"

Thanks all for the help.

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello,

For a specific project, I need to send speadsheets with pipe delimited .csv
formatted files to my client who is windows based.

For them to customize their .csv from comma delimited to pipe delimited, they
go into their Regional settings on their Window's Control Panel. I can't find
this option in Mac's System Preferences not Excel for Mac 2008's Save As
settings...

You could save by running this script:

set tempfile to "~/temp.csv"

tell application "Microsoft Excel"
set myFileName to (get save as filename)
tell active sheet
save in myFileName as CSV file format
end tell
end tell
set myFileName to (POSIX path of myFileName)
set myReplaceScript to "sed -e 's/,/|/g' " & myFileName & " > " & ¬
tempfile & " && mv " & tempfile & " " & myFileName
do shell script myReplaceScript