How to create a sql table from a csv file?

Question

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.

Your pseudocode is an excellent start. How do you think you can get the column list into a list that you can iterate over so that your foreach would work? — Nick.Mc
– Nick.Mc, Commented Apr 29, 2020 at 1:48
@Nick.McDermaid something like $csv = Import-CSV $csvFile; $csvHeaders = ($csv | Get-Member -MemberType NoteProperty).name; foreach($header in $csvHeaders) { ...etc...} so thats getting the headers, which is pretty straight forward, but how to dynamically specify "for the first $x columns, create table column as varchar type, and for the rest, real type." — Cataster
– Cataster, Commented Apr 29, 2020 at 1:55
Personally I would use a counter inside the foreach to decide which datatype should be emitted for each iteration. There's probably a smarter way though. — Nick.Mc
– Nick.Mc, Commented Apr 29, 2020 at 2:30
@Nick.McDermaid ya at this point im getting hopeless trying to find a good way to do it lol. I am looking through c# code since its possible to run c# code through powershell...i literally cant find any example online with powershell to create table from a file...i did find examples with c# though, but they have just 1 predefined data type, unlike my requirement which has 2 data types at least :/ — Cataster
– Cataster, Commented Apr 29, 2020 at 2:34

Nick.Mc · Accepted Answer · 2020-04-29 03:08:12Z

1

This is not a complete answer, just guidance, showing an inelegant brute force method that probably has syntax errors. There are PS guns that can probably reduce this to three lines.

Extending your pseudocode:

$csvfile = .\Fact.csv
$csv = Import-CSV $csvFile
$csvHeaders = ($csv | Get-Member -MemberType NoteProperty).name

$query="CREATE TABLE [dbo].[$csvfile.name]( "
$index=1
foreach ($column in $csvHeaders) {
  $query=$query+"[$column] "
  if($index -gt $varcharendposition) {$query=$query+"real, "} else {$query=$query+"varchar(50), "}  
  $index++
}
$query=$query + ") "

Query $query

This will be full of errors, but try it and troubleshoot each error or bug as t arises. Then you can post your answer and accept it.

answered Apr 29, 2020 at 3:08

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

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

1 Comment

Cataster Over a year ago

I ended up using Smo objects thanks to this post here: codykonior.com/2016/01/21/…

Collectives™ on Stack Overflow

How to create a sql table from a csv file?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related