How to specify for the first n columns a data type for creating a sql table?

Welcome Forums General PowerShell Q&A How to specify for the first n columns a data type for creating a sql table?

Viewing 0 reply threads
  • Author
    Posts
    • #223437
      Participant
      Topics: 39
      Replies: 109
      Points: 623
      Rank: Major Contributor

      Given a .csv file, Fact.csv, that lets say has 7 columns

      The ultimate goal is to have something like this:

      script.ps1 Fact.csv 3 4 

      in which the first 3 columns are for sure a varchar type, while the rest of the columns (4) are real type.

      How do I loop through the headers of the .csv file (which can contain data but we don’t want to import the data, just the headers for the column names of the table), and create the table (the name of the .csv file, e.g. Fact) along with the table columns dynamically, with the column types as specified above by the numbers in a database?

      pseudocode:

      $csvfile = .\Fact.csv
      $csv = Import-CSV $csvFile
      $csvHeaders = ($csv | Get-Member -MemberType NoteProperty).name
      
      Function Query($Query) {
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI" 
      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
      $SqlCmd.Connection = $SqlConnection 
      $SqlCmd.CommandText = $Query 
      $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
      $SqlAdapter.SelectCommand = $SqlCmd 
      $DataSet = New-Object System.Data.DataSet 
      $a=$SqlAdapter.Fill($DataSet)
      $SqlConnection.Close() 
      $DataSet.Tables[0] }
      
      foreach ($column in $csvHeaders | where 1st $x columns = varchar and remaining columns = real) {
      Query "CREATE TABLE [dbo].[$csvfile.name](
          [column1] [varchar](100) NOT NULL,
          [column2] [varchar](100) NOT NULL,
          [column3] [varchar](100) NOT NULL,
          [column4] [real] NULL,
          [column5] [real] NULL,
          [column6] [real] NULL,
          [column7] [real] NULL
      ) ON [PRIMARY]"
      
      }

      Even better, we don’t have to specify the 4 if there is a way to tell Powershell that only the first x amount of columns are varchar, while the rest are real.

      Related

      
      
      
      
      • This topic was modified 1 month ago by cataster16.
Viewing 0 reply threads
  • You must be logged in to reply to this topic.