The easiest solution would be to write a small script that reads that file and executes some queries to import these data sets.
If you want to use PHP (just one example, more information here) it would be something like:
<?php
$db = new PDO($connectStr, $username, $password);
$q = $db->prepare("INSERT INTO table (...) VALUES (...)");
$file = file_get_contents(...);
$lines = explode("\n", $file);
foreach ($lines as $line) {
$data = explode(";", $line);
// manipulate password here, for PHP, use password_hash instead of md5
$q->execute([$data]);
}
?>
Another promising solution could be LOAD DATA INFILE.
You should have a look at http://dev.mysql.com/doc/refman/5.1/en/load-data.html for more information.
The Import/Export wizard builds an SSIS package under the covers but the package the wizard builds only performs the E and L of ETL (Extract, Transform, Load). What you're looking for is T because you needed to transform this weird data into something more manageable.
For the weird header row, you can always specify that there is no header row and the connection manager should skip N rows.
Unfortunately, there's not much you can do about the parsing itself when there's embedded delimiters. Logically, you can look and see that Kansas City, MO was all one field but the parser is simplistic and breaks the string on the delimiter, baring an escape character.
For spaces not empty strings, the Import-Export wizard way would be to import to a table of all varchar columns, and then write an extract query performing whatever preferred magic to make it into a NULL NULLIF(RTRIM(MyCol), '') AS MyCol
Random notes for future readers
If you're on 2005 or 2008, then the only way to get the SSIS editor, BIDS (Business Intelligence Design Studio) is to have a copy of the SQL Server installation media. 2012+ Microsoft allows for the download and installation of the SQL Server Data Tools-BI edition without the need for a SQL Server installation.
The Import/Export wizard is available in all editions of SQL Server, including Express, but the limitation here is that you cannot save the generated SSIS package if you are using Express. All the other versions work fine.
Best Answer
You need to use bcp.exe or SSIS. Read Import and Export Bulk Data by Using the bcp Utility (SQL Server) and The Data Loading Performance Guide. Also, read How to Analyze SQL Server Performance to be able to tell why something is slow.
After you've read those, come back with a more actionable question, including structure of table, csv structure, repeatability of the job, access issues etc.