read spreadsheet with ,ultiple possible delimiters

[WCR]

How do I use Read From Spreadsheet File to read in text files with different possible delimiters?  Some use tabs, some use one space, some use a variable number of spaces, some use commas.  (I do not know how many items there will be on each line.  altenbach's 2006 approach expects a specific number of items on each line.)  Thank you.

[10Degree]

WCR,

Can you provide more specifics on your problem?  You can wire a delimiter string to the Read From Spreadsheet File which is a tab character by default.  Can you just do this or is the problem that you do not know what the delimiter is within the file, or that a variety of delimiters can appear on any given line?  More specifics would help. I am unfamiliar with altenbach's earlier post.  A sample file would help too if it is a more complex parse operation.

cheers,

-cb

[WCR]

Thank you for your inquiry.  The text data which I want to read in and analyze has 2 or more columns of numbers and many rows.  Sometimes the numbers on a row are tab-delimited, sometimes they are separated by one or more spaces.  I don't know what the delimiters will be in a particular file until I open it and look at it.  I want to read in the numbers on a line, separated by tabs and/or one or more spaces.  If I wire a space to the Read Spreadsheet function, it doesn't recognize tabs as delimiters, and vice versa.  Tried wiring an array of springs and that didn't work either.  Altenbach (http://forums.ni.com/ni/board/message?board.id=170&message.id=192847#M192847) suggested a nice solution which has the disadvantage that it is hardwired to expect a constant number of elements per line, which I do not want to assume.

Bill

[johnsold · Accepted solution]

WCR,

Read one line of the file with low level file functions.  Search it for tabs.  If any are found, read the file with Read Spreadsheet with a tab delimiter.  If no tabs are found in the first line, read use "space" as the delimiter with the Read Spreadsheet function.  If other possible delimiters exist, a more complicated search is required, but the same principle can be extended.

Lynn 

[WCR]

Thank you.

[altenbach]

---

WCR wrote:
Altenbach's 2006 approach expects a specific number of items on each line.).

---

Well, It would be a trivial modification to allow for an unknown number of items/line. All you need to do is replace the inner FOR loop with a while loop and terminate it whenever you reach the end of the line string. Very similar to the outer while loop.

Think of it: My code does NOT know how many lines there will be, but still manages things fine. 🙂

(Most likely, you can assume that all lines contain the same number of items, else a 2D array would be inapproriate anyway. So for better memory management you could analyze the first line and then use my code with the now known number of columns.)

Can you attach a typical datafile?

[altenbach]

---

johnsold wrote:

If no tabs are found in the first line, read use "space" as the delimiter with the Read Spreadsheet function. 

---

This wil not work for the case of multiple spaces as delimiters (as defined in the initial problem description). You'll get extra elements of zero.

The advantage of "Scan strings for tokens" is the fact that multiple consecutive delimiters are treated as one, so "three spaces", or a "comma followed by two spaces" or a "space and a tab" will still give the correct result.

Many times we have a tab delimited file and a user will edit it in notepad and might accidentally add a few spaces instead of a tab so things "look right" to the naked eye. This will however blow up when using "spreadsheet string to array". 😮

[altenbach]

OK, here's a quick edit to allow for an unknown number of items per line.

(It assumes that the line ends in an item and not in a delimiter. It also assumes that there are no extra delimiters at the end.

If these assumptions are incorrect, we will get an extra column of zero and/or an extra row of zeroes at the end. If this is a possibility a tiny little more code is needed to deal with it.)

If the file is gigantic, it might be worth to do a dry run to count the number of rows and columns (inner loop needs to run only once) and then repeat with two FOR loops. This allows for a single allocation of the entire output array and eliminates the need to reallocate memory as the array grows. Autoindexing on while loops is relatively expensive. 🙂

Message Edited by altenbach on 11-07-2008 09:17 AM

[cevert73]

Hi,

I've used the attached code in an attempt to deal with a 'dirty' spreadsheet.  This spreadsheet (example.txt) is an output file from a Fortran code which resolves real and imaginary components written within brackets and separated by a comma.

They are mapped to a mesh which is dimensioned as 100x40 respectively.  How do I tidy this up so that the array is wrapped accordingly first time around?

This is a case where occasionally the line ends with a delimiter, which messes things up.

Theoretically I could re-write the Fortran code but I'd rather do it at the LabVIEW end.  Any suggestions?

Thanks.

[altenbach]

I would first remove all spaces and linefeeds, read it as a 1D complex array and reshape it later to a 2D array.

Here's a quick draft.