I am trying to read a csv file in my SQL Server Script. In SAS, I can read it using the infile option. (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm)

I know that I can achieve the same thing in SQL Server using BULK INSERT. (https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017) What I can't figure out is some of the equivalent option in SQL.

Specifically, in SQL I want to achieve the functionality of Missover, DSD, FirstObs and LRECL provided by SAS. I am confident that FirstObs equivalent is Firstrow, I am not sure about Missover and LRECL and I cannot figure out DSD all together.

Any help/suggestion much appreciated.

enter image description here


As per MSSQLTIPS documentation Transferring data from SAS to SQL Server and back SQL Server data typing is much richer than SAS, translating SQL Server values for use with SAS can be challenging. Even the topic of representing and processing missing data values is substantially different between SQL Server and SAS. SQL Server represents missing data values with NULL values, but SAS has a minimum of two ways of representing missing values: a period (.) for numeric missing values and a blank space (' ') for character missing values.

As DSD (delimiter-sensitive data) tells SAS to treats two consecutive delimiters as a missing value and removes quotation marks from character values.

Whereas SQL Server represents missing data values with NULL values.

For further your ref here & here

  • I see, so I can use KEEPNULLS to represent the functionality of both the missover and DSD? Also, is there a way to keep the first row of CSV as column name when I am reading CSV data in SQL server using BULK INSERT? – T.H. Nov 5 '18 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.