Skip to main content
Redhat Developers  Logo
  • Products

    Featured

    • Red Hat Enterprise Linux
      Red Hat Enterprise Linux Icon
    • Red Hat OpenShift AI
      Red Hat OpenShift AI
    • Red Hat Enterprise Linux AI
      Linux icon inside of a brain
    • Image mode for Red Hat Enterprise Linux
      RHEL image mode
    • Red Hat OpenShift
      Openshift icon
    • Red Hat Ansible Automation Platform
      Ansible icon
    • Red Hat Developer Hub
      Developer Hub
    • View All Red Hat Products
    • Linux

      • Red Hat Enterprise Linux
      • Image mode for Red Hat Enterprise Linux
      • Red Hat Universal Base Images (UBI)
    • Java runtimes & frameworks

      • JBoss Enterprise Application Platform
      • Red Hat build of OpenJDK
    • Kubernetes

      • Red Hat OpenShift
      • Microsoft Azure Red Hat OpenShift
      • Red Hat OpenShift Virtualization
      • Red Hat OpenShift Lightspeed
    • Integration & App Connectivity

      • Red Hat Build of Apache Camel
      • Red Hat Service Interconnect
      • Red Hat Connectivity Link
    • AI/ML

      • Red Hat OpenShift AI
      • Red Hat Enterprise Linux AI
    • Automation

      • Red Hat Ansible Automation Platform
      • Red Hat Ansible Lightspeed
    • Developer tools

      • Red Hat Trusted Software Supply Chain
      • Podman Desktop
      • Red Hat OpenShift Dev Spaces
    • Developer Sandbox

      Developer Sandbox
      Try Red Hat products and technologies without setup or configuration fees for 30 days with this shared Openshift and Kubernetes cluster.
    • Try at no cost
  • Technologies

    Featured

    • AI/ML
      AI/ML Icon
    • Linux
      Linux Icon
    • Kubernetes
      Cloud icon
    • Automation
      Automation Icon showing arrows moving in a circle around a gear
    • View All Technologies
    • Programming Languages & Frameworks

      • Java
      • Python
      • JavaScript
    • System Design & Architecture

      • Red Hat architecture and design patterns
      • Microservices
      • Event-Driven Architecture
      • Databases
    • Developer Productivity

      • Developer productivity
      • Developer Tools
      • GitOps
    • Secure Development & Architectures

      • Security
      • Secure coding
    • Platform Engineering

      • DevOps
      • DevSecOps
      • Ansible automation for applications and services
    • Automated Data Processing

      • AI/ML
      • Data Science
      • Apache Kafka on Kubernetes
      • View All Technologies
    • Start exploring in the Developer Sandbox for free

      sandbox graphic
      Try Red Hat's products and technologies without setup or configuration.
    • Try at no cost
  • Learn

    Featured

    • Kubernetes & Cloud Native
      Openshift icon
    • Linux
      Rhel icon
    • Automation
      Ansible cloud icon
    • Java
      Java icon
    • AI/ML
      AI/ML Icon
    • View All Learning Resources

    E-Books

    • GitOps Cookbook
    • Podman in Action
    • Kubernetes Operators
    • The Path to GitOps
    • View All E-books

    Cheat Sheets

    • Linux Commands
    • Bash Commands
    • Git
    • systemd Commands
    • View All Cheat Sheets

    Documentation

    • API Catalog
    • Product Documentation
    • Legacy Documentation
    • Red Hat Learning

      Learning image
      Boost your technical skills to expert-level with the help of interactive lessons offered by various Red Hat Learning programs.
    • Explore Red Hat Learning
  • Developer Sandbox

    Developer Sandbox

    • Access Red Hat’s products and technologies without setup or configuration, and start developing quicker than ever before with our new, no-cost sandbox environments.
    • Explore Developer Sandbox

    Featured Developer Sandbox activities

    • Get started with your Developer Sandbox
    • OpenShift virtualization and application modernization using the Developer Sandbox
    • Explore all Developer Sandbox activities

    Ready to start developing apps?

    • Try at no cost
  • Blog
  • Events
  • Videos

Why Windows and Linux line endings don't line up (and how to fix it)

May 6, 2021
Don Schenck
Related topics:
.NETLinuxKubernetes
Related products:
Developer Tools

Share:

    I recently wrote a few automated database-populating scripts. Specifically, I am running Microsoft SQL Server in a container in a Kubernetes cluster—okay, it's Red Hat OpenShift, but it's still Kubernetes. It was all fun and games until I started mixing Windows and Linux; I was developing on my Windows machine, but obviously the container is running Linux. That's when I got the gem of an error shown in Figure 1. Well, not so much an error as errant output.

    Weird line endings in SQL statement output.
    Figure 1: Errant output from an SQL statement.

    What in the world? Here's the CSV data I used to populate the table:

    1,Active
    2,Inactive
    3,Ordered
    4,Billed
    5,Shipped
    

    Here's the T-SQL code I used for the same purpose:

    BULK INSERT dbo.StatusCodes FROM '/tmp/StatusCodes.csv' WITH (FORMAT='CSV',FIELDTERMINATOR=',',KEEPIDENTITY);
    GO
    SELECT * FROM dbo.StatusCodes;
    GO
    

    What is going on here?

    TL;DR: Line endings

    It's the line endings. They are the issue.

    Specifically, Windows and Linux handle line endings differently. To understand why, we need to go back a ways in history.

    ASDFJKL

    Ever use a manual typewriter? Okay, okay ... enough of the "That's old!" jokes. Figure 2 illustrates.

    typewriter
    Image by <a href="https://pixabay.com/users/blende12-201217/?utm_source=link-attribution&amp;utm_medium=referral&amp;utm_campaign=image&amp;utm_content=1138293">Gerhard G.</a> from <a href="https://pixabay.com/?utm_source=link-attribution&amp;utm_medium=referral&amp;utm_campaign=image&amp;utm_content=1138293">Pixabay</a>
    Figure 2: What a manual typewriter looks like.

    The typewriter mechanism that holds the rubber cylinder is called the carriage because it carries the paper. (That rubber cylinder is technically known as a platen, but stay with me as I employ poetic license and use "carriage.")

    As you type, the carriage moves to the left. When you reach the edge of the paper, you use the big lever on the far left to return the carriage to the starting position; that is, you perform a carriage return. In addition, as the lever moves, it advances the paper up one line, which is known as a line feed.

    When you do both movements, you get "carriage return plus line feed," sometimes abbreviated to CRLF or CR/LF. You can move the carriage without feeding one line, and you can advance one line without moving the carriage. They are two distinct and separate actions, but anyone who has mastered the manual typewriter knows that they are typically done in one, swift, soulful, and athletic motion, akin to desktop gymnastics of the highest order. (Please excuse more poetic license as I romanticize about typing.)

    Teletype

    Meanwhile, over in the world of automation, the Teletype machine became very popular. This allowed the transmission of text around the world, across telephone lines. But long distance calls were expensive, so minimizing the time and data sent was paramount. So, it was decided that one and only one character would be used for a carriage return and line feed, the so-called new line character. You see it as "\n" in code. You paid for every byte, back then, so cutting costs was important.

    We're talking about 300 baud modems here, folks. Just think about that; 300 bits per second; three hundred. Now, we want gigabits everywhere.

    Back to line endings

    The reasons don't matter: Windows chose the CR/LF model, while Linux uses the \n model. So, when you create a file on one system and use it on the other, hilarity ensues. Or, in this case, two hours of debugging ending in madness and me contemplating a new career in woodworking.

    Quick fix for Linux and Windows line endings

    The quick fix for those incompatible line endings was very simple: I altered my T-SQL to include the ROWTERMINATOR specification, like this:

    BULK INSERT dbo.StatusCodes FROM '/tmp/StatusCodes.csv' WITH (FORMAT='CSV',FIELDTERMINATOR=',',ROWTERMINATOR = '\r\n',KEEPIDENTITY);
    GO
    SELECT * FROM dbo.StatusCodes;
    GO
    

    That works when uploading my CSV from my Windows machine. When uploading from my Linux machine, I use the following, where the ROWTERMINATOR is the simple new line character:

    BULK INSERT dbo.StatusCodes FROM '/tmp/StatusCodes.csv' WITH (FORMAT='CSV',FIELDTERMINATOR=',',ROWTERMINATOR = '\n',KEEPIDENTITY);
    GO
    SELECT * FROM dbo.StatusCodes;
    GO
    

    Simple, but unless you know about it, you either get weird results or some seemingly unrelated error messages. So, be advised. For example, if I try to use the Windows-specific command (where ROWTERMINATOR is "\r\n") in my Linux environment, I get the following error:

    Msg 4879, Level 16, State 1, Server mssql-1-h2c96, Line 2
    Bulk load failed due to invalid column value in CSV data file /tmp/StatusCodes.csv in row 1, column 2.
    Msg 7399, Level 16, State 1, Server mssql-1-h2c96, Line 2
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7330, Level 16, State 2, Server mssql-1-h2c96, Line 2
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
    Id statusCodeDescription
    ----------- ---------------------
    

    What does it all mean?

    The upshot is this: You might see some hiccups and weird behavior when you use a file in both Windows and Linux. Just be aware of it and you'll be fine.

    Visit my GitHub repository NetCandyStore for all of the code referenced in this article.

    Last updated: February 5, 2024

    Recent Posts

    • More Essential AI tutorials for Node.js Developers

    • How to run a fraud detection AI model on RHEL CVMs

    • How we use software provenance at Red Hat

    • Alternatives to creating bootc images from scratch

    • How to update OpenStack Services on OpenShift

    Red Hat Developers logo LinkedIn YouTube Twitter Facebook

    Products

    • Red Hat Enterprise Linux
    • Red Hat OpenShift
    • Red Hat Ansible Automation Platform

    Build

    • Developer Sandbox
    • Developer Tools
    • Interactive Tutorials
    • API Catalog

    Quicklinks

    • Learning Resources
    • E-books
    • Cheat Sheets
    • Blog
    • Events
    • Newsletter

    Communicate

    • About us
    • Contact sales
    • Find a partner
    • Report a website issue
    • Site Status Dashboard
    • Report a security problem

    RED HAT DEVELOPER

    Build here. Go anywhere.

    We serve the builders. The problem solvers who create careers with code.

    Join us if you’re a developer, software engineer, web designer, front-end designer, UX designer, computer scientist, architect, tester, product manager, project manager or team lead.

    Sign me up

    Red Hat legal and privacy links

    • About Red Hat
    • Jobs
    • Events
    • Locations
    • Contact Red Hat
    • Red Hat Blog
    • Inclusion at Red Hat
    • Cool Stuff Store
    • Red Hat Summit

    Red Hat legal and privacy links

    • Privacy statement
    • Terms of use
    • All policies and guidelines
    • Digital accessibility

    Report a website issue