Start a new topic

Code sample export data to CSV


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


'Put this in your global.vb

'This code is available across multiple profiles

Imports Microsoft.VisualBasic

Imports system

Imports System.linq



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")

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)



'Write the data table

Dim iRecordCount As Long

Do While

Dim myValues(myDataReader.fieldcount-1) As Object


'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



irecordcount = irecordcount + 1








'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 & " ")




'End try

    End sub

End Class

'=======[CODE ENDS 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)


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

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

import, "")

End Sub

End Class

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


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