# \[Perspective\] onFileReceived - CSV File UTF-8 vs ASCII

**URL:** <https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714>\
**Category:** Ignition\
**Tags:** ignition81, scripting, perspective\
**Created:** [March 28, 2022, 1:28pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714 "2022-03-28T13:28:49Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 28, 2022, 1:28pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/1 "2022-03-28T13:28:49Z")

</div>

Hello.  
I'm trying to import a csv file in a Perspective table using the _onFileReceived_ event of an **Upload Button**. This is the code I'm using:

```auto
import csv
from StringIO import StringIO
	
try:
	reader = csv.reader(StringIO(event.file.getString("UTF-8")), delimiter=";")
	header = reader.next()
	converted_data = [[column.replace(',', '.') for column in row] for row in reader]
	self.view.custom.table.data = system.dataset.toDataSet(header, converted_data)
except:
	import traceback
	logger = system.util.getLogger("import_recipe_parameters")
	logger.error("Error: %s" % traceback.format_exc())

```

Still, I'm having some issue with csv files saved with the CSV UTF-8 format by Excel, while the ones simply saved as CSV files are correctly read.

Using _chardec_, I can see that the file which is correctly imported is encoded like this:  
**ok\_file.csv: ascii with confidence 1.0**  
The one giving me issues is encoded like this:  
**nok\_file.csv: UTF-8-SIG with confidence 1.0**

Are there any ways to make my code compatible with both encoding formats?

```auto
reader = csv.reader(StringIO(event.file.getString("UTF-8-SIG")), delimiter=";")

```

doesn't seem to do the trick.

From Perspective [documentation](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+File+Upload):

> event.file.getString()  
> Fetches the incoming file data and attempts to parse it as a string via UTF-8 (Eight-bit UCS Transformation Format) encoding. Defaults to UTF-8 (super common), but can use other character sets. Passed as a string, for example getString("UTF\_16BE).

Thank you.

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [March 28, 2022, 3:42pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/2 "2022-03-28T15:42:29Z")

</div>

I took a look at this for you, and from what I can discern (in attempting to reproduce a similar file with Excel), Excel is producing a UTF-8 with BOM (byte order mark). Thankfully, you should be able to detect this and fairly easily remove it (below is your code above with modifications):

```auto
	import csv
	from StringIO import StringIO
	
	logger = system.util.getLogger("import_recipe_parameters")
	file_bytes = event.file.getBytes()
	
	# Check to see if this is UTF-8 with BOM
	if bytearray.fromhex("ef bb bf") == bytearray(file_bytes[0:3]):
		# Strip first three bytes
		file_bytes = file_bytes[3:]
		
	try:
		# Read in from file_bytes.tostring() now...
		reader = csv.reader(StringIO(file_bytes.tostring()), delimiter=";")
		header = reader.next()
		converted_data = [[column.replace(',', '.') for column in row] for row in reader]
		self.view.custom.table.data = system.dataset.toDataSet(header, converted_data)
	except:
		import traceback
		logger.error("Error: %s" % traceback.format_exc())

```

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 29, 2022, 6:11am UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/3 "2022-03-29T06:11:04Z")

</div>

Thank you, @kcollins! That did the trick.  
I didn’t know about UTF-8 files with BOM, but I’ll leave some references for other people to read.  
[StackOverflow](https://stackoverflow.com/questions/2223882/whats-the-difference-between-utf-8-and-utf-8-without-bom)  
[Wikipedia](https://en.wikipedia.org/wiki/Byte_order_mark)

---

<div class="post-metadata">

**Author:** ![Samg23](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Samg23](https://forum.inductiveautomation.com/u/Samg23)\
**Post date:** [June 30, 2022, 3:06pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/4 "2022-06-30T15:06:31Z")

</div>

this code works like a charm for me as well, but I don’t fully grasp how the formatting in this:

```auto
converted_data = [[column.replace(',', '.') for column in row] for row in reader]

```

section works. Is it possible to add a piece of code somewhere in there that checks if the column’s value is a float/decimal then rounds it if it is? just replacing column.replace with round(column) doesn’t work for me since some of the columns contain strings so I’m trying to add an if or try clause within it but just can’t get the formatting down if it even allows that.

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [June 30, 2022, 3:22pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/5 "2022-06-30T15:22:20Z")

</div>

Hello @Samg23.  
If I correctly understood what you’re trying to achieve, I think you can change that line of code with something like this (untested):

```auto
converted_data = [[round(column) for column in row if isinstance(column, float)] for row in reader]

```

---

<div class="post-metadata">

**Author:** ![Samg23](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Samg23](https://forum.inductiveautomation.com/u/Samg23)\
**Post date:** [June 30, 2022, 3:43pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/6 "2022-06-30T15:43:08Z")

</div>

Yes, something like that however I want to keep the strings as well, that code returns an error saying the header is mismatched with the converted\_data, I’m _assuming_ this is because it skips everything that isn’t a float, is there a way to add an else statement to that just returning the exact value of the column if it isn’t a float?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 30, 2022, 3:43pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/7 "2022-06-30T15:43:44Z")

</div>

Comprehensions are great, but they aren’t always the right tool for the job. ~~They don’t allow for branching logic.~~ In this case it is best to just use a traditional for loop.

```python
converted_data = []
for row in reader:
    converted_row = []
    for column in row:
        if isinstance(column,float):
            converted_row.append(round(column))
        else:
            converted_row.append(column.replace(',','.'))
    converted_data.append(converted_row)

```

Of course other conditions can be added.

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [June 30, 2022, 3:44pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/8 "2022-06-30T15:44:32Z")

</div>

You can add an _else clause_, like shown [here](https://stackoverflow.com/questions/4260280/if-else-in-a-list-comprehension).

---

<div class="post-metadata">

**Author:** ![Samg23](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Samg23](https://forum.inductiveautomation.com/u/Samg23)\
**Post date:** [June 30, 2022, 3:53pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/9 "2022-06-30T15:53:17Z")

</div>

> [@lrose](#):
>
> Comprehensions are great, but they aren’t always the right tool for the job.

I figured that, but I was always getting an error with mismatched headers when trying to reformat the code, I figured it just meant that format couldn't be used in that scenario, but yours works so it looks like that was down to my user error.

> [@Davide3i](#):
>
> You can add an _else clause_, like it’s shown [here](https://stackoverflow.com/questions/4260280/if-else-in-a-list-comprehension).

Thank you for that, worked like a charm.

Both solutions work but don't round which most likely just means the number I'm getting from the excel sheet isn't a float so I'll have to play around after lunch to find what the general formatting defaulted it to, still new to all of this haha.

Thank you both!

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [June 30, 2022, 6:47pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/10 "2022-06-30T18:47:53Z")

</div>

Try to check the output of _type(column)_. If it’s a string, it doesn’t make sense to use _isinstance()_, but you should cast your column as a float/int.

```auto
converted_data = [[round(float(column)) for column in row if **something** else **something else**] for row in reader]

```

---

<div class="post-metadata">

**Author:** ![Samg23](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Samg23](https://forum.inductiveautomation.com/u/Samg23)\
**Post date:** [June 30, 2022, 7:42pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/11 "2022-06-30T19:42:17Z")

</div>

I ended up going with the traditional for loop and changing the if/then to a try/except:

```auto
converted_data = []
for row in reader:
	converted_row = []
	for column in row:
		try:
			new = float(column)
			converted_row.append(round(new,2))
		except:
			converted_row.append(column.replace('.','.'))
	converted_data.append(converted_row)

```

I left the column.replace in doing nothing because I’m lazy 😉

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [June 30, 2022, 8:00pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/12 "2022-06-30T20:00:46Z")

</div>

Honestly, you should catch the specific fault, that way if a general fault occurs your code doesn’t fail.

To each their own. Glad you got it sorted.

---

<div class="post-metadata">

**Author:** ![Samg23](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Samg23](https://forum.inductiveautomation.com/u/Samg23)\
**Post date:** [June 30, 2022, 8:01pm UTC](https://forum.inductiveautomation.com/t/perspective-onfilereceived-csv-file-utf-8-vs-ascii/57714/13 "2022-06-30T20:01:55Z")

</div>

Good call, will do.