Problem with 'Watch a folder for new .csv files'

AndreasEspersen · ‎07-01-2014

Hi all,

I have a problem with getting data from a CSV file processed by GEP that is beginning to drive me mad.....!

I'm using the input Watch a folder for new .csv files but I haven't had any luck getting the GEP to output it correctly. Either it is only showing the first line or else it shows nothing when outputting to Publish text to a TCP Socket and gives various errors in the log that haven't guided me in the right direction.

The input to GEP is made via model builder and runs every 5 minutes and an example of the file can be seen attached (The file format was changed to txt so the file could be uploaded.)

My Geoevent Definitions and input adaptor can be seen in the attached PNGs.

When changing the GeoEvent Definition to 'incident', which comes with the installation and using the csv files (Flights/Trucks) from the tutorial things are working.

I think the problem is related to building the geometry but i havent found a solution for it.

Any ideas on what is going wrong would be greatly appreciated.

Regards

Andy

Anonymous User · ‎07-02-2014

Original User: RJ Sunderman

Hello Andreas -

I've a few thoughts on what you might be experiencing.

First, I would remove the line specifying the attribute field names from the CSV file you are writing into the folder the 'Watch a folder for new .csv files' inbound connector is watching. Given a specified GeoEvent Definition, I observed the 10.2.2 product placing <null> values into GeoEvent fields when the inbound connector's adapter could not successfully parse a string value from the system text file to create the specified type. The field name "XCoord" for example cannot be converted to a value of type 'Double'. It might be that GeoEvent Processor can handle the header row - but it would still be better to include only the actual event data in the file and leave out the attribute field names. Also, I would be very careful to make sure that the event attributes being received as delimited values are received in the order specified by the GeoEvent Definition. You have at least a couple dozen attribute fields in your input, and it's easy to introduce an off-by-one error that will result in the received input not matching the event definition and the adapter not being able to parse the data.

Second, and this has caught me before, make sure that your system file contains an empty line at the end of the input file. Since you only have one line of actual data, if there is not an actual newline (\n) terminating the line of delimited data values, the GeoEvent Processor's adapter will buffer input every time a file is read until it finally finds a newline. A good way to avoid this is to always use input files with at least a couple of lines of data ... then you only end up wondering why the *last* line in the file isn't being processed.

Third, it took me a couple of minutes to recognize that the numeric values in your sample text file were expressed using the international convention with a ',' rather than a '.' in the decimal values. Your GeoEvent Definition specifies that the first six data values should be received as Double, Double, Integer, Double, Short, Date:

[ATTACH=CONFIG]35053[/ATTACH]
Click to Enlarge

When told to expect a Double value, the Text adapter out-of-the-box actually replaces ',' with '.' and brings in the value 11,19770167 as a numeric double: 11.19770167 ... I was a bit surprised by this actually.

Looking at the first six values from your sample, I can construct a GeoEvent Definition which successfully brings in these values.

11,19770167;56,58325000;219016873;20,000000;1;2014-07-01 10:44:44

Finally, if you want the the out-of-the-box Text adapter to create a Geometry from coordinate values received with the event ... unless you specify a WKID (Well Known Identifier) for the projected coordinate system associated with the coordinate values ... the coordinates must be expressed in decimal degrees.

Since you're GeoEvent Defintion is bringing the values in as Double, I'm going to assume that the value 11.19770167 is 'Latitude' and the value 56.58325000 is 'Longitude' ... so you don't need to specify a WKID, but you do need an "extra" field in your GeoEvent Definition of type Geometry into which the Text adapter can place the Geometry is creates. So your event definition will have one (1) more field than the number of attributes in your delimited list.

Keeping with the simple input example in the code block above, if I add an event field 'Location' to my GeoEvent Definition, I tried to have the input create a Geometry for me using the first two event attributes as the coordinate values ... and this is where I ran into trouble.

[ATTACH=CONFIG]35054[/ATTACH]
Click to Enlarge

[ATTACH=CONFIG]35055[/ATTACH]
Click to Enlarge

Evidently the adapter, told to expect a Double, is willing to take a value 11,19770167 and convert it to a numeric value 11.19770167 ... but it is unwilling to take the same value and use it to create a Geometry. I will follow-up with an example of how you can use a Field Calculator processor to work around this limitation.

Hope this information helps -
RJ

AndreasEspersen · ‎07-03-2014

Hi RJ,

Thanks for the useful information.

The problem was the international comma as you mentioned in the end of your post.

When changing my comma settings in the CSV file to '.' instead of '.' 4 out 21 records was imported. I have found a workaround using python to replace all ',' with '.' but it is still not the ideal solution for us and it looks like a bug.

I will investigate why I only get 4 of my records out.

Thanks again and we might see each other at UC.

Regards

Andy

GregCarr-Hyde · ‎08-26-2015

I am developing a live feed vehicle tracking web map that receives and consumes CSV file data from GPS feeds on vehicles. A dummy file of the live feed is provided in this email and the feed is polled or queried via a URL that downloads the latest instance of the current coordinates for the vehicles.

The process that I’ve tried to follow is similar to the one shown in the GeoEvent Extention Introduction tutorials (attached is the tutorial that shows how to create a ‘live’ map).

I can make the tutorial data (flights) update using the tcp-in and tcp-out connectors and the GeoEvent simulator but if I try the same process as the flights geoevent but use the Location.csv data for Sappi vehicles it does not update.

I’ve also tried the Watch folder for csv update (my preferred method for this task) import connector and created a geoevent service that then connects to a fs-out to update the point feature layer that matches the input csv data. This also did not work.

Some aspects that I’ve trouble shot so far are:

Published a map to the webserver with a blank point file that has the same schema as the csv input
Tested the csv sending via TCP bat file as the tutorial shows. No data is sent
Placed the csv file into the “Watch folder for csv updates” folder at various stages of the geoevent running, stopped and started.
I was able to substitute the x,y coordinates into the flights csv file and use the tutorial process to simulate stepping through the data

The original csv file that downloads has quotation marks at the beginning and end of each field as well as commas to seperate the fields. I've tested without the quotation marks and still have the same result. No data can be seen transferring in the monitor page of the GeoEvent manager.

EvanPerry · ‎08-27-2015

Hi Greg

I've taken a quick look at your Original-Locations and have that successfully running, as is with the quotes, using the watch folder for CSV and just going out to another CSV file again.

A couple of possible things spring to mind.

As you have the coordinates coming in in 2 separate columns you need to add in an extra column into the input Event Definition for the geometries to be assembled into and have the "Build Geometry From Fields" switch to yes and populate the x and y field in the Input Service

You’ll also need to specify the Expected Date Format for your data as it’s not what the GEP expects as the default. Not that that’s stopping the in/out process but you get incorrect data the output field otherwise.

You need to drop a uniquely named file into the watched for each time you. GEP won't process a file of the same name if it’s been processed before even if it has different content.

Some good info in this thread regarding the CSV file inputs https://community.esri.com/thread/89695. Note in that thread about GEP expecting a two-line header in the file otherwise you'll be dropping one or two rows from each CSV

I’ve (hopefully) attached screen shots of how I set the input Event Definition and the Input Service. I’ve assumed the first column in the file is the identifier for the flight so have tagged that as the TRACK_ID

Once you get the CSV file out running and your happy with content in the output file then switch out the csv output in the GeoEvent Servicefor the feature service output, making sure the Event Definition data types from the feature service layer correspond to the equivalent data types from the GEP. You’ll need a Field Mapper Processor to point the GEP Geometry field to the Shape field in the feature service layer and the other fields also. Then you should be up and running.

Hope this helps

Cheers Evan

GregCarr-Hyde · ‎08-28-2015

Hi Evan,

Thanks for your help.

I have applied the suggestions from https://community.esri.com/thread/89695. and created geoevent definitions acordingly before as well.

From what I can tell it is not even reading the csv file being written to the watch folder. I've now contacted esri support for assistance and have had feedback that the file ran normally on their side so all I can think is it may be a server setting somewhere. I've checked permissions on the user, the services and the geoevent side and all have full admin rights so I'm really not sure why the geoevent manager wont even read that a csv file has been placed in the correct folder.

I'll reply with feedback should I find what the problem was.

Thanks

Greg

EvanPerry · ‎08-28-2015

??Hi Greg,

No problem for the help, I know how frustrating it gets when things don't want to work when they should, sorry it didn't solve the issue

From your email it looks like you've pretty much covered all the options. Only thing on the permissions is that it's the account that the GEP was installed under that needs full permissions on the input file.

I did my test under my own user login which doesn't have admin rights on the server and that server is still at 10.2.1

It does sound a bit odd so will be interesting to see what ESRI support come back with

Cheers Evan

Evan Perry

Manager Spatial Solutions

d +64 4 590 6847

m +64 21 650 171

p +64 4 590 6800

f +64 4 590 6801

e evan.perry@ems.co.nz<mailto:evan.perry@ems.co.nz><mailto:evan.perry@ems.co.nz>

e evan.perry@transpower.co.nz<mailto:evan.perry@ems.co.nz>

energy market services

a division of Transpower NZ Limited

Ground Floor | Transpower House | 96 The Terrace | Wellington 6011 | New Zealand

PO Box 5363 | Lambton Quay | Wellington 6145 | New Zealand

Notice: This e-mail and any files transmitted with it are confidential and are for the sole use of the intended recipients. If you have received this e-mail and any attachment in error please immediately notify energy market services by "Reply" and delete it. Any review, dissemination, disclosure, alteration, printing, copying or transmission of it is prohibited and may be unlawful. Note also that its content could have been altered already without the author's consent.

Any views expressed are those of the individual sender, except where specifically stated that they are the views of energy market services.

GregCarr-Hyde · ‎08-28-2015

Hi Evan,

Ok so I do have some feedback after meeting with the ESRI support person. It seems that for some reason the default directory that I used was the problem.

Apparently creating a new directory (we made a directory on the C drive directly) and pointing the input and output services to the new directory worked. I wonder if having a longer path to the folder had something to do with my problem. Also I noticed that he suggested also removing the input directory option in the input settings seems to have helped in my case too.

Thank you very much for your help

Greg

EvanPerry · ‎08-30-2015

Hi Greg

That's an interesting one, glad they have it sorted for you

All the best

Evan

RJSunderman · ‎08-31-2015

Hello Evan / Greg -

I would remove the line specifying the attribute field names from the CSV file you are writing into the folder the 'Watch a folder for new .csv files' inbound connector is watching ... better to include only the actual event data in the file and leave out the attribute field names.

In a reply to Greg, Evan cross-references an older thread, watching a folder with csv files, in which I state: "the first two lines of the CSV file are reserved for a comma separated list of field name and a comma separated list of field data types - your actual event data needs to start with the 3rd line of the text file." But then in this thread I suggest the opposite, to remove specification of attribute field names from the CSV file.

So which is it, you're asking. Should a CSV file include a two-line header with field names and types, or shouldn't it?

My apologies for the confusion. In the older thread I must have been thinking of when a CSV file is used as the source of event enrichment, not when the CSV file is used as the source of the event data.

An enrichment file would need the header rows to specify field names and field types.
You would not want a CSV input file to include anything more than the (comma) delimited data values.

Evan is correct, above, when he suggests that the OS level user responsible for running the GeoEvent Windows service is the account which needs permission to access a registered folder. I don't remember if it was at 10.2.2 or a later release that we added visual indication of the apparent permissions on a registered folder to the Site > GeoEvent > Data Stores page in Manager. That particular enhancement might help clear up potential issues with whether or not an input connector will be able to retrieve data from a registered folder.

Also, Greg points out that when registering the system folder, the user-interface's default is to place a value 'input' in the 'Input Directory' parameter. I always remove this value and leave the 'Input Directory' parameter unspecified. The default would only work if you wanted your 'Watch a Folder for New CSV Files' input to look for a sub-directory named input beneath a registered system path C:\GeoEvent\csv (for example).

When working with file-based input and output (and enrichment) I usually register separate folders:

GeoEvent_Input -- C:\GeoEvent\input
GeoEvent_Output -- C:\GeoEvent\output
GeoEvent_Enrich -- C:\GeoEvent\enrichment

I don't configure my 'Watch a Folder for New CSV Files' to expect an 'input' sub-directory, and I don't allow it to 'Include Subfolders'.

Finally, please keep in mind that the GeoEvent Extension generally supports RESTful data streams. The inputs capable of watching a system folder for files have some significant limitations and are generally intended to prove that real-time analytics you have designed in a GeoEvent Service behave as you intend. Moving toward production we expect real-time data feeds to arrive via HTTP/POST or as replies to queries you make on an external server's URL.

Best Regards -

RJ