This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 45%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
Hello I have a flat .dat file as Source and am trying to import it into a SQL Server 2019 Database as a Table.
This .dat file FIELDTERMINATOR = '|' Vertical Pipe...and I've been trying different variations using bcp and BULK INSERT with FORMAT FILE (and tweaking things) but still unsuccessful.
Looks like my issue has something to do with the Final Column in the .dat file with LF (Line Feed?) as I've provided image shot here in my question.
The black bar (redacted column name) in the image is the Source .dat file desired "Final_Column_Name" (and similarly the other end Target destination Table).
But as you notice the .dat file also has an extra empty blank Column after this "Final_Column_Name"...am trying to skip/not include during import of this blank extra column or whatever it takes to get the import to succeed and then do clean up afterwards).
(1st line item is actual Column names header and the 2nd line and on wards below are the rows to be imported and notice no actual data in row 2 but still need to include in the overall import since the other columns in this row 2 does have data)
Like I mentioned something to do related to the final 2 columns at the end along with the row terminator in the format file...any thoughts on how I can get it to import like I mentioned at the very least and then clean up afterwards or straight up fix would be better if possible.
Thanks in advance.
Final_Column_dat_file_screenshot.jpg
Sorry if my screenshot provided here is duplicate as I tried to provide first but don't seem to see it and now tried again.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
While asking a question, you need to provide a minimal reproducible example:
Please edit your original question and provide the following:
(1) Source file minimal but sufficient sample.
(2) What you need to do, i.e. logic, and your code attempt trying to implement it.
(3) Destination db table CREATE table T-SQL statement to load sample data in #1 above.
All within the question as text, no images.
If the last column contains free-form text with line breaks, BULK INSERT might misinterpret these as row terminators, leading to data misalignment.
Also, try adjusting ROWTERMINATOR for LF/CRLF mismatches.
Best regards,
Cosmog
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
but still unsuccessful.
"Unsuccessful" means what in detail? Do you get any error message?