Import data from SQL Server into Fabrik

[nusilmar]

This is not a question but is in the spirit of sharing information to us all. I don't know if you do this diferently but this is what i come up with.

I had the need to have a table in fabrik with certain data from my ERP ( pending values from customers) and needed that to be constantly updated ( for us on an hourly basis). So i made an query on my local server with the info i needed and then i uploaded to my web server and then i imported that into fabrik. This is my step by step :

1. Build your query so that you have all the data you need to use in fabrik ( check fields needed)
2. Create a list in fabrik with the elements you need
3. Create in your local server the following folders ( i will start from a base folder "e:\FTP\") .
   Create a folder "PEND" ( will be "e:\ftp\pend")
   Create a folder "Trash" inside "PEND" ( will be "e:\ftp\pend\trash" )
   Create a folder "Clean" inside "PEND" ( will be "e:\ftp\pend\clean" )
4. Now comes the tricky part ; Since you need to make an csv to be imported the best way is to do it by PowerShell. But you also need that the csv to be formated in a way that it could be imported clearly by fabrik. Since the build of the csv adds some separators between the header and the data and also adds a total records line i needed to do the following :
   1. Create a SQLCMD to create an CSV with ONLY the headers i need
      

      SQLCMD -S "<sqlserver instance>" -d "<SQL DB>" -U "<SQL user (usually sa)>" -P "<Usre password>" -Q ?set nocount on;SELECT top 0 Id AS id, dtmDate AS date_time, strAbrevTpDoc AS tpdoc, strNumber AS numdoc, intCodEntity AS customer, fltTotal * intSign AS voriginal, fltValuePaid * intSign AS vpaid, fltPendValue * intSign AS Vpend, intCodEntity AS fabcust FROM Mov_Sales_Cab WHERE (NOT (fltPendValue = 0);set nocount off? -W -s ?;? -o ?E:\Ftp\pend\Trash\header.csv?

      Now a little explanation about this : Since my SQL DB Fields may not be the same as the ones from Fabrik i needed to create the "AS" clause to match my SQL DB header with my Fabrik elements.
      This is also needed , for me at least, to create a BLANK CSV with only an header. I will not be here explaining the way SQLCMD works but so that you know , when i add headers to my output SQLCMD adds something like this ( witch is going to be the output of this query) :
      

      id,date_time,tpdoc,numdoc,customer,voriginal,vpaid,Vpend,fabcust
      --;----;----;----;----;----;----;----;----;----

      As you may notice it adds a second line with minus and comas.
   2. To get rid of that second line i need this :
      

      findstr /R /C:"^[^-]*$" E:\Ftp\pend\Trash\header.csv > E:\Ftp\pend\Clean\headerclean.csv

   3. And finally i need to get my data, so repeat the query but this time with only the data :
      

      SQLCMD -S "<sqlserver instance>" -d "<SQL DB>" -U "<SQL user (usually sa)>" -P "<Usre password>" -Q ?set nocount on;SELECT Id AS id, dtmDate AS date_time, strAbrevTpDoc AS tpdoc, strNumber AS numdoc, intCodEntity AS customer, fltTotal * intSign AS voriginal, fltValuePaid * intSign AS vpaid, fltPendValue * intSign AS Vpend, intCodEntity AS fabcust FROM Mov_Sales_Cab WHERE (NOT (fltPendValue = 0);set nocount off? -W -h-1 -s ?;? -o ?E:\Ftp\pend\Clean\header.csv?

5. Now copy-paste the 3 codes in the order above and put it in a text document and save it as exportpend.ps1 in a diferent folder ( eg: C:\EXPORTCMD\ )
6. Now we need to create a bat or cmd file with the join operation of the 2 csv files into one and also a way for us to execute the powershell file. So we need to do this :
   

   Powershell.exe -executionpolicy remotesigned -File  C:\EXPORTCMD\EXPORTPEND.ps1
   cd E:\Ftp\pend\Clean
   type E:\Ftp\pend\Clean\headerclean.csv E:\Ftp\pend\Clean\pend.csv > E:\Ftp\pend\Clean\pendvalues.csv
   copy E:\Ftp\pend\Clean\pendvalues.csv E:\Ftp\pend /Y
   del E:\Ftp\pend\Clean\pendvalues.csv /Q

   Do not forget to manualy run this batch file at least once so that the policy be accepted by Windows ( for me this was needed on my W2012R2 )
7. The basic is done , now we need to send it to our webserver and need to do this in on a regular basis so the best way for me to do this is to create an hourly ( for me is enough, but you could set a different timer) and use a program ( i use SyncBackFree ) to send the E:\Ftp\pend\pendvalues.csv file to your web server ( by ftp)
8. Once you get this on your webserver you only need to create a schedule task on fabrik to import your csv file into your created list and do it also hourly ( as example). Some things you should setup in the task ( this is working for me and is what i need but you may change to what you want) : Activate the option "Drop List Data" and "after import move to done folder"

Hope that this helps some of you, and sorry for the long post. Will be putting this in Wiki , on real life examples soon.