Load data of a pcap file and insert into a SQL Server table

Question

I have a file with extension .pcap and I want to INSERT all data of this file into a table in a SQL Server database.

I have tried to use BULK INSERT because I was using this to load .csv file and insert all data of csv file in a table. But now I want to load a .pcap file and insert data of this file into SQL Server database.

I am able to open that .pcap file in WireShark, but I want to load all data into a SQL Server database. Below is the code which I am trying to do this::

 CREATE TABLE #DataUpdation
 (
        Number BigInt
        ,Time_ VarChar(512)
        ,Source_ VarChar(512)
        ,Destination_ VarChar(512)
        ,Protocol_ VarChar(512)
        ,Length_ VarChar(512)
        ,Info VarChar(8000)
    )

    DECLARE @Query VARCHAR(Max)
    SET @Query=
     'BULK INSERT #DataUpdation
       FROM '+char(39)+'C:\Sample.pcap'+char(39)+'
       WITH
       (      
          FIELDTERMINATOR = '','',
          ROWTERMINATOR = ''\n'',
          FIRSTROW = 2 ,
          MAXERRORS =0 
       )'
    EXECUTE(@Query) ;

Here is the error :

Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 7. Verify that the field terminator and row terminator are specified correctly.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

I have googled it to get the field terminator of .pcap file but no luck...

Thanks in advance.

Answer 1

Following is the command to convert a pCap file to csv format:

tshark -T fields -n -r {the pathname of the capture file} -E separator=, -e {first field name} -e {second field name} ... >{the pathname of the output file}

Where {the pathname of the capture file} is the pathname of the capture file you're reading and {first field name}, {second field name} and so on are the names of the fields, and {the pathname of the output file} is the pathname of the output file.

Here is the final command of TShark to convert pCap file to CSV file format:

tshark -T fields -n -r C:\capture.pcap -E separator=, -e ip.src -e ip.dst >C:\output.csv

Now we have the pCap file in CSV format, it is now possible to Insert/Add this in sql server database table.

Note: tshark = C:\Program Files\Wireshark\tshark.exe