Append a column in the imported file with its file name

bbaburaj

Forums

Hello everyone,
Hope you all are doing great.
I have a question regarding data importing. Is it possible to append a column with its file name when it is importing to IDEA? for example, My file name is XXX and i want to import the file into IDEA and after that i need to append the file name (i.e. XXX) to that database as a seperate column. I use to utilize multiple data import macro which i sdeveloped by Brian and that is really amazing. Is it possible to incoporate this option to that?
Eagerly waiting for valuable suggestions
Thanks,
Bibin K B
 
 

Brian Element Mon, 01/16/2017 - 07:22

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

Brian Element Mon, 01/16/2017 - 12:16

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

 

mretourne Thu, 11/09/2017 - 08:37

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
'--------------------------------------