Convert XLS to SDF

An application built for SQL Server Compact may require an .sdf database instead of an Excel workbook, so spreadsheet data must be loaded into relational tables. The conversion is normally a controlled import through CSV or application code, not a direct rewrite of the workbook file.

What the XLS format is

.xls is the binary Excel Workbook format used mainly by Microsoft Excel 97–2003 and compatible spreadsheet programs such as LibreOffice Calc. It can contain multiple worksheets, formulas, formatting, merged cells, charts, named ranges, and loosely typed values. Older business exports, archives, and legacy systems still produce XLS files.

An XLS worksheet is not automatically a relational table. It may contain title rows, mixed data types, blank cells, duplicate headings, subtotals, formulas, and presentation-only formatting. These elements must be removed or mapped before importing the data into SDF.

What the SDF format is

In the Databases category, .sdf normally means a SQL Server Compact database, also called SQL CE. It is a file-based relational database containing tables, columns, indexes, constraints, and optionally relationships. It does not preserve Excel formatting, charts, macros, comments, or workbook structure.

SQL Server Compact stores the database in one portable file and supports SQL queries and indexes without a separate database server. SQL Server Compact 4.0 is legacy software and has been discontinued, so confirm that the destination application supports its provider and database version before creating an SDF file.

How to convert XLS to SDF

Using SQL Server Compact Toolbox and CSV

1. Open the XLS file in Microsoft Excel or LibreOffice Calc. Remove title rows, merged cells, subtotals, duplicate headings, and empty trailing columns. Treat each worksheet intended for import as a separate table.
2. Define the target column types before exporting. Preserve identifiers such as 00123 as text, standardize dates, remove numeric thousands separators where necessary, and decide whether empty cells represent SQL NULL or empty strings.
3. Export each worksheet as a separate UTF-8 CSV file through File → Save As. CSV does not retain formulas, formatting, multiple worksheets, charts, relationships, or workbook metadata. Recalculate formulas in Excel or Calc first if their displayed values are the data to be imported.
4. On Windows, install the SQL Server Compact 4.0 runtime and SQL Server Compact Toolbox. Create a SQL Server Compact 4.0 database in the toolbox and save it with an .sdf extension.
5. Create the destination schema before importing rows. For example:
   CREATE TABLE Customers (CustomerID int NOT NULL PRIMARY KEY, Name nvarchar(200) NOT NULL, SignupDate datetime NULL);
   Use nvarchar for Unicode text and specify suitable precision and scale for financial decimal columns.
6. Use the toolbox data-import function, if provided by the installed version, to load each CSV into its corresponding table. Map columns by name or explicitly by position; do not rely on automatic type detection.
7. Run validation queries such as SELECT COUNT(*) FROM Customers. Compare source and destination row counts, inspect nulls and dates, test non-ASCII text, verify primary keys, and check for duplicate records.

Using a repeatable .NET import

For recurring conversions or large workbooks, use a .NET utility with an Excel reader such as ExcelDataReader and the legacy System.Data.SqlServerCe provider. Read each worksheet, create the SDF schema with CREATE TABLE, and insert values with parameterized INSERT commands inside batched transactions. Parameters prevent apostrophes and other cell contents from becoming SQL syntax, while transactions are substantially faster than committing every row.

LibreOffice can help automate intermediate export with soffice --headless --convert-to csv --outdir csv input.xls, but command-line CSV export may process only the active worksheet or otherwise vary by LibreOffice version. Export and verify each worksheet separately when all workbook tabs are required.

Using an online converter

Mainstream online services such as CloudConvert or Zamzar may convert .xls to .csv, which can then be imported locally with SQL Server Compact Toolbox. They generally do not produce a usable SQL Server Compact .sdf database with the required schema, keys, indexes, and relationships. Do not upload confidential or regulated workbooks; verify worksheet selection, character encoding, dates, and leading-zero identifiers in the downloaded CSV.

Quality and compatibility limits

Conversion normally discards workbook presentation and calculation features. Formulas are usually imported as their cached or displayed results rather than as formulas; formatting, charts, comments, macros, named ranges, and worksheet order are not represented in SDF.

CSV processing can convert identifiers with leading zeroes to ordinary numbers, represent long numbers in scientific notation, or misinterpret dates because of regional settings. Define the SDF schema before loading data, keep identifier columns as text when required, and use unambiguous date values such as 2026-10-01 where the target column is a date.

SQL Server Compact 4.0 has a maximum database size of approximately 4 GB and fewer SQL features than full SQL Server. Reproduce the consuming application's required table names, column names, data types, primary keys, indexes, relationships, and provider version exactly. An SDF file made for one SQL CE version or schema may not work with an application expecting another.