In this example we are going to make a job to import an XLSX-file. The XLSX file contains one TAB called ‘Customers’. Step 1 – 3 are done in SQL Server Management Studio.

Create a table to contain the filenames

You need to create a table to contain the filenames (path included) of the files that have to be imported. The fields in the table should contain the following information:

1. File name: Filename and path to the file at the time of the import.
2. Date: Fill this field when the import was successful
3. Type: Indication of the type of import file

Example code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Custom Import Files](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[File name] [nvarchar](250) NULL,
	[Date] [datetime] NULL,
	[Type] [nvarchar](100) NULL
) ON [PRIMARY]
GO

Table with the contents of the XLSX file

Now create a new table to contain the contents of the XLSX file we are going to import. The fields in the file should be large enough to contain whatever is in your (test) file. All fields are created as NVARCHAR. Transformation to other formats will be done later in the process.

Example code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Custom Import XSLX data](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Field1] [nvarchar](250) NULL,
	[Field2] [nvarchar](100) NULL,
	[Field3] [nvarchar](100) NULL,
	[Field4] [nvarchar](100) NULL,
	[Date] [nvarchar](10) NULL,
) ON [PRIMARY]
GO

Configuration

Next, create the configuration to select the files that should be imported. For example we are going to scan folder ‘E:\Data\Import\’ for files which names start with ‘MyImportFile’.
(remember that E: is not your local environment when you run this, it will be E: on the server where you run the software)

Make an entry in table [Tasks]

ID	2	(Just an ID)
Batch ID	10	(ID to identify this action – this ID has to be unique)
Description	‘Import of test file’	(description of the process – not mandatory)
Source type	null
Source name	‘E:\Data\Import\MyImportFile*’
Clear destination	0
Active	1
Option	null
Memo	null

And the second entry [import]

ID	3
Batch ID	30	(same ID as in the [Tasks] table entry)
Description	‘Import of test file’
Table name	‘Custom Import Files’
Field name	‘File name’
Source column	‘File name’
Operation	nul
Text	null
Key	1

And another table [import]

ID	4
Batch ID	30	(same ID as in the [Tasks] table entry)
Description	‘Import of test file’
Table name	‘Custom Import Files’
Field name	‘Type’
Source column	null
Operation	‘=’
Text	‘Test XSLX’
Key	0

This is all the configuration needed. It will make the import task scan ‘E:\Data\Import\’ for files with a name that starts with ‘MyImportFile’. For every file that is found a record will be added to table ‘Custom Import File’ where the name of the file is placed in field [File name] and the field [Type] will be set to ‘Test XSLX’

All that is needed to do this in the import job is the ‘Import_Directory’ command (see step 6 Create a job to perform the import).

Specify the name of the file to be imported

For the next step we will use the MA!N Commands Window. We are going to specify what file has to be imported. We can retrieve this from the [Custom Import Files] table and need to update the [Tasks] table. The entry in [Tasks] has to be created once. This will be done by command that we create in MA!N and should look like this for example:

UPDATE	[dbo].[Tasks]
SET	[Source name] =	REPLACE	(	(	SELECT	TOP 1 [File name]
								FROM	[dbo].].[Custom Import Files]
								WHERE	[Date] is NULL
								AND [Type] = 'Test XSLX'
								ORDER BY	[ID]
							)
							, 'F:\Data'
							, ''
						)
						+ ':Customers'
WHERE	[ID] = 2

After this update the record in the [Tasks] table should look like this:

ID	2
Batch ID	20
Description	‘Import of XLSX test file’
Source type	‘Excel:False:False’
Source name	‘\Import\ MyImportFile.xlsx:Customers’
Clear destination	1
Active	1
Option	NULL
Memo	NULL

We replace ‘F:\Data’ with ‘ ‘ because MA!N will know that it should look in the Import folder.

Make entries in the [Import] table for all the columns in the XLSX file.

ID	5
Batch ID	20	(same ID as in the [Tasks] table entry)
Description	‘Import of XLSX test file’	(description of the process – not mandatory)
Table name	‘Custom Import XSLX data’	(Target table)
Field name	‘Field1’	(Column in target table)
Source column	0	(0 = first column)
Operation	null
Text	null
Key	0

And the second entry

ID	6
Batch ID	20	(same ID as in the [Tasks] table entry)
Description	‘Import of XLSX test file’	(description of the process – not mandatory)
Table name	‘Custom Import XSLX data’	(Target table)
Field name	‘Field2’	(Column in target table)
Source column	1	(1 = second column)
Operation	null
Text	null
Key	0

(in our example we need to add 5 entries in total to the [Import] table for this step, one for each column in the source table)

Create a job to perform the import

For this step you will go to the Jobs Window in MA!N (View > Configuration > Jobs). In this window we will select ‘NEW’. A window should pop-up where you can create the new job.

This command ensures that first step 3 is executed and then the steps 4 and 5. If it’s a large import file we can indicate that the process can use more time by specifying ;999 at the end.

Data_Import_File;20 -> Data_Import_File;20;999

Add job to scheduler

The final step is to schedule the Job. To do this we will open the Jobs Scheduled Window ((View > Configuration > Jobs Scheduled). In this window we will select ‘NEW’. A window will pop-up where you can add the new job to the scheduler.