Can anyone suggest how I can convert an Xcel V.7 spreadsheed to a pipe-delimited file?
I'm hoping you won't tell me that the only way to do it is to save it as a csv file and replace all the commas with pipes - because I have a lot of important commas in my data too!

TIA
Alan

You can save it as a tab-delimited .txt file -- or at least you can in Excel97, which is what I have. Then you can either replace the tabs with pipes or use a tab as a delimiter in DBMan.

Or you can export the spreadsheet into Access and then export the Access database using any delimiter you want.


------------------
JPD

Hey,

rom my own experience, i've seen others convert the db to MS Access and then save with pipe line as delimiter.

What I do is save the excel file to .txt. I open in my fave web editor, and replace the TABS (they're saved with tabs as delimiter). You might need to delete the " that may surround your text.

(oh yeah, JP replied while i was replying )

------------------
Eoin
i.am/eoin


[This message has been edited by eoin (edited May 05, 2000).]

Thanks to JPDeni and Oein

When you save the file as tab delimited, how do you find and replace tabs? They appear to be invisible, i.e., not represented by anything you can replace. What am I missing?

Alternatively: how would you specify TAB as the delimiter in DBman?

Thanks.
Alan

If you use a programming-type text editor, like Programmer's File Editor or EditPlus, you can search and replace things like tabs. (I would suggest using one of those anyway.)

To use a tab delimiter in DBMan, set


in your .cfg file.


------------------
JPD

Carol,
Many thanks. Can you suggest which would be the best delimiter to use? I have about 2000 records each with about 8 fields (in case that has any bearing on the answer).

BTW - How do you replace tabs with pipes in EditPlus? I can see the tabs as gray '>>', but I don't see how to specify the tabs in search+replace.

Alan A

[This message has been edited by Alan33 (edited May 05, 2000).]

It doesn't really matter. The advantage of using a tab delimiter in DBMan is that you can export directly from Excel without having to make any changes to the file. The advantage of using a pipe is that most people use the pipe and, if you have any questions, it will be assumed that you do as well.

If I were going directly from Excel to DBMan, I would probably use the tab delimiter, just because it would save a step.


------------------
JPD

Thanks,
I also like the idea of tabs because it makes the data easier to read in a text editor.

I'm getting onto that shopping cart project at last...

Alan A

[This message has been edited by Alan33 (edited May 05, 2000).]

It's probably a bit late to be of much help to this thread, but in case anyone else was wondering... You can highlight the tab with the cursor/mouse or arrow keys (while holding down shift), copy (ctrl-c) it and paste (ctrl-v) it into the find field, then put a pipe or whatever you want into the replace field and that should do the trick.

Dan

Hi JPDeni,

I like your "saving one step" concept of using TAB as delimiters, but when I upload a TAB delimited file it seems that each TAB is replaced by three space characters. And the database looks funny in DBman.
I have tested with Fetch (from a Mac) and with Gossamer-Thread's FileMan from both Windows 95 and Mac and it looks the same. I am not 100% sure the TABs really are replaced by space characters, but when I make more -z (to display non-graphical characters) on the file no TAB character is visible.

How can I check the file and read the hex values of each character?

And if TABs turn out to be auto converted to three space characters: how do I get around this?

(I know, this is not really a DBman problem )

Going back to the original question. I do beleive this is possible to export | pipe | delimited from Excel. The problem is, I don't know how to edit the visual basic macros.

For example, here is a portion of the macro that imports from a pipe delimited file:


Here is a macro that exports as tab delimited:


Now, if we can only get someone who knows how to program it for pipes.

I really don't know what to tell you, o grain. This is way beyond my level of expertise.


JPD

Hey ho, Katana Man!

There are several ways if saving steps and it seemed wise to do it at the source as you suggested. I got in contact with a guy, Jim Rech, from an Excel newsgroup, who wrote me the following excellent macro.

I copied it to the VB editor and then run it and it actually creates a pipe delimeted file!!!

Jim Rech added the following comments:

This is a macro that directly writes out a text file using the VB file i/o methods. The macro writes out the selected range if more than one cell is selected when it is run. If only one cell is selected the macro writes out the entire active sheet. I have modified one line to use a pipe rather than the usual list separator character. With a large range this will be quite a bit slower than Excel itself is.


It is obviously the line ListSep = "|" that does the trick.

Yes! Kick butt! It works nicely. Thanks.