0

I am trying to do bulk insert throught a migration in C#, so far looks like this

    public override void Up()
    {
        Sql("BULK INSERT Test FROM 'C:\\test.csv' 
             WITH (
                   FORMATFILE = 'C:\\format.xml',
                   FIRSTROW = 2);"
             );
    }

test.csv

Id  Name    Birthday    Status
1   John    23-11-1984 00:00    OK
2   Peter   17-05-1986 00:00    N/A
3   James   18-03-1987 00:00    OK

format.xml

<?xml version="1.0"?>  
<BCPFORMAT   
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"   
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>  
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t"   MAX_LENGTH="12"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"   MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t"   MAX_LENGTH="30"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>  
  <ROW>  
    <COLUMN SOURCE="1" NAME="Id" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="3" NAME="Birthday" xsi:type="SQLDATETIM8"/>
    <COLUMN SOURCE="4" NAME="Status" xsi:type="SQLBIT"/>
  </ROW>
</BCPFORMAT>

Test Table definition

Id (PK, not null)
Name (nvarchar(max), null)
Birthday (datetime, null)
Status (bit, null)

After updatig database I encountered first error (date format for Birthday in CSV is DD-MM-YYYY HH:MM):

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (Birthday).

But if I change date format to YYYY-MM-DD HH:SS in the CSV file, it works.

Second issue is that for field 'Active' in CSV, I need to take the strings "OK" or "N/A" and in some way convert it and insert it in the table as Bit.

So at this point, my question is:

  • Is there any way to convert this data before insert it into the table?
Improve this question
4
  • 1
    See msdn. Use Mapping Class. You may need to override the class to add custom methods. msdn.microsoft.com/en-us/library/… Commented Mar 12, 2018 at 11:25
  • Take a look at DTS. I would just parse directly in C#, fix the data, and load. Commented Mar 12, 2018 at 11:37
  • maybe this might work too? stackoverflow.com/a/45217654/6668185 Commented Mar 12, 2018 at 11:50
  • Possible duplicate of Automated task Commented Mar 12, 2018 at 11:58
Related questions
1
bulk insert from csv to DB using Linq to sql
1
How to insert data from a CSV file into SQL Server?
34
Upload CSV file to SQL server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.