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

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