sql bcp utility -in with xlsx ( Excel ) file

Question

How to use bcp utility to import data from xlsx to sql database. When i tried to import it throws error saying 'String data, right truncation'. Do i need to specify any format or delimiters option explicitly? If I try to export the data from database as xlsx file and import that back to DB it works fine. but the exported file is not properly formatted as i try to open it with office excel it show up in some weird format.

Answer 1

Don't use the xlsx file as it is, but save it in another format.

1. You may save the xlsx as a comma separated or tab separated file.

2. Specify the delimiter on the command line like this with a comma:

   bcp MySchema.dbo.[My Table] in MyCreatedCSVFile.csv -T -c -t,
   

One caveat: you may have your delimiter in your data file, in which case you will have to change your data or select a different delimiter.