How to conver the Excel(with data) file into sql file using powershell script with one time header?

How to conver the Excel(with data) file into sql file(data with queries to execute after)  using powershell script?

Hi,

I have a excel.xlsx file.

Now i need to convert this file into .sql file

I done but i got header column script two times in the .sql file.

Let me know the reason?

FYI code given below excel to sql file:

if([System.IntPtr]::Size -ne 4) {
 Write-Host -foregroundcolor red "This script must be run in the x32 version of PowerShell"
 Write-Host -foregroundcolor red "C:\windows\syswow64\WindowsPowerShell\v1.0\powershell.exe"
 #Write-Host -foregroundcolor red "C:\windows\system32\WindowsPowerShell\v1.0\powershell.exe" #this is 64-bit
 exit
}

function Safe-SQL($value) {
 $value = $value.Trim()
 $value = $value.TrimStart("'")
 $value = $value.TrimEnd(".")
 $value = $value.Trim()
 $value = $value.Replace("'", "''")
 return $value
}

function Remove-Diacritics($value) {
 $normalized = $value.Normalize([Text.NormalizationForm]::FormD)
 $sb = new-object Text.StringBuilder
 $normalized.ToCharArray() | % {
  if( [Globalization.CharUnicodeInfo]::GetUnicodeCategory($_) -ne [Globalization.UnicodeCategory]::NonSpacingMark) {
   [void]$sb.Append($_)
  }
 }
 return $sb.ToString()
 #.Normalize(NormalizationForm.FormC))
}

get-childitem ..\xxxxx |? { $_.Name.Contains(".xlsx") } |% {
 Write-Host $_.fullname
 
 $filename = ("..\script\xxx-" + $_.Name + ".sql")
 Write-Host $filename
 "" | Out-File $filename
 "USE [xpn]" | Out-File -append $filename
 "GO" | Out-File -append $filename

 $conn = new-object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + $_.fullname + "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1'")
 $conn.Open()
 $sheets = $conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables, $null);
 $sheets.Rows |% {
  #Write-Host ($_["TABLE_NAME"])
 }

 $cmd = new-object System.Data.OleDb.OleDbCommand("select * from [xxx_Company_Details$]", $conn)
 $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
 $partners = new-object System.Data.DataTable
 $da.Fill($partners) | out-null
 $conn.Close()

 $partners.Rows |% {
 
  
  
  #$safename = (Safe-SQL $_[1].ToString())
  #$uid = $_[3].ToString()
  
  $safecountryenname = (Safe-SQL $_[0].ToString())
  $safecountrylowerenname = $safecountryenname.ToLower()

  $name = $_[1].ToString()  

  $chtypevolume = $_[2].ToString()
  $chtypevalue = $_[3].ToString()
  
  $chseg = $_[4].ToString()
  
  #$chseg = $chseg.Split(";#")
  $sql += "`n`t[chsegmentationid] = (select id from [xxxxx] where [enname] = '" + (Safe-SQL $chseg[0]) + "'),"
  
  $chtype = $_[32].ToString()
  $chtype = $chtype.Split(";#")
  $sql += "`n`t[chtypeid] = (select id from [ChType] where [enname] = '" + (Safe-SQL $chtype[0]) + "'),"

  $chseg = $_[33].ToString()
  $chseg = $chseg.Split(";#")
  $chseg |% {
    if($_ -ne "") {
     $sql += "`n"
     $sql += "`n"
     $sql += "insert into [xxxxx] (eid, Chid) "
     $sql += "`nselect (select [id] from [xxxxx] where [uniqueidentifier] = '$uid'), (select id from ChSegmentation where enname like '%"+(Safe-SQL $_)+"%')"
  $sql += "`n"     
    }
   }

  $chtype = $_[32].ToString()
  $chtype = $chtype.Split(";#")
  $chtype |% {
    if($_ -ne "") {
     $sql += "`n"
     $sql += "`n"
     $sql += "insert into [xxxxxx] (eid, chtypeid) "
     $sql += "`nselect (select [id] from [xxxx] where [uniqueidentifier] = '$uid'), (select id from student where enname = '"+(Safe-SQL $_)+"')"
  $sql += "`n"     
    }
   }

  $sql | Out-File -append $filename

 }
}


November 12th, 2014 7:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics