Export .dat to Xml or readable file with help of .fmt

Question

I downloaded a database from http://opendb.lightspeedsystems.com/contentupdate/database.htm it was a zip archive. Anyway my question is, there are two files: virussignatures.dat and virussignatures.fmt. I've heard that I should extract the database as SQL by using BCP. The contents of virussignatures.fmt is:

8.0
11
1    SQLCHAR       2    64      "~~~~~"            1     VirusName  SQL_Latin1_General_CP1_CI_AS
2    SQLCHAR       2    32      "~~~~~"            2     VirusType           SQL_Latin1_General_CP1_CI_AS
3    SQLCHAR       2    32      "~~~~~"            3     AppSig              SQL_Latin1_General_CP1_CI_AS
4    SQLINT        0    4       "~~~~~"            4     SigStart            ""
5    SQLINT        0    4       "~~~~~"            5     SigEnd              ""
6    SQLCHAR       2    1024    "~~~~~"            6     Signature           SQL_Latin1_General_CP1_CI_AS
7    SQLBIT        1    1       "~~~~~"            7     Test                ""
8    SQLINT        0    4       "~~~~~"            8     CategoryNumber      ""
9    SQLINT        0    4       "~~~~~"            9     SourceNumber        ""
10   SQLDATETIME   0    8       "~~~~~"            10    TransactionTime     ""
11   SQLCHAR       2    50      ""                 11    KBID                SQL_Latin1_General_CP1_CI_AS

I want to turn the virussignatures.dat to an XML or an understandable TXT.

Could this be done using Vb.Net? If so how? Anyway, if not how should this be done?

Answer 1

Take a note of where your sqlserver db is (local or on a server). I assume local. If you don't have SqlServer installed yet download SqlExpress. That version does come with the client tools needed.

Create a database if you don't have one, I assume it is called virussignatures.

Create this table (run it in a query window in your (new) database)

CREATE TABLE [dbo].[Virusses](
  VirusName nvarchar(64) NULL,
  VirusType nvarchar(32) NULL,
  AppSig nvarchar(32) NULL,
  SigStart int NULL,
  SigEnd int NULL,
  Signature nvarchar(1024) null,
  Test bit null,
  CategoryNumber int null,
  SourceNumber int null,
  TransactionTime Datetime null,
  KBID nvarchar(50)
);
GO

Import data in your local sql db

bcp virussignatures.dbo.Virusses in virussignatures.dat -f virussignatures.fmt -T -S .

read: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Or alternatively from a New Query (rigth-click on your database in sql management studio)

BULK INSERT 
    Virusses 
      FROM 'c:\where\ever\your\file\is\virussignatures.dat' 
      WITH 
        ( FORMATFILE = 'c:\where\ever\your\file\is\virussignatures.fmt' );
GO

now you are ready to write the vb program based on this c# example to read the table in a DataSet and call WriteXml by implementing following code in a VB.NET program.

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Virussignature";
command.CommandType = CommandType.Text;
command.Connection = new SqlConnection("Your conect string here");
SqlDataAdapter da = new SqlDataAdapter(command); 
DataSet ds = new DataSet();
da.Fill(ds, "Virussignature");
StreamWriter xmlDoc = new StreamWriter("virus.xml"); 
ds.WriteXml(xmlDoc);
xmlDoc.Close();