It seems like you need to clean up your delimiters and there are generally two:
1. Row delimiter
2. Column delimiters
Your row delimiter seems to be a comma (,) but your column delimiters seem to be mixed between a colon (
and a semi colon (
which I suspect is why you're having an issue attempting to split this into a proper columns and records. If this is your issue then:
1. Standardise all column delimiters by using a text editing programme (Notepad etc). I usually use the Replace function to perform this quickly. My suggestion is to use pipe (|) delimiters.
2. Standardise your row delimiters.
3. Attempt to import data using the SQL Import/Export wizard to import to target table
4. If 3. fails then use BCP to perform the Import into a new SQL table (This rarely fails)
5. Using the Insert Into...Select from move the data from the new SQL table to the original target table.
6. Step 5 might require some data transformation.
I generally find it easier to work with data once the data is in SQL Server.
I hope this helped.