Why one csv reads in as a spresheet file but the other is all zeroes?

[PeterBrown]

As the title says. Both files seems similar to me, but the 500 file reads in fine as a %3f format but the other returns an all zero array.

[PeterBrown]

I realise to read the headers it'll need to be a string.. I only care for the numerical values however.

[GerdW]

Hi Peter,

---

@PeterBrown wrote:

As the title says. Both files seems similar to me, but the 500 file reads in fine as a %3f format but the other returns an all zero array.

---

The data in the "500" file are saved as numeric values.

The data in the "750" file are saved as strings as they are ALL encapsulated in quotation marks!

Possible solution:

1. Read the file as array of strings, remove the quotation marks for each string element, convert from string to numeric value.
2. Read the file as a string, remove all quotation marks, use SpreadsheetStringToArray to convert to an array of numeric values…

[pincpanter]

In the 750 csv, numbers are surrounded by double quotes. You can do one of the following:

- remove double quotes in the file itself

- use Read Delimited Spreadsheet (Double) using the format "%f: this will not work for the 500 file however

- if you don't know in advance how the csv is written, read the second line as a String or String array and check (you can use the same vi reading two rows), then proceed accordingly.

[johntrich1971]

Here's a snippet of one quick way. There may be a less Rube Goldberg method - I just didn't take the time to think it through.

[PeterBrown]

Thanks All. That makes sense. I'm not sure why our testing machine (Shimadzu) exported to CSV as numerical in the 500 file and then exported as strings (with the inverted commas) for the 750 file. There was no change in how it exported and Excel hid the inverted commas so this is a surprise. Maybe I should have opened them as text files before posting here..

As this is a once off I'll likely try reading as a spreadsheet with the %.f first and see how that goes seeing it's so simple. And just use a different vi on the numerical files.

Then if that fails I'll try reading as a string and removing the inverted commas. I've done things like that before so I know that isn't too hard.

I'll report back. Thanks, Peter.

[johntrich1971]

---

@PeterBrown wrote:

Thanks All. That makes sense. I'm not sure why our testing machine (Shimadzu) exported to CSV as numerical in the 500 file and then exported as strings (with the inverted commas) for the 750 file. There was no change in how it exported and Excel hid the inverted commas so this is a surprise. Maybe I should have opened them as text files before posting here..

---

What are "inverted commas"? Are you talking about the quotation marks?

In any event the example that I gave will work for both files. If there are quotation marks then it will remove them. If there are no quotation marks then it will move on.

[RTSLVU]

---

@johntrich1971 wrote:

---

@PeterBrown wrote:

Thanks All. That makes sense. I'm not sure why our testing machine (Shimadzu) exported to CSV as numerical in the 500 file and then exported as strings (with the inverted commas) for the 750 file. There was no change in how it exported and Excel hid the inverted commas so this is a surprise. Maybe I should have opened them as text files before posting here..

---

What are "inverted commas"? Are you talking about the quotation marks?

.

---

I had an English teacher in Grammar School that called Apostrophes "Flying Commas"... 😛 

[johntrich1971]

---

@RTSLVU wrote:

---

@johntrich1971 wrote:

---

@PeterBrown wrote:

Thanks All. That makes sense. I'm not sure why our testing machine (Shimadzu) exported to CSV as numerical in the 500 file and then exported as strings (with the inverted commas) for the 750 file. There was no change in how it exported and Excel hid the inverted commas so this is a surprise. Maybe I should have opened them as text files before posting here..

---

What are "inverted commas"? Are you talking about the quotation marks?

.

---

I had an English teacher in Grammar School that called Apostrophes "Flying Commas"... 😛 

---

🤣