Import CSV file data into List View

  • 23 August 2018
  • 6 replies
  • 235 views

Badge +1

I came up with a way to read the contents from a CSV file into a table variable using a stored procedure. Once in the table variable, the data can be manipulated, join, aggregated, etc. to your hearts content before returning it to forms. I attached a very simple POC here that you can look at – it was built on 5.1 and requires an application database in SQL Server to host the stored procedure.

 

The only caveat here is that the stored procedure isn’t super flexible/dynamic – you cannot create 1 stored proc to import multiple different CSV files. You’ll need to create a separate stored proc for every CSV file.

 

I wanted to share this out so that it is available.

 

14995iFF024C692EA6A6A3.png

 


6 replies

Awesome stuff, Eugene. Nice work!
I have a problem not able to follow the steps you can recommend because of the Deploy. The system asks for Instance.Please recommend me.
Badge +1

Hello Nattapon,

The demo solution (CSV Import.kspx) references a database in SQL Server. To be able to deploy the demo solution, you'll need to:

  1. Create a database in SQL Server, and create a schema for that database called "test".
  2. Run the "CSV Import.sql" script file against the database created in step 1.
  3. Grant the account that will be used to connect to the database "db_owner" permissions.
  4. Go to K2 Management and create a new SQL Server Service Instance to connect to the database created in step 1 using the credentials that you configured in step 3. Use the details in the "SQL Server Service Instance.txt" file to register the new service instance. For more information, see the product documentation
  5. Use Package and Deploy to deploy the "CSV Import.kspx" file. When the contents of the package has been loaded, the Service Instance will show with a red icon. Right-click on it and select "Configure". In the popup screen, in the top right-hand corner, click on the ellipses button and select the service instance that you created in step 4. Click on OK and you should be able to deploy.

I hope this points you in the right direction.

 

Regards,

 

Eugene

Badge +5

Hi Eugenej,

 

  Will i be able to use this for Excel?, I need to read  Excel cell value from the smartForm. I tried using  the similar teachnique to Convert Base64 string to plain text. But it is giving me junk value.

 

Badge +1

This approach wasnt meant to read a specific Excel cell value into a SmartForm. What this example does is to return all the rows and cells in a CSV file as a List method in a SmartObject. You'll need to code a custom web-service for the functionality that you need.

I have any question, Can i change character for support multiple language? and how to.

Reply