Import Text file, with a twist

[SQLBI]

Hi,

I've posted this thread originally in the SQL programming forum, but now think DTS is my answer...

Please take a look at thread183-1038907.

Many thanks.

Cheers,
Leigh

"Give a man a fish and he can feed himself for a day..."
Give him the means to catch his own fish and he can really annoy his partner by spending all his spare time fishing!

 

[gradley]

Unfortunately DTS does not deal well with multi-formatted data. You can setup the import using Fixed field format instead of delimited but you still have to designate the start and end positions for each of the fields.

What I've done in the past (although it's a pain), is created an ActiveX script and used File System Object to read the multi-format and extract into multiple temporary formatted files which can then be imported using DTS.

If you would like to persue this option, I can supply a sample.

 

[SQLBI]

gradley,

A sample would be helpful, many thanks.

Cheers,
Leigh

"Give a man a fish and he can feed himself for a day..."
Give him the means to catch his own fish and he can really annoy his partner by spending all his spare time fishing!

 

[gradley]

This example takes an input file with 3 possible formats:

1. Record begins with "COR" -- New "COR" record.
2. Record begins with "LVL" -- New "LVL record.
3. Record doesn't begin with either -- Old "COR" record.

Your logic will obviously be different. If looks like your formats are determine by a record with a [key name] first. You can probably read the file similiar to below and only write those records following the key names specified.

Hope this helps!

Function Main()

	CONST ForReading = 1

	dim objInterfaceFile
	dim objInterfaceLVL
	dim objInterfaceCOR
	dim objFSO
	dim objStoreCountFile
	dim fileInput
	dim fileLVL
	dim fileCOR
	dim strHoldValue
	dim intPointer
	dim intPointer2
	dim i

	objInterfaceFile =  DTSGlobalVariables("strInterfacePath").Value & "LearningCenter\EmployeeTLC.int"
	objInterfaceLVL =  DTSGlobalVariables("strInterfacePath").Value & "LearningCenter\LVLEmployeeTLC.int"
	objInterfaceCOR =  DTSGlobalVariables("strInterfacePath").Value & "LearningCenter\COREmployeeTLC.int"
	
	'  instantiate the Scripting Object
	set objFSO = CreateObject("Scripting.FileSystemObject")
	
	If objFSO.FileExists(objInterfaceCOR) Then
		objFSO.DeleteFile(objInterfaceCOR)
	end if
	
	If objFSO.FileExists(objInterfaceLVL) Then
		objFSO.DeleteFile(objInterfaceLVL)
	end if

	SET fileLVL = objFSO.CreateTextFile(objInterfaceLVL, false)
	SET fileCOR = objFSO.CreateTextFile(objInterfaceCOR, false)

	fileLVL.WriteLine "DUM||||||||||"
	fileCOR.WriteLine "DUM|||||||||||"

	SET fileInput = objFSO.OpenTextFile(objInterfaceFile, ForReading)
	strHoldValue = fileInput.Readline

	Do while  fileInput.AtEndOfStream <> True
	SELECT CASE  Left(strHoldValue,3)
		CASE "COR"
			fileCOR.WriteLine strHoldValue
		CASE "LVL"
			fileLVL.WriteLine strHoldValue
		CASE ELSE
			intpointer2 = 1
			For i = 1 To 4
			    intPointer = InStr(intPointer2, strHoldValue, "|")
			    intpointer2 = intPointer + 1
			Next
			strHoldValue = "COR|" & Left(strHoldValue,intPointer) & "|" & Mid(strHoldValue,intPointer + 1, Len(strHoldValue) - intPointer)
			fileCOR.WriteLine strHoldValue
	END SELECT
	strHoldValue = fileInput.Readline
	Loop
	fileInput.Close

	SET fileInput = Nothing
	SET fileLVL = Nothing
	SET fileCOR = Nothing
	SET objFSO = Nothing
		


	Main = DTSTaskExecResult_Success
End Function

 

[gradley]

One other note:

The "DUM||||||||||" values are created in case no records are written for a specific format. The new file will only contain this record.