How to load data from multiple csv files to multiple tables according to filename

 #Read csv file 

#open connection to database using bulkcopy

#convert array to data table

#bulkload data into table

#note: column sequence and data types should match

$ServerName='DESK-HCD8L\INDLA'

$DatabaseName='RAJ_IND_Analysis'

function Get-Type 

{ 

    param($type) 

 

$types = @( 

'System.Boolean', 

'System.Byte[]', 

'System.Byte', 

'System.Char', 

'System.nvarchar',

'System.Datetime', 

'System.Decimal', 

'System.Double', 

'System.Guid', 

'System.Int16', 

'System.Int32', 

'System.Int64', 

'System.Single', 

'System.UInt16', 

'System.UInt32', 

'System.UInt64') 

 

    if ( $types -contains $type ) { 

        Write-Output "$type" 

    } 

    else { 

        Write-Output 'System.String' 

         

    } 

}

function Out-DataTable 

{ 

    [CmdletBinding()] 

    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 

 

    Begin 

    { 

        $dt = new-object Data.datatable   

        $First = $true  

    } 

    Process 

    { 

        foreach ($object in $InputObject) 

        { 

            $DR = $DT.NewRow()   

            foreach($property in $object.PsObject.get_properties()) 

            {   

                if ($first) 

                {   

                    $Col =  new-object Data.DataColumn   

                    $Col.ColumnName = $property.Name.ToString()   

                    if ($property.value) 

                    { 

                        if ($property.value -isnot [System.DBNull]) { 

                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)") 

                         } 

                    } 

                    $DT.Columns.Add($Col) 

                }   

                if ($property.Gettype().IsArray) { 

                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 

                }   

               else { 

                    $DR.Item($property.Name) = $property.value 

                } 

            }   

            $DT.Rows.Add($DR)   

            $First = $false 

        } 

    }  

      

    End 

    { 

        Write-Output @(,($dt)) 

    } 

 

} #Out-DataTable

$FileExtension = '*csv*.*'#you can use wildcards here for name and for extension

$SearchInfolder = 'D:\CSVMetaData\Ora2pg_reports\'

$FileNames=Get-ChildItem -Path $SearchInfolder -Filter $FileExtension -Recurse | %{$_.FullName}

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($FileNames)

foreach($Files in $FileNames)

{

$file=$Files

$FileName=[System.IO.Path]::GetFileNameWithoutExtension($Files)

$TblName="["+$FileName.Replace(".","_")+"]"

#write-output $TblName

$cn = new-object system.data.SqlClient.SQLConnection("Data Source="+$ServerName+";Integrated Security=SSPI;Initial Catalog="+$DatabaseName+"");

#Write-output $fileName

$cn.Open()

$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn

$bc.BatchSize = 10000;

$bc.BulkCopyTimeout = 1000

$bc.DestinationTableName = $TblName

#$bc.DestinationTableName = "["+ $fileName.Replace(".","_") +"]"

Write-Output $bc.DestinationTableName

$data = Import-Csv $file | Out-DataTable

$bc.WriteToServer($data)

}

How to create multiple tables from multiple csv files according to filename

 $ServerName='DESK-HCD8L\INDLA'

$DatabaseName='RAJ_IND_Analysis'

$FileExtension = '*csv*.*'#you can use wildcards here for name and for extension

$SearchInfolder = 'D:\CSVMetaData\Ora2pg_reports\'

$FileNames=Get-ChildItem -Path $SearchInfolder -Filter $FileExtension -Recurse | %{$_.FullName}

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($FileNames)

foreach($Files in $FileNames)

{

#$FileNamess=Get-ChildItem -Path $searchinfolder -Filter $fileExtension -Recurse | %{$_.FullName}

#$fileNa =  [System.IO.Path]::GetFileNameWithoutExtension($FileNamess)

#$Tbl =$fileNa.Replace(".","_")

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = "Server='$ServerName';Database='$DatabaseName';trusted_connection=true;"

$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Header = (Get-Content $Files | Select-Object -First 1).Split(',')

$CleanHeader = @()

foreach($H in $Header){

$CleanValue = $H -Replace '[^a-zA-Z0-9_]',''

$CleanHeader += $CleanValue

}

$FileName=[System.IO.Path]::GetFileNameWithoutExtension($Files)

$TblName=$FileName.Replace(".","_")

write-output $TblName

$StagingTableName = $TblName

$sql = @("IF EXISTS (SELECT name FROM sys.tables WHERE name = '$StagingTableName') DROP TABLE [$StagingTableName];")

#$sql = @()

$sql += ("CREATE TABLE dbo.[$StagingTableName]($($CleanHeader[0]) VARCHAR(255)")

$CleanHeader[1..$CleanHeader.Length] | ForEach-Object {$sql += ",[$_] VARCHAR(255)"}

$sql += ");"

$Command.CommandText = $sql

$Command.ExecuteNonQuery()

$Connection.Close();

#Write-output $sql

}