Import csv into workflow without sql

Permanently deleted user

31. Oktober 2017 05:37

I would like to import a csv that is automatically generated each night into my workflow so my form is prefilled for my users instead of them having to copy paste from the csv.

 

I have a query task for spatial layers, but I am not sure if this is possible if it is just a csv file on a directory drive. This is all I have available to me as I do not have access to sql.

 

 

0

• 

  Permanently deleted user

  4. Dezember 2017 21:54

  Hi Sylvia, 

   

  If you have the right drivers installed on your server (eg, the x64 version of https://www.microsoft.com/en-ca/download/details.aspx?id=13255), and the csv file sitting somewhere your server can find as a local file path, you can query it using the SQLQuery activity even though it itsn't a SQL table.  The SQLQuery activity isn't quite as easy to use as the QueryTask, but I'll try to cover the important parts.

   

  Here's an example of what you would use in the connection string inside the Connection property: Driver={Microsoft Access Text Driver (*.txt, *.csv)};Dbq=C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\Default\REST Elements\Sites\LA_County\Resources\Data;Extensions=asc,csv,tab,txt; Replace C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\Default\REST Elements\Sites\LA_County\Resources\Data with the path to the folder that has the csv file, and chose System.Data.Odbc from the "Provider Name" dropdown list below the connection string.

   

  Here's an example of what you would use in the Command property select * from LAParkTrees.csv WHERE COMMON_NAME = 'AFRICAN WATTLE'; You would replace LAParkTrees.csv with the name of the csv file, and customize the rest for the type of query you need to run.  You can use the Paremeters property to bring in variables from the rest of the workflow as parameters if needed.

   

  Then you can work with the data table as-is, or use the DataTableToFeatureSet activity if you're more comfortable working with featuresets instead.  One thing to keep in mind is that DataTables don't like to be serialized without having their tabename proptery set.  Keep the DataTable variable (from the SQLQuery results) scoped to a sequence that doesn't have any client-side activities.

   

  Hope that helps!

   

  -Amanda

  0
• 

  Permanently deleted user

  12. Dezember 2017 22:45

  thanks Amanda,

   

  I tried your method after getting someone to install the 64 bit drivers on the server. However I get the below error with my connection

   

  Connection failed!

   

  DataConnection '': Unable to establish a connection for DataConnection ''. [DataConnection '': "The connection string of DataLinkConnectionItem '' is invalid. [ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]]

   

  I am using the below connection string

   

  Driver={Microsoft Text Driver (*.txt; *.csv)};Extensions=asc,csv,tab,txt;Dbq=C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\GE470\REST Elements\Sites\test\DLI Job Book Survey Details.csv

  0
• 

  Permanently deleted user

  21. Dezember 2017 17:13

  Hi Sylvia, 

   

  The connection string you posted doesn't quite match my example.  It looks like it matches the 32 bit version of the driver, instead of the 64 bit version (see https://www.connectionstrings.com/microsoft-text-odbc-driver/).  Try this instead: Driver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;Dbq=C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\GE470\REST Elements\Sites\test

   

  -Amanda

  0

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.