save null character to excel

[GregFreeman]

Because MS Excel cells expect null terminated strings, I'm losing data when writing to Excel using active X from LabVIEW. What I am doing is typecasting an array to a string, and storing that string in a single Excel cell. But, if that string has a null character, all data after it is lost. Has anyone overcome this, and if so, how? The reason I want to do this is because my Excel spreadsheet is formatted in the following way

run1, set1, graph1 array, graph 2 array...graph10array

run 1, set 2, graph1 array, graph 2 array...graph10array

...

run2, set 1, graph1 array, graph 2 array...graph10array

....

run n, set n,graph1 array, graph 2 array...graph10array 

This allows me to search for "run n, set n" and get all the corresponding graph data very easily. Also, unfortunately, using a database is not exactly an option :(.

[Matt_W1]

Here's some ideas

Option1: Don't use excel use tdms instead, combine run and set into the group names, and put the graph arrays into channels, this should be much faster, and easier to deal with

Option2: Is there a reason a self contained database wouldn't work. Like the SQLite API for LabVIEW

Option3: Reencode your strings so they don't contain \0, then unencode them when you read them.

Option4: Change your format (each sample from the graphs goes into it's on cell)

[GregFreeman]

Matt,

Thanks for your response. The first two are customer driven. Their old format was in a DB and they want to keep that format, but move to excel so they can access other data visually from within Excel. If I was to put the graph stuff in a separate file, i'd then have to map to other files, check to make sure the files are there, etc.

Encoding and reencoding strings crossed my mind. However, this could potentionally cause "unencoding" of something I don't want to be unencoded. For example, let's say I replace all null characters with a, but then there is an a in the typecasted string which I don't want replaced. It will be replaced anyways. Is there a way around this? I'm thinking maybe do something like replace a null character with the ascii characters NULL. Then use a regex to search and replace. 

The last one would work and I may just map the data to columns on a different sheet and write all the points.

---

@matt W wrote:

Here's some ideas

Option1: Don't use excel use tdms instead, combine run and set into the group names, and put the graph arrays into channels, this should be much faster, and easier to deal with 

Option2: Is there a reason a self contained database wouldn't work. Like the SQLite API for LabVIEW

Option3: Reencode your strings so they don't contain \0, then unencode them when you read them.

Option4: Change your format (each sample from the graphs goes into it's on cell)

---

[Darin.K]

---

@for(imstuck) wrote:

Encoding and reencoding strings crossed my mind. However, this could potentionally cause "unencoding" of something I don't want to be unencoded. For example, let's say I replace all null characters with a, but then there is an a in the typecasted string which I don't want replaced. It will be replaced anyways. Is there a way around this? I'm thinking maybe do something like replace a null character with the ascii characters NULL. Then use a regex to search and replace. 

---

Encoding/Decoding is a bit more sophisticated than simply replacing characters since you point out an obvious failure.  I'd suggest the Escape/Unescape HTTP URL.vi.  These use a standardized method and should do the job you require.

[Matt_W1 · Accepted solution]

Could you just add an excel exporter, and handle the data internally in your own way.

I thought I had added a link for the encoding bit

http://en.wikipedia.org/wiki/Binary-to-text_encoding

Hex or Base64 encodings should be fine (although large).

Off the top of my head (so maybe broken) a possible encoding.

Replace all instances of '=' with '=0' then all instances of '\0' with '=1', then just reverse the replacements in either order to unencode, other bad characters can be replaced with a different '=(a different single letter that is not '=')'

[BertMcMahan]

This is a very old thread, but I found something today I wanted to mention for future Googler's:

---

@Matt_W1 wrote:

Option1: Don't use excel use tdms instead, combine run and set into the group names, and put the graph arrays into channels, this should be much faster, and easier to deal with

---

It appears that tdms cannot handle null characters, and drops them like Excel does. I dunno why you'd need to have nulls in your strings intentionally, but I can say that TDMS doesn't like em!

(Everyone please note the age of the post before replying to the OP 😁)

[Hooovahh]

---

@BertMcMahan wrote: It appears that tdms cannot handle null characters, and drops them like Excel does. I dunno why you'd need to have nulls in your strings intentionally, but I can say that TDMS doesn't like em!

Well if you want to flatten some data to an array of bytes you might be tempted to store it as a string.  In which case you will run into this null issue.  I have a VIM that reads and writes arrays of clusters to TDMS and had this issue and needed to add some escaping code.

https://forums.ni.com/t5/Example-Code/Read-and-Write-array-of-Cluster-to-TDMS-Get-Set-Cluster-and-En...

Additionally I found that some characters do weird things with Linux fonts, and may replace characters with "?" so I needed more work to escape and unescape those too.

[BertMcMahan]

---

@Hooovahh wrote:

---

@BertMcMahan wrote: It appears that tdms cannot handle null characters, and drops them like Excel does. I dunno why you'd need to have nulls in your strings intentionally, but I can say that TDMS doesn't like em!

Well if you want to flatten some data to an array of bytes you might be tempted to store it as a string.  In which case you will run into this null issue.  I have a VIM that reads and writes arrays of clusters to TDMS and had this issue and needed to add some escaping code.

https://forums.ni.com/t5/Example-Code/Read-and-Write-array-of-Cluster-to-TDMS-Get-Set-Cluster-and-En...

Additionally I found that some characters do weird things with Linux fonts, and may replace characters with "?" so I needed more work to escape and unescape those too.

---

I do use your TDMS functions already, they are great and I appreciate you sharing them 🙂

Figuring this one out was quite confusing, as the TDMS file format internal structure document  says strings are stored as "Pascal strings" the same way LabVIEW does it (with a prepended length- not as null-terminated). Unfortunately, it still seems that something, somewhere in the TDMS code treats a null as the end of a string, despite claiming to store it another way. My (quite rudimentary testing) shows that it's definitely happening on the Export. I don't know if Importing would accept a null character if you were able to get one in there somehow.