SQL BCP format file is not applying

SSIS and Import/Export
1

I'm using sql server 2017 and this is how my format file looks like
14.0
4
1 SQLCHAR 0 5 " " 1 cycle SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 2 "" 2 month SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 5 " " 3 year SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 5 "\r\n" 4 type SQL_Latin1_General_CP1_CI_AS

Table structure is as follows ,

CREATE TABLE TEST
(
CYCLE VARCHAR(5) NOT NULL,
MONTH VARCHAR(2) NOT NULL,
YEAR VARCHAR(4) NOT NULL,
TYPE VARCHAR(5) NOT NULL
)

And my bcp command is
bcp Sample..Test in D:\Test\201710\Customer.TXT -f D:\Test\bcpfmt\CUST.FMT -S -C -T
-c -t" " -r \n

Sample Data:
00005 051996 MOVE

My question is, I want to import data from file to table 'Test'. My sample data is as above.
'00005' -> value for the column 'cycle'
051996 -> value for the column 'month' and 'year'. It stores like '05' for 'month' and '1996' for 'year'
MOVE -> value for column 'Type'\

how can we do this in SQL server BCP. I have tried bcp format file, but the format file is not getting applied. Is there any setting for this? how to make the format file to work in sql bcp?

2

Try this. HTH

3

Thanks Jotorre.

I found the solution for my issue.

The only change i did is in my bcp command only. I have placed '-c' before '-f'. After replacing, bcp is working as per the format file

bcp Sample..Test in D:\Test\201710\Customer.TXT -c -f D:\Test\bcpfmt\CUST.FMT -S -C -T