Display Commas In File

[NitzZ]

Hey crossrulz and others, 

I wanted to know if using the double quotes around the text which has commas to write into the csv file approach has any bad effects? checking just for my knowledge.. 🙂 

regards,
Nitz

(Give Kudos To Good Answers, Mark it as a Solution If your Problem was Solved )

[billko]

---

@NitzZ wrote:

Hey crossrulz and others, 

I wanted to know if using the double quotes around the text which has commas to write into the csv file approach has any bad effects? checking just for my knowledge.. 🙂 

regards,
Nitz

(Give Kudos To Good Answers, Mark it as a Solution If your Problem was Solved )

---

I can't really see a problem.  It just tells whatever is reading the delimited file to treat the stuff in between as a string.  Since the original intention was to output everything as a string anyway, I don't see an issue with that.  In fact, I'm not mistaken, the quotation marks thingy was just so you could avoid situations exactly like this.

[crossrulz]

---

NitzZ wrote:I wanted to know if using the double quotes around the text which has commas to write into the csv file approach has any bad effects? checking just for my knowledge.. 🙂

---

Just played with a dummy CSV file and Excel.  Putting the quotes around your string does cause it to ignore the delimiters that are inside of the quotes.  Tested with Excel 2010.  Other than having an extra 2 bytes per line in your file, I don't see any issues.

[Mitch_Peplow]

Morning all,

Lots of replies so thanks very much.

NitzZ your solution worked a treat. Out of interest where did you learn that feature? Is it embedded in the help files somewhere?

I went digging into the lower levels of the write to spreadsheet/read from spreadsheet function and it turns out that the way I do my File IO work is identical to the write to spreadsheet/read from spreadsheet functions, it’s just that it’s been wrapped up into one subVI.

I do have other code writing to different files which already uses the array to spreadsheet function but again they are comma separated, not tab so I do agree that potentially tab delimited can help alleviate this issue but that does lead me onto asking why use CSV at all as surely the tab command is a fundamental function that any spreadsheet program can accept?

Tabbing between each value would also be a good idea and may actually look better if separated so I may actually end up doing that. Also as pointed out I could just add a couple more column headers but the question was me simply asking if there was a simple way to escape the commas which has been proven to be possible.

Cheers all

Mitch

[Mitch_Peplow]

crossrulz,

Hopefully it is the lack of coffee in my system but I cannot seem to add a tab between each as suggested as when I add \t although the final string is formatted correctly, when it comes to write it ignores the tab request and generates one long string? Im assuming its because we are CSV formatted so any delimiter command other than ',' is ignored.

I tried '\\' to make sure it would print the literal characters \t which it does so how can a tab request be incorporated into a .csv formatted file?

That then led me onto another question of can you tab strings in a tab delimited file as we have now proven you can indeed have commas printed in CSV files, however the \t is a command so how would you make it tab the string and not move to the next cell? Hope that makes sense

[NitzZ]

Hey Mitch,

I don't think it's embedded in the labVIEW help files (atleast I haven't seen it there. It may even be there..:)). Usage of " " for such purposes is common among text based programmers and so I felt that it would work in your case as well.

Regarding writing \t into a tab delimited file, I have no clue how to do it.. 😞 I tried few things but didn't work. Even I'm looking forward to crossrulz and others to answer this question.. 🙂

regards,
Nitz

(Give Kudos To Good Answers, Mark it as a Solution If your Problem was Solved )

[PhillipBrooks]

There is a library on the LAVA forums that can be used to read and write quoted/escaped CSV files.

http://lavag.org/files/file/239-robust-csv/

[Mitch_Peplow]

Morning all,

Right, I was the originator of this question some time ago and whilst the initial issue was resolved, I've now ran into another one. I've attached a quick demo VI to illustrate the issue which I will now discuss. Before I start though, please refrain from simply saying ‘just use a different delimiter’ or ‘just use tab’ as I realise this would be the easiest method, however for this application the delimiter must stay as a comma and all this was discussed last time.

If you look at ‘Comma.vi’ you will see I have three string controls. All the text in each control is to be treated as a single whole string, thus when we come to write to file each control string is encased in the double “ “ quotation marks. All good, however this leads onto the first problem.

You will notice that I've had to include a VI named ‘Replace Quotes.vi’; this is because the quote symbol signifies that anything after it is to be treated as part of a continual string therefore if any string also contains quotes this changes how the file is written. This is easier to understand if you run the file with the replace quotes option on/off and compare the results files.

Now that’s annoying in its own right, but I can live with it. The next issue is something I cannot live with.

Although I can write to file to print the commas as per the original post, I encounter issues when reading back the file due to the fact that the ‘Spreadsheet String to Array’ function does not interpret the double quotes in the same way as the write does. This is best shown in the VI when you run it and look at all the rows read back.

The problem is that the function is purely looking for the delimiter; it has no idea that the quotes should signify a single string. It interprets each comma as just that.

Now I've tried to think of a few ways that the true string could be parsed out, alas to no avail which is where I'm hoping you guys can weave some magic. My thought is to parse out the string via the ‘Match Pattern’ or ‘Match Regular Expression’ function and build the array from this as I cannot think of, and indeed do not know if there is a format syntax available that would signify to the ‘Read From Spreadsheet File.vi’ or ‘Spreadsheet String to Array’ function (which is the base function used anyway) that the quote signifies the start of a whole string.

I'm no expert on string format syntax so there may be an easy fix for this but thus far I have not thought of a syntax format that will satisfy what I need to do. I pretty much always use backslash code displays and it seems that this may well help me when trying to match a pattern if it’s looking for “ “\n

Another idea I had was to somehow use the flatten to/from string functions as I have managed to do some nifty trickery with those before. The VIs from the LAVA website that PhillipBrooks posted a link to also shows a flatten function in the pallets picture. Indeed having never downloaded the library, I'm not sure if the VIs will accomplish what I'm after as they do state about escaping commas and quotes.

Any help or tips are more than welcome and like I say, if I had the option I would change the delimiter. That said it is slightly annoying that whatever delimiter you choose, you will run into this issue, apart from tab delimited as far as I can see.

Cheers for looking and thanks in advance

Mitch

[Mitch_Peplow · Accepted solution]

Morning all,

Right, we have a solution to my little predicament, two solutions in fact. Both came from NI so many thanks to James Voaden and James Willis. Both solutions are attached.

Both solutions work, however one is so simple that I wanted to punch myself for not thinking of it.

The .zip named 'NI Solution' contains the original solution NI sent me. It works but the parsing code is a bit of beast to get your head round if you have had a long day so tackle it first thing in the morning with a big cup of coffee!

The second solution named 'Comma Solution' quite simply uses " as the delimiter when reading back instead of using a comma. This means anything within the " " is treated as one complete string; the only thing is you still need to replace any "s within the string when writing otherwise the read back fails as before.

I also added in some array dimension trimming at the end as an extra row and column were being added to the data. This is because LabVIEW expects something after the last " of the first row but we actually start a new row, thus it appends an extra empty column. The extra row is because we have a final \n in the format string syntax. If necessary you could also add in an empty string check to ensure the row/column you are attempting to trim is indeed empty so not to accidentally lose any data.

Hope this helps some of you.

Cheers

Mitch

[billko]

---

@Mitch_Peplow wrote:

Morning all,

Right, we have a solution to my little predicament, two solutions in fact. Both came from NI so many thanks to James Voaden and James Willis. Both solutions are attached.

Both solutions work, however one is so simple that I wanted to punch myself for not thinking of it.

The .zip named 'NI Solution' contains the original solution NI sent me. It works but the parsing code is a bit of beast to get your head round if you have had a long day so tackle it first thing in the morning with a big cup of coffee!

The second solution named 'Comma Solution' quite simply uses " as the delimiter when reading back instead of using a comma. This means anything within the " " is treated as one complete string; the only thing is you still need to replace any "s within the string when writing otherwise the read back fails as before.

I also added in some array dimension trimming at the end as an extra row and column were being added to the data. This is because LabVIEW expects something after the last " of the first row but we actually start a new row, thus it appends an extra empty column. The extra row is because we have a final \n in the format string syntax. If necessary you could also add in an empty string check to ensure the row/column you are attempting to trim is indeed empty so not to accidentally lose any data.

Hope this helps some of you.

Cheers

Mitch

---

Don't forget to mark this your post as the solution so others can benefit from your experience.  🙂