Convert XLS to MDF

A legacy spreadsheet may need to be loaded into Microsoft SQL Server when a business application, reporting system, or database administrator requires SQL Server data rather than an Excel workbook. The conversion is an import into a SQL Server database; it is not a direct file-format rewrite.

What the XLS format is

XLS is Microsoft Excel's binary workbook format for Excel 97–2003. It stores worksheets, cell values, formulas, formatting, charts, comments, and limited workbook metadata. Excel, LibreOffice Calc, and older business systems can create XLS files.

An XLS workbook is not a relational database. During import, each selected worksheet normally becomes a SQL Server table, and its first row can become the table's column names.

What the MDF format is

MDF is the primary data file used by Microsoft SQL Server. It stores database pages containing tables, indexes, schemas, and other database objects. SQL Server normally creates a separate .ldf transaction-log file alongside it.

MDF is appropriate when data must be queried with Transact-SQL, used by a SQL Server application, or administered through SQL Server Management Studio (SSMS). It is not a portable spreadsheet format, and renaming workbook.xls to database.mdf does not convert the contents.

How to convert XLS to MDF

Using SQL Server Import and Export Wizard

1. Install a local SQL Server instance, such as SQL Server Express for many local workloads or Developer Edition for development and testing. Install SSMS separately if it is not included. Typical instances include localhost\SQLEXPRESS and (localdb)\MSSQLLocalDB.
2. Make a backup of the XLS file. Identify the worksheets to import, confirm whether the first row contains column names, and remove workbook encryption if the provider cannot open the file. Worksheet protection alone is a separate Excel feature and may still restrict editing.
3. Create a destination database in SSMS, or let the wizard create one. To control the MDF and LDF locations, run a command such as CREATE DATABASE XlsImport ON PRIMARY (NAME = XlsImport, FILENAME = 'C:\SQLData\XlsImport.mdf', SIZE = 50MB, FILEGROWTH = 25MB) LOG ON (NAME = XlsImport_log, FILENAME = 'C:\SQLData\XlsImport_log.ldf', SIZE = 25MB, FILEGROWTH = 25MB);. The directory must already exist and be writable by the SQL Server service account.
4. Open SQL Server Import and Export Wizard. Select Microsoft Excel as the data source, choose the XLS file, and select the Excel 8.0 or Excel 97–2003 format. If the Excel provider is missing, install the Microsoft Access Database Engine matching the wizard's 32-bit or 64-bit architecture.
5. For the destination, select Microsoft OLE DB Driver for SQL Server, then specify the SQL Server instance, authentication method, and XlsImport database. The older SQL Server Native Client may work on existing installations but is deprecated.
6. Choose Copy data from one or more tables or views. Map each worksheet to a table, remove unwanted sheets, rename tables and columns where necessary, and enable the first-row-as-column-names option when applicable.
7. Review the column mappings instead of accepting every inferred type. Assign suitable SQL types to dates, decimal amounts, identifiers, and long text. Run the import, then verify row counts and values in SSMS, for example with SELECT COUNT(*) FROM dbo.Sheet1;.

SQL Server creates the MDF as part of the database creation process. To locate the physical files, run SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'XlsImport');. For transfer to another SQL Server, a .bak backup and restore is usually safer than copying database files.

If an MDF is specifically required, stop access and detach the database only after confirming that no required connections remain:

ALTER DATABASE XlsImport SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sys.sp_detach_db N'XlsImport';

Attach the MDF and its LDF through Databases → Attach in SSMS. The destination server must be the same or a compatible newer SQL Server version, and its service account must have access to the file directory. Do not distribute only the MDF unless the recipient understands SQL Server attachment and recovery procedures.

Using automation

Recurring imports can use SSIS, PowerShell, or Python with pandas and pyodbc. These tools read the XLS workbook and insert rows through SQL Server; they do not write a valid MDF file directly. A database administrator should add primary keys, constraints, indexes, and appropriate schemas after loading the tables.

Quality and compatibility limitations

• Worksheet formatting, charts, merged-cell layout, comments, macros, and most formulas are not relational database objects. Imports generally load values or formula results, not the workbook's presentation or executable VBA. Recalculate and save the workbook in Excel first if current formula results are required.
• The Excel provider infers column types from sampled rows. Mixed numeric and text values can become NULL, be truncated, or be converted incorrectly. Import uncertain columns as text into staging tables, then clean and cast them with SQL.
• Legacy XLS worksheets are limited to 65,536 rows and 256 columns. SQL Server supports different limits, but imported data still needs suitable types and a deliberate table design.
• Online conversion services such as Zamzar or CloudConvert are not suitable for producing a deployable SQL Server MDF; they target document and spreadsheet conversions rather than SQL Server database creation. Uploading business spreadsheets can also disclose their contents. Use a local SQL Server instance and SSMS for this conversion.