none
Upload CSV file to SQL with changing column data type on upload

    Question

  • Hi,

    I am new to VB and using Visual Studio 2015, can someone get a good advice on how can I upload csv file to SQL server with opportunity to change columns. I tried CSV reader - but get my head around it, is weird complex. If anyone can provide a detailed advice, will save my day.

    Thank you,

    Julie

    Tuesday, March 14, 2017 12:55 PM

Answers

  • I mean change data type for example varchar 50 then change on upload to varchar 255

    I'm not sure why you would need to change the type. The type should be determined by the destination column on the SQL Server side. Here is an example that I have. Maybe it will help as a starting point:

            Dim SQLConnectionString As String = "Data Source=(local);" & _
                                "Initial Catalog=Northwind;" & _
                                "Integrated Security=SSPI"
    
            ' Open a connection to the AdventureWorks database.
            Using SourceConnection As SqlConnection = _
               New SqlConnection(SQLConnectionString)
                SourceConnection.Open()
    
                ' Perform an initial count on the destination table.
                Dim CommandRowCount As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.Orders2;", _
                    SourceConnection)
                Dim CountStart As Long = _
                   System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                Console.WriteLine("Starting row count = {0}", CountStart)
    
                ' Get data from the source table as a TextDataReader.
                Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & "C:\Documents and Settings\...\My Documents\My Database\Text" & ";" & _
                    "Extended Properties=""Text;HDR=No"""
    
                Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
    
                Dim TextCommand As New OleDbCommand("SELECT * FROM Orders#txt", TextConnection)
                TextConnection.Open()
                Dim TextDataReader As OleDbDataReader = TextCommand.ExecuteReader(CommandBehavior.SequentialAccess)
    
                ' Open the destination connection. In the real world you would 
                ' not use SqlBulkCopy to move data from one table to the other   
                ' in the same database. This is for demonstration purposes only.
                Using DestinationConnection As SqlConnection = _
                    New SqlConnection(SQLConnectionString)
                    DestinationConnection.Open()
    
                    ' Set up the bulk copy object. 
                    ' The column positions in the source data reader 
                    ' match the column positions in the destination table, 
                    ' so there is no need to map columns.
                    Using BulkCopy As SqlBulkCopy = _
                      New SqlBulkCopy(DestinationConnection)
                        BulkCopy.DestinationTableName = _
                        "dbo.Orders2"
    
                        Try
                            ' Write from the source to the destination.
                            BulkCopy.WriteToServer(TextDataReader)
    
                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
    
                        Finally
                            ' Close the AccessDataReader. The SqlBulkCopy
                            ' object is automatically closed at the end
                            ' of the Using block.
                            TextDataReader.Close()
                        End Try
                    End Using
    
                    ' Perform a final count on the destination table
                    ' to see how many rows were added.
                    Dim CountEnd As Long = _
                        System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                    Console.WriteLine("Ending row count = {0}", CountEnd)
                    Console.WriteLine("{0} rows were added.", CountEnd - CountStart)
                End Using
            End Using


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Julz90 Tuesday, March 14, 2017 2:45 PM
    Tuesday, March 14, 2017 2:01 PM
  • Thank you so much, yes file have a column headers, data is different format: varchar and int and mixed 1234, 5678 - this one I will need to treat as Varchar.

    Thanks,

    Julie

    If you want to define the column headers and data types for a .csv file you can also use a schema.ini file:

    https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Julz90 Tuesday, March 14, 2017 2:45 PM
    Tuesday, March 14, 2017 2:24 PM

All replies

  • You can use the SqlBulkCopy Class to import to SQL Server, but I'm not sure what you mean by "change columns". I think I would need to know a bit more detail about your text file (e.g. does it have column headers, does it use a schema.ini file, etc.).

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 14, 2017 1:49 PM
  • I mean change data type for example varchar 50 then change on upload to varchar 255
    Tuesday, March 14, 2017 1:55 PM
  • Thank you so much, yes file have a column headers, data is different format: varchar and int and mixed 1234, 5678 - this one I will need to treat as Varchar.

    Thanks,

    Julie

    Tuesday, March 14, 2017 1:58 PM
  • I mean change data type for example varchar 50 then change on upload to varchar 255

    I'm not sure why you would need to change the type. The type should be determined by the destination column on the SQL Server side. Here is an example that I have. Maybe it will help as a starting point:

            Dim SQLConnectionString As String = "Data Source=(local);" & _
                                "Initial Catalog=Northwind;" & _
                                "Integrated Security=SSPI"
    
            ' Open a connection to the AdventureWorks database.
            Using SourceConnection As SqlConnection = _
               New SqlConnection(SQLConnectionString)
                SourceConnection.Open()
    
                ' Perform an initial count on the destination table.
                Dim CommandRowCount As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.Orders2;", _
                    SourceConnection)
                Dim CountStart As Long = _
                   System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                Console.WriteLine("Starting row count = {0}", CountStart)
    
                ' Get data from the source table as a TextDataReader.
                Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & "C:\Documents and Settings\...\My Documents\My Database\Text" & ";" & _
                    "Extended Properties=""Text;HDR=No"""
    
                Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
    
                Dim TextCommand As New OleDbCommand("SELECT * FROM Orders#txt", TextConnection)
                TextConnection.Open()
                Dim TextDataReader As OleDbDataReader = TextCommand.ExecuteReader(CommandBehavior.SequentialAccess)
    
                ' Open the destination connection. In the real world you would 
                ' not use SqlBulkCopy to move data from one table to the other   
                ' in the same database. This is for demonstration purposes only.
                Using DestinationConnection As SqlConnection = _
                    New SqlConnection(SQLConnectionString)
                    DestinationConnection.Open()
    
                    ' Set up the bulk copy object. 
                    ' The column positions in the source data reader 
                    ' match the column positions in the destination table, 
                    ' so there is no need to map columns.
                    Using BulkCopy As SqlBulkCopy = _
                      New SqlBulkCopy(DestinationConnection)
                        BulkCopy.DestinationTableName = _
                        "dbo.Orders2"
    
                        Try
                            ' Write from the source to the destination.
                            BulkCopy.WriteToServer(TextDataReader)
    
                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
    
                        Finally
                            ' Close the AccessDataReader. The SqlBulkCopy
                            ' object is automatically closed at the end
                            ' of the Using block.
                            TextDataReader.Close()
                        End Try
                    End Using
    
                    ' Perform a final count on the destination table
                    ' to see how many rows were added.
                    Dim CountEnd As Long = _
                        System.Convert.ToInt32(CommandRowCount.ExecuteScalar())
                    Console.WriteLine("Ending row count = {0}", CountEnd)
                    Console.WriteLine("{0} rows were added.", CountEnd - CountStart)
                End Using
            End Using


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Julz90 Tuesday, March 14, 2017 2:45 PM
    Tuesday, March 14, 2017 2:01 PM
  • Thank you so much, yes file have a column headers, data is different format: varchar and int and mixed 1234, 5678 - this one I will need to treat as Varchar.

    Thanks,

    Julie

    If you want to define the column headers and data types for a .csv file you can also use a schema.ini file:

    https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Julz90 Tuesday, March 14, 2017 2:45 PM
    Tuesday, March 14, 2017 2:24 PM
  • Thank you so much, I will try this code now.
    Tuesday, March 14, 2017 2:45 PM