-2

I have a .csv file with the following sample data format:

REFID|PARENTID|QTY|DESCRIPTION|DATE
AA01|1234|1|1st item|null
AA02|12345|2|2nd item|null
AA03|12345|3|3rd item|null
AA04|12345|4|4th item|null

To load the above file into a table I am using below BCP command:

/bcp $TABLE_NAME in $FILE_NAME -S $DB_SERVER -t "|" -F 1 -U $DB_USERNAME -d $DB_NAME

What i am trying to look here is like below (adding sysdate instead of null from bcp) 

AA01|1234|1|1st item|3/16/2020
AA02|12345|2|2nd item|3/16/2020
AA03|12345|3|3rd item|3/16/2020
AA04|12345|4|4th item|3/16/2020

Update : I was able to exclude header with @Jamie answer by -F 1 option, but looking for some help on inserting date with bcp. Tried looking some old Q&A, but no luck so far..

dildeepak
  • 1,349
  • 2
  • 16
  • 34
  • The ".csv file" statement is meaning less. The file is a text file, that is all. It might have a .csv extention, but to the BCP program, might as well be .xyz... the extension means nothing. Further, the file is not a comma-separated file. It is delimeted by the pipe character "|". There are no commas. So, you see, it's not a "csv" file. It's just text that is delimeted by |. Next you cannot use the -t option and specify a format file (-f option). The -f overrides the -t option. Can you share the contents of your format file? When you say "no luck" what do you mean? what error? – jamie Mar 17 '20 at 00:07
  • Thanks @jamie.. formatter is working fine.. and also i was able to use -F 1 ... Although, i am still struggling for date part.. – dildeepak Mar 17 '20 at 00:26
  • This should have been submitted as a separate question. changing the title just hides the original question and solution. You should change this title back to its original value and mark this question as answered. Then submit a NEW question about defaults or using t-sql to get your value. – jamie Mar 19 '20 at 02:29

1 Answers1

1

To exclude a single header record, you can use the -F option. This will tell BCP which line in the file is the first line to begin loading from. For your sample, -F2 should work fine. However, your command has other issues. See comments.

There is no way to introduce new data using the BCP command as you stated. BCP cannot introduce a date value while copying data into your table. To accomplish this I suggest a default for your date column or to first load the raw data into a table without the date column then you can introduce the date value as you see fit in late processing.

jamie
  • 745
  • 4
  • 11
  • Thanks @jamie , i have updated my code as well as question.. And looking for some help on sysdate part,, as your suggestion for handling at last time will be feasible for my case, as the data once loaded to db will trigger another process. Any suggestion further please. – dildeepak Mar 17 '20 at 00:34
  • that is an entirely different question. You cannot get this data via bcp. I suggest submitting a different question about how to use defaults or how to update a column from null to include a date. – jamie Mar 18 '20 at 18:16