Excel to SQL Server Import Demo

This repo showcases the ExcelReadBulk function for reading large Excel files as well as the capability of specifying a database table and columns at runtime when using the Database DbBulkCopy function.

Requirements

• SQL Server 2019 or up
• Linx Designer 6.14.x or up

Set up

• Run the CreateDatabase_DbBulkCopy.sql SQL script in SQL Server to create the DbBulkCopy database.

Running the import demo

1. Open the Import Linx solution in the Solution folder in Linx Designer.
2. Open the Settings and set appropriate values for the TargetDbUser and TargetDbPassword settings.
3. Set the DataFolderPath to point to the Data folder.
4. Debug the Main process.

The process exports the data from the Excel spreadsheet into an intermediate text file and then imports the data from the text file into the database.

Note: The solution could be made to export the data from the Excel spreadsheet directly into the database without an intermediate text file, though the demo shows how the import may be done as two separate steps.

By default the process imports 50 000 rows of data. To import a file containing 500 000 rows of data, change the ImportFilePath setting to point to the input_500_000.xlsx file.

Performance

Importing 50 000 rows from the text file into the database is seen to take about 2.5 seconds.

To cater to line-breaks, the solution replaces each line break with a \x01 character when writing the text file and changes them back to line-breaks when writing to the database. If the incoming data is known to not include any line breaks within values, the _02TextFileToDatabase process can be simplified to further increase the performance: Remove the line-break replacement in the expression of the RowValues function so the expression becomes =TextFileRead.ForEachLine.LineContents.Split('\x00'). This improves the runtime of an import of 50 000 rows to about 1 second.