Append a column in the imported file with its file name

Hi Bibin,

Yes it is.  If you look at my script for importing multiple files I have that option included in there so feel free to steal the code.   If you need some help integrating it with your script let me know and I can write a demo script for you.

Thanks

Brian

Hi Bibin, here is some example code that will hopefully get you going.  The first function imports and Excel spreadsheet, the function return the tables name.  The second function is then sent the table name and then creates a field called table that will hold it.

Let me know if you have any questions.

Brian

Option Explicit

Sub Main
	Dim sFileName As String
	sFileName = ExcelImport()	'C:\Users\elementb\Documents\IDEA\Samples\Join.xlsx
	'call the function to add a virtual field that will contain the new table name
	Call FieldManipulationAppendFields(sFileName)	'Join-Database.IMD
End Sub


' File - Import Assistant: Excel
'import the excel file.
Function ExcelImport() As String
	Dim task As task
	Dim dbName As String
	Set task = Client.GetImportTask("ImportExcel")
		dbName = Client.LocateInputFile ("C:\Users\elementb\Documents\IDEA\Samples\Join.xlsx")
		task.FileToImport = dbName
		task.SheetToImport = "Database"
		task.OutputFilePrefix = "Join"
		task.FirstRowIsFieldName = "TRUE"
		task.EmptyNumericFieldAsZero = "FALSE"
		task.PerformTask
		dbName = task.OutputFilePath("Database")
	Set task = Nothing
	'return the name of the excel file
	ExcelImport = dbName
End Function

' Data: Field Manipulation - Append Fields
Function FieldManipulationAppendFields(sTableName As String)
	Dim db As database
	Dim task As task
	Dim table As table
	Dim field As field
	Dim eqn As String
	'get rid of the path information from the table name
	sTableName = iSplit(sTableName, "\", "", 1,1)
	Set db = Client.OpenDatabase(sTableName)
		Set task = db.TableManagement
			Set table = db.TableDef
				Set field = table.NewField
					eqn = """" & sTableName & """"  'add the table name to the equation
					field.Name = "TABLE"
					field.Description = ""
					field.Type = WI_VIRT_CHAR
					field.Equation = eqn
					field.Length = Len(sTableName) 'make sure the length of the field is the same as the table name
					task.AppendField field
					task.PerformTask
				Set field = Nothing
			Set table = Nothing
		Set task = Nothing
	Set db = Nothing
End Function

Hi all,
Here is a simple script to to it in the current file :
'---------------
Sub Main
Call AppendField() 'Source
End Sub
' Add a field
Function AppendField
Dim Nom As String
Dim LenRight As Integer
LenRight = (Len(Client.CurrentDatabase.Name) - Len(Client.WorkingDirectory))
Name = """" & Right(Client.CurrentDatabase.Name,LenRight) & """"
Set db = Client.CurrentDatabase()
Set task = db.TableManagement
Set field = db.TableDef.NewField
Formule= Name 
field.Name = "SOURCE"
field.Description = ""
field.Type = WI_VIRT_CHAR
field.Equation = Name
field.Length = 255
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
'--------------------------------------