locked
CSV to SQL selective import script RRS feed

  • Question

  • Hey guys and girls,

    I have been attempting to make a script which will take specific columns from a CSV file specified by the user and import them into a SQL database. I have got quite far and even have it importing information (with a few quirks) correctly however I all it does so far is it displays the column names from the CSV and the SQL table in question, it then asks the user which columns map to which in the table, the reason for this is because the columns may not be named the same in each csv as they are coming from many different sources which we cannot standardise.

    What I am having issues with is there is a case where the user will not want a column and I am not sure how to allow the script to handle this, I was thinking of using an IF statement to say if the users input is blank then the value is null but I think that this might try and put a null Value into the table.

    I have put the script in below, any advice would be appreciated :)

    Also I am using this script with an CSV import module I got from here: https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-fa339046

    ##IMPORTANT This script depends on a Powershell module called Import-CsvToSQL It can be downloaded from here https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-fa339046
    
    
    ##SQL database information
    $sqlserver = "Server\Instance" 
    $database = "TEST" 
    $table = "DataIngest" 
    
    #checks the file directory and prompts the user to pick which file they want to use
    Get-ChildItem -Path C:\csv
    $csvname = read-host -prompt 'Which file would you like to upload?'
    import-csv "c:\csv\$csvname" -Delimiter "|" | select -First 1 | Format-table
    
    
    #Reads and displayes the Column names of the target CSV file
    $UploadFieldArray = New-Object system.collections.ArrayList
    import-Csv "c:\csv\$csvname" -Delimiter "|" | gm -membertype noteproperty | select name   | ForEach-Object{
    $counter = 1
    Write-Host "================ UploadFields ================"}{
    $UploadFieldArray += $_
    Write-Output "$($counter) $($_.name)"
    $counter += 1
    }
    
    #Polls SQL Server and returns Column names for the target table
    $GlassboxFieldArray = New-Object System.Collections.ArrayList
    Invoke-Sqlcmd -Query "Use glassbox select COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Items' AND TABLE_SCHEMA='DEVICES'" -ServerInstance LWSKBST21\GLASSBOX | select COLUMN_NAME | ForEach-Object{
    $counter = 1
    Write-Host "================ Glassbox Fields ================"}{
    $GlassboxFieldArray +=$_ 
    Write-Output "$($counter) $($_.COLUMN_NAME)" 
    $counter += 1
    }{Write-Host "================================================"}
    
    
    
    
    #Collects the results from above and prompts the user to assign and "Pair" the Column names together
        $ResultArray = New-Object System.Collections.ArrayList
        $UploadFieldArray  | ForEach-Object{
            $input = Read-Host "Please Select Glassbox Field Number Equating to Upload Field $($_.name)"
            #$varColumns+= '"' + $input + "',"
            $ResultArray += $GlassboxFieldArray[$input]
            Write-Host $GlassboxFieldArray[$input-1].Name
            Write-Output "$($GlassboxFieldArray[$input-1])"
    
     
        }
     {Write-Host "================================================"}
    
     ##Informational for debugging only This displays which columns the code thinks you want 
    $spot = 0
    ForEach-Object{
        Write-Output $UploadFieldArray[$spot].Name
        #Write-Output "$($GlassboxFieldArray[$spot])"
        $spot += 1
    }
    
    ## creates a new CSV file using the input from the user to lift set columns
    Import-csv -path C:\csv\$csvname -Delimiter "|" | select $GlassboxFieldArray[0..$GlassboxFieldArray.Length].COLUMN_NAME | ConvertTo-CSV -Delimiter ";" -NoTypeInformation | % {$_ -replace '"',""} | Out-File C:\csv\Temp.csv -fo -en ascii
    
    ## Uses Import-cslTosql powershell Module to Import the newly made CSV File
    Import-CsvToSql -Csv C:\csv\Temp.csv -SqlServer $sqlserver -database $database -table $table -firstrowcolumn -Delimiter ";"

    Thanks 

    Grant 

    Tuesday, November 7, 2017 2:58 PM

Answers

  • You are overcomplicating this.

    Use SQLBulkCopy and just prompt the user for column mappings and load.

    Here is a script that has all parts except the prompt for column names.

    $csvfile='.\MyCsv.csv'
    $sqlTable='MyDataTable'
    $DataSource='MyServer\MyInstance'
    $DataBase='db1'
    
    $ConnectionString ='Data Source={0}; Database={`}; Trusted_Connection=True;' -f $DataSource,$DataBase
    $csvDataTable = Import-CSV -Path $csvfile | Out-DataTable
    $bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
    $bulkCopy.DestinationTableName=$sqlTable
    $bulkCopy.WriteToServer($csvDataTable)
    


    We can add the mappings like this:

        # create the BC object
        $sqlbc=[System.Data.SqlClient.SqlBulkCopy]$sqlConnection
        $sqlbc.DestinationTableName=$tableName
        
        # add all columns - you can add as few  as you like.
        for($i=0; $i -lt $rdr.FieldCount;$i++){
            $fname=$rdr.GetName($i)
            Write-Host $fname -ForegroundColor green
            [void]$sqlbc.ColumnMappings.Add($fname, $fname)
        }
        
        # write all of the data to the table
        $sqlbc.WriteToServer($rdr)
    

    If you don't specify columns all columns are mapped by name.

    The $fname is duplicated but can be "source name" and "destination name"

    Just prompt user for column name mappings.


    \_(ツ)_/

    • Marked as answer by Grant Colgan Tuesday, November 7, 2017 4:17 PM
    Tuesday, November 7, 2017 3:29 PM

All replies

  • You are overcomplicating this.

    Use SQLBulkCopy and just prompt the user for column mappings and load.

    Here is a script that has all parts except the prompt for column names.

    $csvfile='.\MyCsv.csv'
    $sqlTable='MyDataTable'
    $DataSource='MyServer\MyInstance'
    $DataBase='db1'
    
    $ConnectionString ='Data Source={0}; Database={`}; Trusted_Connection=True;' -f $DataSource,$DataBase
    $csvDataTable = Import-CSV -Path $csvfile | Out-DataTable
    $bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
    $bulkCopy.DestinationTableName=$sqlTable
    $bulkCopy.WriteToServer($csvDataTable)
    


    We can add the mappings like this:

        # create the BC object
        $sqlbc=[System.Data.SqlClient.SqlBulkCopy]$sqlConnection
        $sqlbc.DestinationTableName=$tableName
        
        # add all columns - you can add as few  as you like.
        for($i=0; $i -lt $rdr.FieldCount;$i++){
            $fname=$rdr.GetName($i)
            Write-Host $fname -ForegroundColor green
            [void]$sqlbc.ColumnMappings.Add($fname, $fname)
        }
        
        # write all of the data to the table
        $sqlbc.WriteToServer($rdr)
    

    If you don't specify columns all columns are mapped by name.

    The $fname is duplicated but can be "source name" and "destination name"

    Just prompt user for column name mappings.


    \_(ツ)_/

    • Marked as answer by Grant Colgan Tuesday, November 7, 2017 4:17 PM
    Tuesday, November 7, 2017 3:29 PM
  • Well that is much simpler :)

    If you don't mind me asking what exactly is the section which adds the columns doing, I am still very much new to this whole powershell game.


    Tuesday, November 7, 2017 3:51 PM
  • If no mappings are specified all columns whose names match are automatically mapped.  If you map programmatically you can choose the source and destination columns.   This is the part you would change to use user mappings.


    \_(ツ)_/

    Tuesday, November 7, 2017 5:16 PM