Function to create SQL TABLE does not works well using Powershell

Question

Hope you are well

I'm going to explain my situation

I need to get the values from a Excel Sheet (in a shared folder) to export it and then create a SQL Table in my instance. After of this I will execute a Stored Procedure (I already created) in order to create extended properties for a specific schema on tables,columns and rows.

By the moment I was able to get the data from excel sheet (using powershell) and saved in a variable but the problem is that I needed to add one column called 'SchemaName' because the Stored Procedure need to finds only values with the same schema name. (In the table will exist many values with differences schema names ) So in order to move forward this. I added a column with the schema name required but even if the output of the variable is correct when I try to create the table pop up an error or just import data without the column 'SchemaName'

Here is my code:


$DataSource= '\\servername\temp\test.xlsx'    -sharedfolder path
$cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=YES';Data Source=c:\temp\test.xlsx;Mode=Share Deny Write;Jet OLEDB:Engine Type=37;";
$cnStr
$cn = New-Object System.Data.OleDb.OleDbConnection $cnStr;
$cn.Open()

$cmd = $cn.CreateCommand()

$cmd.CommandText = "SELECT * FROM [Sheet1$]"
$rdr = $cmd.ExecuteReader();

$dt = new-object System.Data.DataTable

$dt.Load($rdr)


$Database_Name = 'DBTest'
$Table_Name = 'Tbltest'
$Schema_Name = 'Schematest'


$dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name

$dt | write-dbadbtabledata -sqlinstance 'servername' -databasename $Database_Name -table $table_name -autocreatetable

Example of how looks the sheet

Rol	Member	Description
Admin	name + email	Description of the rol
Data Lead	name + email	Description of the rol
Arquitect	name + email	Description of the rol
DBA	name + email	Description of the rol
QA	name + email	Description of the rol

Also I tried with other functions o methods to create the table but the mainly reason that I believe is the columns seems that is added to the variable but in reality not.

Thanks in advance

Why do you want a NoteProperty type when datatable member properties are Property type? Why not just use $dt.Columns.Add('Schema_Name')? — AdminOfThings
– AdminOfThings, Commented Sep 2, 2021 at 13:14
Because I want the same value for all the rows. I don't why but I still can not create a table with the schema name added, just added the rest of the values but not the schema name. Also I used the -passthru command but same results. — SakZepelin
– SakZepelin, Commented Sep 3, 2021 at 10:42

score 2 · Accepted Answer · 2021-09-02 13:55:40Z

2

Let's unpack at this line:

$dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name

$dt by itself just outputs the object. In this case, were sending it down the pipeline to the next command.

add-member adds the member to the "thing" going down the pipeline, and passes that along. But in this case, you don't give it anywhere to go... The pipeline ends there and there's to assignment back into a new object. When you run it at the PowerShell prompt, I'll get output to your window, but not saved anywhere.

If you want to modify the $dt object you'll have to explicitly send it there, just like you do setting other variable/object values:

$dt = $dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name

You could also just skip that assignment and send it right on down the pipeline:

$dt | add-member -membertype noteproperty "Schema_Name" -Value $Schema_Name -PassThru | write-dbadbtabledata -sqlinstance 'servername' -databasename $Database_Name -table $table_name -autocreatetable

edited Sep 2, 2021 at 13:55

answered Sep 2, 2021 at 12:00

user1948808

Sign up to request clarification or add additional context in comments.

2 Comments

AdminOfThings Over a year ago

$dt | add-member does modify $dt. Add-Member outputs nothing through the pipeline without -PassThru parameter.

SakZepelin Over a year ago

Probably that was why still not working for me. Because I also tried to use a new variable with the same values but the function finally just imported data without schema_name

AdminOfThings · Accepted Answer · 2021-09-03 13:13:39Z

I apologize upfront for not being able to do the Excel part because that just takes too much setup. However, if we start from the DataTable object, the following works:

Set up DataTable with data as if read from an xlsx:

# Create a DataTable
$dt = [data.datatable]::new()
# Add columns to DataTable
$dt.columns.add('Role')
$dt.columns.add('Member')
$dt.columns.add('Description')
# Add rows to DataTable
$row = $dt.rows.add()
$row.Role = 'Admin'
$row.Member = 'john smith [email protected]'
$row.Description = 'The top guy'
$row = $dt.rows.add()
$row.role = 'Data Lead'
$row.Member = 'jon jones [email protected]'
$row.Description = 'The toughest'

Now $dt is a DataTable with rows of data and columns Role,Member, and Description.

$dt

Output:

Role      Member                       Description
----      ------                       -----------
Admin     john smith [email protected] The top guy
Data Lead jon jones [email protected]  The toughest

Let's add the new column with the same value for all rows:

$dt.Columns.Add('Schema_Name',[String],"'Schematest'")
$dt

Output:

Role      Member                       Description  Schema_Name
----      ------                       -----------  -----------
Admin     john smith [email protected] The top guy  Schematest
Data Lead jon jones [email protected]  The toughest Schematest

Now let's insert the data into a SQL table:

# Create SQL table with data
$dt | Write-DbaDbTableData -SqlInstance localhost -Database 'test' -Table 'Tbltest' -AutoCreateTable
# Query table for data
Invoke-DbaQuery -SqlInstance localhost -Database 'test' -Query 'SELECT * FROM Tbltest'

Output:

Role      Member                       Description  Schema_Name
----      ------                       -----------  -----------
Data Lead jon jones [email protected]  The toughest Schematest
Admin     john smith [email protected] The top guy  Schematest

Thank you very much For the moment, the solution I have used was to export it as a csv file and then import it again and create the table but seems that change the format code and the final text . I mean: Original: Test text New data: !est !ext With your answer that's problem will not occur again :)

Collectives™ on Stack Overflow

Function to create SQL TABLE does not works well using Powershell

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related