Advertisement
cwprogram

[Revised] CSV -> SQL Express Code

Jul 9th, 2011
349
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Function IsScriptBlock
  2. {
  3.     param($value)
  4.    
  5.     if($value -and $value.GetType() -eq [System.Management.Automation.ScriptBlock])
  6.     {
  7.         return $true;
  8.     }
  9.     else
  10.     {
  11.         return $false;
  12.     }
  13. }
  14.  
  15. Function DatabaseHandler
  16. {
  17.     param($connection_string, $destination_table,$data_table,$timeout)
  18.     $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connection_string)
  19.     $bulkCopy.DestinationTableName = $destination_table
  20.     $bulkCopy.BulkCopyTimeout = $timeout
  21.     $bulkCopy.WriteToServer($data_table)
  22. }
  23.  
  24. function MakeColumn($column_name,$data_type)
  25. {
  26.     $column = New-Object System.Data.DataColumn
  27.     $column.DataType = [System.Type]::GetType($data_type);
  28.     $column.ColumnName = $column_name;
  29.  
  30.     return $column
  31. }
  32.  
  33. function IntializeDataTable
  34. {
  35.     param($data_table,$column_data)
  36.    
  37.     foreach($column_specifier in $column_data)
  38.     {
  39.         $data_table.Columns.Add(
  40.             (MakeColumn $column_specifier.name $column_specifier.data_type))
  41.     }
  42. }
  43.  
  44. Function CsvHandler
  45. {
  46.     param($data_table,$column_data)
  47.     begin {
  48.         Write-Host "Processing Started:" (Get-Date)
  49.         $progress = 0
  50.         $chunksize = 10000
  51.     }
  52.     process {
  53.        
  54.         $row = $data_table.NewRow()
  55.         foreach($column_specifier in $column_data)
  56.         {
  57.             $name = $column_specifier.name
  58.             if( $column_specifier.ContainsKey("function") -and ( IsScriptBlock $column_specifier.function ) )
  59.             {
  60.                 $column_value = & $column_specifier.function $name $_
  61.             }
  62.             elseif ( $column_specifier.ContainsKey("fixed_value") )
  63.             {
  64.                 $column_value = $column_specifier.fixed_value
  65.             }
  66.             else
  67.             {
  68.                 $column_value = $_.$name
  69.             }
  70.            
  71.             $row[$name] = $column_value
  72.         }
  73.        
  74.         $data_table.Rows.Add($row)
  75.        
  76.         $progress++
  77.         if($progress % $chunksize -eq 0)
  78.         {
  79.             Write-Host $chunksize "records processed"
  80.         }
  81.     }
  82.     end {
  83.         Write-Host "Processing Ended:" (Get-Date)
  84.     }
  85. }
  86.  
  87. Function PopulateDatabaseFromCsv
  88. {
  89.     param($csv,$column_data,$connection_string,$destination_table,$timeout=30)
  90.    
  91.     $data_table = New-Object System.Data.DataTable($destination_table)
  92.     IntializeDataTable $data_table $column_data
  93.     Import-Csv $csv | CsvHandler $data_table $column_data
  94.     DatabaseHandler $connection_string $destination_table $data_table $timeout
  95. }
  96.  
  97. // Sample Usage
  98. $connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\TEMP\Maxmind.mdf;Integrated Security=True;User Instance=True"
  99. $csv = "C:\TEMP\GeoIPCountryWhois.csv"
  100. $destinationTable = 'geoip'
  101. $columnData = @(
  102.     @{ name = 'startip'; data_type = 'System.UInt32'; },
  103.     @{ name = 'endip'; data_type = 'System.UInt32'; },
  104.     @{ name = 'country_code'; data_type = 'System.String'; },
  105.     @{ name = 'country'; data_type = 'System.String'; }
  106. )
  107. PopulateDatabaseFromCsv $csv $columnData $connectionString $destinationTable 200
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement