I have a text file with fields seperated by the pipe character " | ". If i create a tab seperated file in Excel from this file, it stops at 65,000 records (there are over 100K records).

is there a way to import the text file directly into FM and designate the delimeter as the pipe character to create the fields so i dont have to deal with the Excel limitation?

Or any equally suitable alternatives? Thanks!!

afterthought: i guess i can replace the pipe character with a tab in MS Word, but ideally id like to avoid this extra step

Edited September 12, 200718 yr by Guest

You can always import the file into a single field and then parse out the values to the other fields with calculations or a script. Though I've never tried it, it might be possible that an auto-enter calculation could do the parsing as well.

There are 1,000,000+ rows in Excel 2007 should you ever decide.

James

www.james-mc.com

There are 1,000,000+ rows in Excel 2007 should you ever decide.

James

www.james-mc.com

I think that is the best answer. I am using Excel 2003, which seems to set a 65K row limit. Thanks, i didnt know they fixed this in 2007

I'm no expert on XML, but I believe that you could do this with the appropriate XSLT in an XML import.

No, I don't think so. You can Export to pipe-delimited using xml/xsl, but you can only Import XML from an actual xml source, not text; kind of the wrong direction for what he needs. Possibly you could use command line to "translate" the pipe to a tab? But on Windows I couldn't be sure of the exact syntax.