importing DAT file

RJ 106 Reputation points

Hi there,

Need help on understanding how a BCP command loads a dat file with no headers smoothly to a target table structure but cant do with import function, cant read the file via notebook, or SSMS import.

im having SQL server 2019 and trying to import a dat file. Some dat files are large. when BCP command is used, some files got loaded fine to predefined target tables even though when i tried to open the dat file, it looked like below. however some files did not load via BCP. So I was trying to troubleshoot without using BCP command. Tried using import function, --> import data -> flat file --> the file does not have headers (since we already have a predefined table) but wondering why i see jebrish special characters. any idea? how to handle and load this?

also any recommendation on how to trouble shoot a row which may have data issue on it? i dont have any other ETL tool. its just a direct import.

partial table structure

column1 - int

column2 - real

column3 - real

column4 - varchar(50) (Male / Female)

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,633 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP

    The file you are looking at may be binary.

    Unfortunately, only saying "DAT files" is not going to help much. You need have some documentation of the file format. A .DAT file can be anything.

    BCP can read binary files - but really only if the files have been exported from SQL Server. For instance, strings must be prefixed with one, two or four bytes in length. Null-terminated strings won't do.

    Maybe the files you were able to import with BCP were text files?