Reading spreadsheet file with varying number of delimiters

[massen]

Dear Forum,

I wish to read in a spreadsheet file (text) generated by some 3rd party programme. It turns out that while the programme generates columns separated by spaces, the number of spaces between columns varies in a fashion I have yet to disentangle. This is not a problem if I wish

to read in the file in excel (or equivalent) - but LabView's "spreadsheet string to array" functions doesn't seem to be able to cope with it - if I enter "space" as the delimiter it accepts only a *single* space... is there some way to indicate "zero or more" spaces to this function ? [I know I can make a bunch of loops and read the items one number at a time, but this is very slow, so I'd prefer to avoid it]

Thanks,

Niels

[Omar_II]

You can do a search and replace. Search for any number of spaces and replace them with just one and then use the Spreadsheet String To Array function.

Use the + to search for one or more.

Be sure to set the Search and Replace setting for "Regular Expression" Right click options.

But that might not always work if there are columns that are empty.

Post an example of the text file that you are working with from the 3rd party software. 

+	Marks part of a pattern as one that can appear one or more times in the input. For example, be+ matches be in believe, bee in beep, and fails (no match) with bat.

[massen]

Hi Omar,

Thanks. Nice idea - just tried it... but it's actually slower than just reading the values one at a time with a double for-loop... [it's a big file]

Other suggestions are most welcome.

Cheers,

Niels

[johnsold]

Niels,

It is possible that the file uses a fixed width format. In that kind of format extra spaces are used so that each field occupies the same number of characters. If it is a fixed width format you can use that information to divide the string into equal length segments whihc each represent one field.

Another possible way to deal with such a file is to use the string search functions to replace all multiple instances of space characters with single instances and then convert to array.

Lynn

[Omar_II]

---

@massen wrote:

Hi Omar,

Other suggestions are most welcome.

---

Without seeing an example of the file I would just be guessing.

Can you post a small subset of the text file?

If the file was not created with something to delimit the values, then you are going to have to do something yourself like loop through each line and try to figure it out.

[0utlaw · Accepted solution]

Hello massen,

The spreadsheet string to array can actually accept an array of delimiters, so you can wire in a 1D array containing any delimiters you want to watch for - one, two, or three spaces, etc.

To do this, just create a 1D string array containing the delimiters and wire it into the delimiter input at the top of the primitive.  Like most primitives, the function is polymorphic.

If the program that generates this file doesn't limit the spaces it inserts in some way or uses spaces elsewhere within the data, you might run into problems, but for most cases I think this should suffice.

Best Regards,

[johnsold]

Tom L.,

I did not know about the array of delimiters.  While I cannot think of a good use for it, I am glad to know that it is possible.

If you have an array of delimiters, will it recognize any element as a delimiter anywhere in the string, or does in match the order in the array?

The detailed help for that function says, "delimiter is the value or values used to separate fields..." but it does not clearly indicate that an array will work. Please pass along to the documentation people that a clarification would be nice.

Thanks,

Lynn

[altenbach]

For a more detailed discussion and alternative code if consecutive delimiters should be considered a single delimiter, have a look at my idea here.

Contract multiple delimiters for "Spreadsheet string to array"

Please vote for it too!

[0utlaw]

Hi Lynn,

Per your question - I believe that the most complete match in the delimiter array will be used- eg if you provide an array containing one/two/three spaces, in that order, any breaks of one, two, or three spaces would be interpreted as a single delimiter, not a series of single-space delimiters.  If four spaces were present in the file it would be interpreted as two delimiters.

Regards,

[massen]

Hi Tom,

Thanks for that - that did the job - and it's fast.

Cheers,

Niels