How to skip first column from the CSV file while uploading it into SQL Server using BCP through ruby script

user_14785567 0
Viewed 286
rubyscript.rb

require 'tiny_tds'  
require 'csv'

#For connecting into mssql database
def establish_connection
    @client = TinyTds::Client.new username: 'username', password: 'password', host: 
    'hostid', database: 'database_name', azure: false
    puts("Connection secured")
end


#For creating a table
def create_table
        sql = "CREATE TABLE ABBTesting102(first_name VARCHAR(20), email VARCHAR(20));"
        @data=@client.execute(sql)
        puts("Table created Successfully")
    end


#For Importing to  mssql using bcp

def bcp_importing
    puts("Inside bcp importing function")
    sql_query = ' 
    BULK INSERT ABBTesting102   
       FROM {students.csv}   
       WITH (FORMATFILE = {format_file.fmt});'

    @datas = @client.execute(sql_query)
    puts("Importing Successfully Completed")
end

format_file.fmt

14.0  
2  
2       SQLCHAR       0       70       ","      1     first_name         ""  
3       SQLCHAR       0       70       ","      2     email         ""  


Students.csv

       id,first_name,email  
       1,sam,sam@gmail.com
       2,Emma,emma@gmail.com
       3,Max,Max@gmail.com

This is the sample data. When I tried with this rubyscript, the data is not being inserted. What I'm trying to do is that skip the first column while inserting into the SQL Server using BCP. I tried inserting without using BCP and format file and it seems to be inserting, but I'm trying to insert this student.csv while using the BCP. I want to skip the id column while inserting it into SQL Server and I don't want the id to be inserted. The SQL Server credentials given are dummy. Can someone help me insert this CSV into a SQL Server table using BCP while skipping the first column which is the identity column in ruby.

ruby-on-rails-5 bcp ruby sql-server
1 Answers

I don't have any SQL Server to play with at hand, so it's a guess, but your FMT file should look more like this:

14.0
3
1       SQLCHAR        0       7       ","     0       id              ""
2       SQLCHAR        0       70      ","     1       first_name      ""
3       SQLCHAR        0       70      "\r\n"  2       email           ""

Note: The row Terminator might be wrong, it could be "\n" (UNIX) instead of "\r\n" (Windows)

Let's see the Description of this format:

enter image description here

The Server column order seems to do what you want:

Server column order: ...
... To prevent a column in the table from receiving any data from the data file, set the server column order value to 0.

What I didn't find out is how to get rid of the header (first line) of your CSV, it doesn't seam possible with bcp

user_33877160
Related