Using Job to Import Data from Text File

[jcg6]

Is it possible to use a job to import a comma delimited file directly into a table? This job would need to run nightly. Thanks for any assistance.

 

[jabrony76]

Yes, very easily via DTS.

Set up a DTS package to import the text file. Easiest way to do this is via the Import Data Wizard. Then just select to save the package at the end of the wizard and then right-click on it and schedule it as a job.

 

[nigelrivett]

Have a look at

http://www.nigelrivett.net/ImportTextFiles.html

It imports all files in a directory and archives them.

It's usually easier to use bulk insert for importing text files than dts.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Jokada]

i think this is what you need
just replace the select statement with an update or insert statement and remove the code to write to the second file.
I read all information from the file into an 2 dimensional array. retrieve the info from it and make your sql statement.
to shedule the program, use "planned tasks" from windows


'set DB and connection
Set MyConnection = CreateObject("ADODB.Connection")
SQLServer = "ES-APPL-LD"
SQLDatabase = "IPDatenbank"
MyConnection.Open "Driver={SQL Server};server=" & SQLServer & ";database=" & SQLDatabase & ";uid=SA;pwd=;"

'set recordset
Set rsFindUser = CreateObject("ADODB.Recordset")


'file to write to
Const ForAppending = 8
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
CurrTime = Now()
'---------------------------------------------------------
'
'CHANGE HERE THE FILE YOU WANT THE RESULTS IN
'
'---------------------------------------------------------

filenameWrite = "C:\UsersInternetJuni-" & Left(CurrTime, 10) & ".txt"
Set objTextFile2 = objFSO2.OpenTextFile(filenameWrite, ForAppending, True)
'this is an option that doesn't work
'filenameWrite = InputBox("Please enter the exact path you want the result file written to", "Enter Path ","C:\UsersInternetJuni-" & Left(CurrTime, 10) & ".txt")

'file to read from
Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
'---------------------------------------------------------
'
'CHANGE HERE THE FILE YOU WANT THE RESULTS FROM
'
'---------------------------------------------------------
filenameRead = "F:\internet\Juni 2004.csv"
Set objTextFile = objFSO.OpenTextFile (filenameRead, ForReading)

Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline
arrServiceList = Split(strNextLine , ";")
arrUser = Split(arrServiceList(0), "\")
For i = 1 to Ubound(arrUser)
strUserName = arrUser(i)
strSQLCreate = "SELECT [Username], [VoorNaam],[AchterNaam],[Location],[Kostenstelle],[Abteilung],[Description] FROM [Benutzer] WHERE Username Like '" & strUserName & "'"
'open recordset with the defined user
rsFindUser.Open strSQLCreate, MyConnection
'check if recordset is empty
If rsFindUser.EOF Or rsFindUser.BOF Then
'if user is not found
strVoornaam = "deleted"
strAchternaam = "deleted"
strLocation = "deleted"
strKosten = "deleted"
strAbteilung = "deleted"
strDescr = "deleted"
Else
arrRsUser = rsFindUser.getRows()
strVoornaam = Trim(arrRsUser(1, 0))
strAchternaam = Trim(arrRsUser(2, 0))
strLocation = Trim(arrRsUser(3, 0))
strKosten = Trim(arrRsUser(4, 0))
strAbteilung = Trim(arrRsUser(5, 0))
strDescr = Trim(arrRsUser(6, 0))

End If
rsFindUser.Close()
Next
For i = 1 to Ubound(arrServiceList)
strMBs = arrServiceList(i)
Next
objTextFile2.WriteLine(strUserName & vbtab & strVoornaam & vbtab & strAchternaam & vbtab & strLocation & vbtab & strKosten & vbtab & strAbteilung & vbtab & strDescr & vbtab & strMBs & " MB")
Loop
MyConnection.close
objTextFile.close
objTextFile2.close
MsgBox "Fertig!!" & vbCrLF & "Sie finden die File wieder unter:" & vbCrLf & filenameWrite ,vbInformation