By ronen ariely on
Some report generated by third party application export data which do have a consistent format. Each line come in different format. Those application use an export's format that design on-the-fly, for each record and each value in the record separately. The format is set by the value itself.
For example if there is data in the field then it will export the data inside a quotation mark, but if there is no data then the data will be blank and without a quotation mark. Moreover some application do not use 'data type' when generating a report. If there is data and the data fit as numeric then again some applications might not use any quotation mark and in the same field on different record a data that do not fit as numeric will be export in quotation mark. We can think about a single CSV file which a specific column is exporting in 6 different formats. In order to use bulk insert directly we have to make sure that all the data is consistent with one format. So how do we do it?!?