Search code examples
c#sqlbcp

How do I import a csv file with one column defaulting?

I have a table with 6 columns, one of which is a date default value. I want to import a 5-column CSV file, letting that date default. I get the error Invalid character value for cast specification.

I also used a format file, but it doesn't help: number of table and csv file column are not matching.

How can I fix this?

Solution

  • Create a View with the columns that match the data file, then BCP into the View. Make sure any other columns in the table will allow Null values and/or have Default values.

    If your still having issues:

    You may need a format file to tell bcp the file to table (or view) mapping BCP Format Files

    I would generate the format file, and edit it to see what BCP thinks the mapping is. BCP may be mapping one of your CSV file columns to the wrong field, or its getting thrown off in some other way.

    Just modify the file to map the correct CSV columns to the correct table or view columns and you should be good.