Today I've been wrestling with the SQL Server import tool.  On the surface, it looks like it will import Excel files, CSV files and the like straight into a database table.

Not so.

The Excel import has a lot of issues. Mainly truncation issues - despite setting all the switches to ignore truncation etc.  What a joke.

Here's how I eventually converted an Excel file into a SQL Server table.

1. Open the Excel file
2. Select the entire spreadsheet and copy it
3. Create a new Microsoft Access and saved it as a 2003 (.mdb) format.  Seems like the SQL Server 2008 data import wizard cannot read Access 2007 files!  .mdb files only. Go figure.
4. Create a new table
5. Paste Excel contents into new table.  Data looks good
6. Use the SQL Server import wizard to import from an Access file
7. All good!

Of course, the whole reason why I needed to go through this rigmorole is just to convert the Excel spreadsheet into XML.  Saving Excel data into XML is a horribly complex task.  Use the "For XML" clause of SQL Server is quick, simple and gives a lot of options.

The trick is, you need your data in a SQL Server database...