Cannot bulk load because the file could not be read. Operating system error code (null).

First create a test .CSV file ( c:\test.csv) add IP_adresses on one column and another column.

Example  test.CSV...

comp_name,IP_in,IP_out,speed
HP2003,11.215.25.100,95.145.35.5,fast
HP2004,11.215.25.101,95.145.35.5,slow
HP2005,11.215.25.102,95.145.35.5,fast

--create table in SQL (pubs, adventureworks, etc.)
CREATE TABLE CSV_data (
Track_No int IDENTITY(1,1),
Computer [char] (10),
IP_from [char] (15),
IP_to [char] (15),
Performance [char] (10)
)

Import to SQL using openrowset for CSV...

SELECT *
FROM    
OPENROWSET (
'MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\',
'Select * from test.csv'
) AS Results

You will notice that the IP_Adress is incomplete.
I tried different field types (text, varchar, etc.)

A solution to this problem is BULK INSERT...

BULK INSERT CSV_Data
      FROM 'C:\test.csv'
      WITH
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n',
         FIRSTROW=1
      )

BULK INSERT makes it hard to insert new fields on the fly.


OPENROWSET has a BULK option that uses a fromat file.

SELECT *
FROM    
OPENROWSET (
BULK N'C:\test.csv',
FORMATFILE = 'c:\test.fmt')
SINGLE_BLOB
) AS Results

format file test.fmt content...
9.0
4
1       SQLCHAR       0       10      ","      1     comp_name        SQL_Latin1_General_Cp437_BIN
2       SQLCHAR       0       15      ","      2     IP_in            SQL_Latin1_General_Cp437_BIN
3       SQLCHAR       0       15      ","      3     IP_out           SQL_Latin1_General_Cp437_BIN
4       SQLCHAR       0       10      "\r\n"   4     speed            SQL_Latin1_General_Cp437_BIN

The problem now is that the OPENROWSET (BULK...) Option gives me the error...

Cannot bulk load because the file could not be read. Operating system error code (null).

You can try it with my notes above.