2d array to xml

[Prain]

Trying to write simple 2d array of integers to xml file using "Flatten to XML" and "Write to XML".

Can't understand, why does it make 1d array out of 2d?

i.e. for example array like this:

11	12	13
21	22	23
31	32	33

turns in xml file (opened by MS Excel) into something like this:

11
12
13
21
22
23
31
32
33

How to make it write each column to the right place?

[Cente90]

I think the "Array to spreadsheet string" function will fit your needs: you can convert your array to a single string keeping the rows and columns layout, then write it to the xml file and reload it when necessary.

[Bob_Schor]

Did you "experiment" with the XML Functions?  Did you notice that the XML produced by Flatten to XML looks like it should be parse-able to a 2D Array?  That suggests that either the writing-then-reading XML data to/from files is doing something unexpected or the whole thing is simply "broken".

I've not used NI's "native" XML functions, but I have used XML with LabVIEW quite successfully.  So I did what I hope you tried -- I made some 2D Data ( 3x4 array whose elements were 11, 12, 13, 14, then 21, 22, 23, 24, then 31, 32, 33, 34), Flattened to XML, did an XML Write, did an XML Read, and tried to get my array back.  I did, and they were identical.

On doing this, I discovered how NI's XML I/O functions work (they add two lines at the beginning and one at the end you need to trim if you want to recover the XML string that you originally passed to the Write to XML File).  As the previous response noted, you can use the Spreadsheet/Array functions to simplify processing multi-line strings (why NI hasn't provided a simple "Multi-line String to 1D Array" and "1D String Array to Multi-line String" function pair boggles the mind).

My code works nicely.  I'd post it, but you'll learn so much more about LabVIEW's XML functions by doing this experiment yourself -- if you try and get stuck, I (and other Forum readers) will help you out, but make the effort, first.

Bob Schor

[Prain]

Thanks for your reply. I tried it with different delimiters, but of no use: it works fine, while I'm in LabView and makes exactly what it meant to do, but I need the file to be opened in MS Excel. I mean that in MS Excel there should be several columns and several rows, each cell containing one data element. What I see now, using spreadshit, is that values of all rows are stuck together in one cell, so that there is only one cell for all values. Though rows in that cell are separated. It is like Excel doesn't want to understand 'tab' delimiters, 'new line' delimiters are ok for it, though it still puts all the values in one cell.

Found that it was an Excel options problem. Managed to make it right by setting the "text to columns" option there to read 'tab' delmiters. Now trying to make it read 'newline' delimiters correctly.

[Prain]

Thanks. Yoy are right: everything is fine while you are in LabView. It flattens and unflattens xml file correctly. But the  point is to make an XML file that could be opened in Excel. And there are problems with this, I described above.

Found that it was an Excel options problem. Managed to make it right by setting the "text to columns" option there to read 'tab' delmiters. Now trying to make it read 'newline' delimiters correctly.

[Bob_Schor · Accepted solution]

A couple of things:

• I discovered that I used the wrong "Read from XML" function, which gave me headers and a footer I needed to remove.  The Read From XML File function that you used was the right one, and it is a perfect "inverse" to the Write To XML File function preceding it.
• I removed the Sequence structure.  You should almost never need to use it!  If functions are connected by Wires (including, especially, the Error Line), they will automatically be sequenced!
• Instead of a Local Variable (generally bad), you can use an I32 2D Array Constant to define the input of the Read from XML File.  Here's my minor revision of your code:

Now, about Excel.  There are two types of Spreadsheet Files that LabVIEW can handle.  One is (properly) called an "Excel File", and has the extension .xls or .xlsx -- it is a file in the (proprietary) format defined by Microsoft Excel, allows coloring of cells, formatting, macros, etc.  The other is a Text file that has the following properties:

• The file consists of a multi-line string representing numeric or string array of data (that is, the data are either in the form "1  2  3" or "Red Green Blue").
• Each line of the string represents a row of the array.
• The strings are separated by a character, by default a <tab>, but also commonly a comma.
• Text files conforming to this convention are often called "Comma-Separated Values", or .csv files.
• Excel has "hijacked" the .csv Extension, giving them an "Excel-like" Icon, fooling people into calling them "Excel" files.

If you want to take your 2D array of numbers and write a .csv file, you can use LabVIEW's Delimited Spreadsheet functions (the name changed slightly around LabVIEW 2013-2014).  Excel can open such a file, though it won't have the "fancy" Excel properties of the .xls/.xlsx files.  For the latter files, you need to use the Report Generation Toolkit, now included in the Professional version of LabVIEW.

Bob Schor

[alexderjuengere · Accepted solution]

---

@Prain wrote:

Thanks. Yoy are right: everything is fine while you are in LabView. It flattens and unflattens xml file correctly. But the  point is to make an XML file that could be opened in Excel. And there are problems with this, I described above.

Found that it was an Excel options problem. Managed to make it right by setting the "text to columns" option there to read 'tab' delmiters. Now trying to make it read 'newline' delimiters correctly.

---

in a graphical nutshell:

[Prain]

Thanks for that example. Looks like it does exactly what I want to. Though I didn't manage to create the same thing.

First of all what type of delimiter is that? May be there is such designation in 2017labview, but I couldn't find anyhing similar in 2015.

Second question is about file extension. You use "test2.xml" filename for "Write Delimited Spreadshit.vi" function. When I try to open saved .xml file with Excel it throws an error. Then I rename it to .csv and Excel opens it correctly, but values are stuck together in each row - delimiter problem. So should it be exactly .xml extension?

[Prain]

Thanks for your advices.

Didn't know that using local variable consumes more memory than creating constant.

Now trying to use "Delimited Spreadsheet functions" and found some new difficulties, described in previous comment - about proper delimiter and file extension.

[Bob_Schor]

Extensions are used to suggest the format of the file.  One can always write a text file and give it the extension .exe -- it will still be a text file, but if you double-click it, the OS will say "Hey, I bet this is an executable, I'll interpret its bytes as a Program and ... <Crash>".  Similarly, you could rename "Excel.exe" to "Excel.txt", and when you double-click it, a program like NotePad will show you gibberish.

There is a form of file called a "Comma-Separated Variables", or .csv, file that is a text file of 2D data with columns separated by a character (commas, usually) and rows written as separate lines of text.  Write Delimited Spreadsheet writes text files in this format, so the file name should have the extension .csv (though only for the convenience of having the Operating System "think it knows what to do with the file when it is double-clicked").  Note that if you have Microsoft Office installed, Excel will "hijack" the .csv extension and associate Excel as the program to open it when double-clicked, giving the file an "Excel-lookalike Icon".  So while naming a Delimited Spreadsheet file Test2.xml isn't, per se, wrong, it is misleading and confused you.

By default, Write Delimited Spreadsheet uses a <Tab> character, shown as \t, as the separator.  One of the inputs to this function is the delimiter, which (to make it truly "Comma-separated") you can change to ",".

I hope this answers your questions.  You could probably have figured at least some of this out by reading the LabVIEW Help for this function.

Bob Schor