How can i specify a datatype for a column in a datatable?

Welcome Forums General PowerShell Q&A How can i specify a datatype for a column in a datatable?

Viewing 5 reply threads
  • Author
    Posts
    • #226563
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      I am bulkcopying records from a csv file to a sql table. The sql table has columns that are varchar, and columns that are real datatype (based on the csv attributes we are given)

      Lets suppose that the first 7 columns are the Foreign Keys of varchar(100), and the rest of the 80+ columns are Real datatype.

      During the bulk copy, I used Out-DataTable function because apparently thats the most efficient way to bulk copy (especially with our files containing 1000’s of records).

      However, I am getting the following error:

      [/crayon]

      Now i wish the error could specify which column exactly, but based on my research, ive found that this could be related to the Datatype being presumed to be string type for all columns.

      Verifying with the following:  $column.DataType

      [/crayon]

      So the question is: how do i tell the Datatable to allow the first 7 columns to be string, but the rest of them real datatype?

      here is the code:

      [/crayon]

      Maybe something like this?

      PseudoCode:

      [/crayon]

       

      related

      • This topic was modified 4 months, 2 weeks ago by cataster16.
    • #226782
      Participant
      Topics: 13
      Replies: 1758
      Points: 3,153
      Helping Hand
      Rank: Community Hero

      Wrote this years ago, works well:

      Another option is to install SQL Mgmt and it will have the SQLPS Module. Then you can use Write-SqlTableData

      https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps

      The code can be as simple as:

      assuming that CSV Headers match.

    • #226785
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      Wrote this years ago, works well:

      PowerShell
      39 lines

      <textarea class=”ace_text-input” style=”opacity: 0; height: 17.9048px; width: 7.2381px; left: 52px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      $dtUsers = $CSVDataTable | Out-DataTable
      $sqlConnection = new-object System.Data.SqlClient.SqlConnection(“Data Source=$databaseServer;Initial Catalog=$databaseName;User Id=$databaseUserID;Password=$databasePassword”);
      $sqlConnection.Open()
      #Get the schema from the table
      $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $sqlCmd.Connection = $sqlConnection
      $sqlCmd.CommandText = “SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘$databaseTableName'”
      $dataTable = New-Object System.Data.DataTable
      $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $sqlAdapter.SelectCommand = $sqlCmd
      $sqlAdapter.Fill($dataTable)
      $buildTable = New-Object System.Data.SqlClient.SqlCommand
      $bc = new-object (“System.Data.SqlClient.SqlBulkCopy”) $sqlConnection
      $bc.DestinationTableName = “dbo.$databaseTableName”
      $bc.DestinationTableName
      $arrMap = $dtUsers.Columns | Select ExpandProperty ColumnName
      $arrDB = $dataTable | Select ExpandProperty Column_Name
      for ($idxDB=0;$idxDB -le ($arrDB.Length -1);$idxDB++) {
      “Looking for {0} in map data…” -f $arrDB[$idxDB]
      $idxMap = [array]::indexof($arrMap,$arrDB[$idxDB])
      if ($idxMap -ge 0) {
      “Mapping data source column index {2} for {3} to database index {0} for {1}.” -f $idxDB, $arrDB[$idxDB], $idxMap, $arrMap[$idxMap]
      $bc.ColumnMappings.Add((New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping($idxMap, $idxDB)))
      }
      else {
      “Skipping column map for {0}, not found in dataset” -f $arrDB[$idxDB]
      }
      }
      $bc.WriteToServer($dtUsers)
      $sqlConnection.Close()
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      Another option is to install SQL Mgmt and it will have the SQLPS Module. Then you can use Write-SqlTableData

      https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps

      The code can be as simple as:

      <textarea class=”ace_text-input” style=”opacity: 0; height: 17.9048px; width: 7.2381px; left: 45px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      Import-Csv $csvFile | Write-DataTable
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      assuming that CSV Headers match.

      Wait, I have SQLserver module installed. Man so this whole time I was naive about the write-sqltable function? Sigh…lol thank you though for bringing this to my attention

    • #227212
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      Wrote this years ago, works well:

      PowerShell
      39 lines

      <textarea class=”ace_text-input” style=”opacity: 0; height: 18px; width: 6.59781px; left: 51px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      $dtUsers = $CSVDataTable | Out-DataTable
      $sqlConnection = new-object System.Data.SqlClient.SqlConnection(“Data Source=$databaseServer;Initial Catalog=$databaseName;User Id=$databaseUserID;Password=$databasePassword”);
      $sqlConnection.Open()
      #Get the schema from the table
      $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $sqlCmd.Connection = $sqlConnection
      $sqlCmd.CommandText = “SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘$databaseTableName'”
      $dataTable = New-Object System.Data.DataTable
      $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
      $sqlAdapter.SelectCommand = $sqlCmd
      $sqlAdapter.Fill($dataTable)
      $buildTable = New-Object System.Data.SqlClient.SqlCommand
      $bc = new-object (“System.Data.SqlClient.SqlBulkCopy”) $sqlConnection
      $bc.DestinationTableName = “dbo.$databaseTableName”
      $bc.DestinationTableName
      $arrMap = $dtUsers.Columns | Select ExpandProperty ColumnName
      $arrDB = $dataTable | Select ExpandProperty Column_Name
      for ($idxDB=0;$idxDB -le ($arrDB.Length -1);$idxDB++) {
      “Looking for {0} in map data…” -f $arrDB[$idxDB]
      $idxMap = [array]::indexof($arrMap,$arrDB[$idxDB])
      if ($idxMap -ge 0) {
      “Mapping data source column index {2} for {3} to database index {0} for {1}.” -f $idxDB, $arrDB[$idxDB], $idxMap, $arrMap[$idxMap]
      $bc.ColumnMappings.Add((New-Object System.Data.SqlClient.SqlBulkCopyColumnMapping($idxMap, $idxDB)))
      }
      else {
      “Skipping column map for {0}, not found in dataset” -f $arrDB[$idxDB]
      }
      }
      $bc.WriteToServer($dtUsers)
      $sqlConnection.Close()
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      Another option is to install SQL Mgmt and it will have the SQLPS Module. Then you can use Write-SqlTableData

      https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps

      The code can be as simple as:

      <textarea class=”ace_text-input” style=”opacity: 0; height: 18px; width: 6.59781px; left: 44px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      Import-Csv $csvFile | Write-DataTable
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      assuming that CSV Headers match.

      soo i tried write-sqltable and it doesnt convert to Real if the datatype is Real :/

      Write-SqlTableData : The given value of type String from the data source cannot be converted to type real of the specified target column.

    • #228739
      Participant
      Topics: 0
      Replies: 1
      Points: 26
      Rank: Member

      I’m by no means an expert, so I’ll preface my suggestion with that…

      I frequently use the bulk copy program to write data from a csv to SQL Server.  I create the destination table as all nvarchar, write the data to that table (one line of code), and then cast/convert the columns that need to be in another format from that table and write or insert into the formatted table.  It may be a pain to write the SQL script since you have 80+ columns to convert, but it works.  You could use a temp table for the first step, but I just keep a table for the initial “dump” for each of my jobs, then truncate it at the end of the job.

      I don’t have a need to work with Real datatypes, so if you’ll have issues converting from nvarchar to Real, then this won’t work, but figured I would share a workaround in case it helps…

    • #231121
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      Rank: Major Contributor

      I’m by no means an expert, so I’ll preface my suggestion with that…

      I frequently use the bulk copy program to write data from a csv to SQL Server. I create the destination table as all nvarchar, write the data to that table (one line of code), and then cast/convert the columns that need to be in another format from that table and write or insert into the formatted table. It may be a pain to write the SQL script since you have 80+ columns to convert, but it works. You could use a temp table for the first step, but I just keep a table for the initial “dump” for each of my jobs, then truncate it at the end of the job.

      I don’t have a need to work with Real datatypes, so if you’ll have issues converting from nvarchar to Real, then this won’t work, but figured I would share a workaround in case it helps…

      ya thats one thing i considered, just making all my columns nvarchar, but then again, idk if real is compatible with Nvarchar so wouldnt know how easy/hard the conversion would be.

      nonetheless, i now have it figured out 🙂

Viewing 5 reply threads
  • The topic ‘How can i specify a datatype for a column in a datatable?’ is closed to new replies.