Share via

importing DAT file

RJ 106 Reputation points
2023-08-24T16:17:12.8833333+00:00

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.
13,361 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2023-08-24T21:40:29.6633333+00:00

    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?