As I was working on a table extracted from a PDF file, I wanted to load that table in Excel. By default it would put all the fields in column A. Not good. So I put that in a text file, added a pipe at the end of each time and then join lines really quick (with vim, it's J to join lines.)

Now I had a nice .csv file, with fields separated by a pipe (|) character.

123|Something|More info|This "worked" before?!

Now, time to load that file in Excel. I tried that and it gave me everything cut in "strange" places but especially, no way to select the column separator. Why wouldn't I see the Wizard dialog!?

Reading posts around the net, most people would come up with some Visual Basic scripts (the scripting language used by Excel and other MS-Office suite tools.) Neat, except that I wasn't up to writing macros for such an easy task!

Finally, I found one comment saying that the file had to have the .txt extension to get the Text Import Wizard!!! So I changed the extension from .csv to .txt and this time it worked. I got the usual 3 panes wizard and could enter the pipe as the separator.

my-file.csv → my-file.txt

Open

select .txt, .csv, .prn

click Open

Wizard!