Start a new topic

Code sample export data to CSV

HI 

Hope the below is useful


Code is used to extract data from an sql server.

Emails records count and elapsed time, and encloses quotes/commas in format IOM can read

The extract happens BeforeImport Event.

The rest frontend of the profile has been set up to process the data of this file we have just extracted

We then setup the profile to run automatically with the Windows Task scheduler


essentially we

1  Leverage SQL view to translate/format our data

2  Custom code used to place csv file at target location

3  IOM profile to import/update data

all done automatically via Windows Task Scheduler


I welcome any insight as regards error handling or issues dealing with characters that can mess up the IOM profile


'=======[CODE STARTS HERE]

'Put this in your global.vb

'This code is available across multiple profiles

Imports Microsoft.VisualBasic

Imports system

Imports System.linq

Imports system.data

Imports system.data.sqlclient


Public Class GlobalCode

Inherits ImportOM.API.GlobalBase


'Add any code here that will be useful from one profile to another

Private Shared function encloseQuotes(str As String)As String

If str.contains(chr(34))=True Then

str = str.replace(chr(34),chr(34) & chr(34))

End If

If str.Contains(chr(34))=True Or str.Contains(",")=True Then

str=chr(34) & str & chr(34)

End If

Return str

End function


    Public Shared Sub ExportSQLtoCSV(strSQL As String, strFilename As String, _

    myImpSession As importom.API.iImportSession, myDistList As String) 

    

    Dim StartTime as DateTime

Dim ElapsedTime As TimeSpan

starttime = now()

' Try

'adjust text string values appropriate for your SQL connection

Dim myConnection As sqlconnection =New sqlconnection("server=mySQLServerName;database=myDatabaseName;uid=aValidUser;pwd=aPassword")

myConnection.open

Dim myCommand As sqlCommand = New sqlCommand  (strSQL, myConnection)

Dim myDataReader As sqlDataReader = myCommand.executereader

'need to put code here to ad

Dim outPutStream As IO.StreamWriter=New IO.StreamWriter(strFilename)

'Write the column headers

Dim iFldCount As Long

Dim myLine As String = mydatareader.GetName(0)

For ifldcount = 1 To mydatareader.FieldCount-1

myline = myline & "," & mydatareader.GetName(ifldcount)

next

outputstream.WriteLine(myline)

'Write the data table

Dim iRecordCount As Long

Do While myDataReader.read

Dim myValues(myDataReader.fieldcount-1) As Object

myDataReader.getvalues(myvalues)

'get first column from this row

myline = enclosequotes( myvalues(0))

'get all other columns from this row

For i As Long = 1 To myDataReader.fieldcount - 1

myLine = myLine & "," &  enclosequotes(myValues(i)) ' myFieldValue

next

outputstream.writeline(myline)

irecordcount = irecordcount + 1

loop

myDataReader.close

outputstream.Close

myConnection.close

myDataReader.dispose

outputstream.Dispose

elapsedTime=now().subtract(StartTime)

'message format is

'need the import profile object that will send the email

myImpSession.SendEmail(mydistlist, "", "", "IOM data exported", _

"" & strFilename & "

" _

& irecordcount  & " records exported.

regards " & myImpSession.REUsername & "



Export took " & String.Format("{0:00}:{1:00}:{2:00}", _

       CInt(ElapsedTime.TotalHours),  CInt(ElapsedTime.TotalMinutes) Mod 60, CInt(ElapsedTime.TotalSeconds) Mod 60) & _

       " to complete

SQL command:
" & myCommand.CommandText & " ")


myConnection.dispose

myCommand.dispose

'Catch

'End try

    End sub

End Class


'=======[CODE ENDS HERE]


'=======[CODE STARTS HERE]

'Put this in appropriate place in your profile.vb

'This code is specific to your current profile

Public Class Profile

    Inherits ImportOM.API.ProfileBase

Public Overrides Sub BeforeImport(ByVal Cancel As ImportOM.API.iCancel)

MyBase.BeforeImport(Cancel)



Call GlobalCode.ExportSQLtoCSV("SELECT * FROM myView", _

"\\myServer\myShare\myFileName.csv", _

import, "someone@example.org")

End Sub

End Class


'=======[CODE ENDS HERE]


OOOOOOOO

How do you get the Windows Task Manager to run IOM on a file?

I think I would do a similar thing but pull the data down from my online data base through their web service API to build the file.
OMG I just found the IOM console command...

My world is forever changed!
Login or Signup to post a comment