Bulk Insert - (0 row(s) affected)

Question

I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the file. However, I have data in the file itself.

Sample CSV File (Header + One Line below header)

.CSV Header

changedate,deactivate,lockout,noemail,empno,orighire,salary,salut,fname,mi,lname,username,hstreet1,hstreet2,hcity,hstate,hcountry,hzip,busphone,busext,cellular,empemail,jobtitle,jobcode,jobgroup,orglevel1,orglevel2,orglevel3,orglevel4,orglevel5,company,perfdate,supervisor,misc1,misc2,misc3,misc4,active

.CSV Content

2014-04-03 00:00:00,NO ,NO ,NO ,1133,2014-04-03 00:00:00,1111.1111,,test,test,test,user.test,111 test way,,Sacramento,CA,USA,11111,,,,[email protected],test test,057,02,sss,261,SAC,,test,ttt,1911-11-11 00:00:00,1111,N,,test,0,NULL

.SQL

BULK
INSERT dbo.Archive
FROM 'C:\scripts\User.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n',
FIRSTROW = 2
)
GO

I tried to achieve this through ps and this was the outcome and code:

# Database variables
$sqlserver = "dbserver"
$database = "db"
$table = "tb"

# CSV variables
$csvfile = "C:\scripts\User.csv"
$csvdelimiter = ","
$firstRowColumnNames = $true

################### No need to modify anything below ###################
Write-Host "Script started..."
$elapsed = [System.Diagnostics.Stopwatch]::StartNew() 
[void][Reflection.Assembly]::LoadWithPartialName("System.Data")
[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

# 50k worked fastest and kept memory usage to a minimum
$batchsize = 50000

# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Integrated         Security=true;Initial Catalog=$database;"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize

# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable

# Open the text file from disk
$reader = New-Object System.IO.StreamReader($csvfile)
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
if ($firstRowColumnNames -eq $true) { $null = $reader.readLine() }

foreach ($column in $columns) { 
$null = $datatable.Columns.Add()
}

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {
$null = $datatable.Rows.Add($line.Split($csvdelimiter))
$i++; if (($i % $batchsize) -eq 0) { 
    $bulkcopy.WriteToServer($datatable) 
    Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
    $datatable.Clear() 
} 
} 

# Add in all the remaining rows since the last clear
if($datatable.Rows.Count -gt 0) {
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}

# Clean Up
$reader.Close(); $reader.Dispose()
$bulkcopy.Close(); $bulkcopy.Dispose()
$datatable.Dispose()

Write-Host "Script complete. $i rows have been inserted into the database."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()

ERROR

PS C:\scripts> C:\scripts\Import-CSVtoSQL.ps1 Script started... Exception calling "WriteToServer" with "1" argument(s): "Received an invalid column length from the bcp client for colid 38." At C:\scripts\Import-CSVtoSQL.ps1:51 char:2 + $bulkcopy.WriteToServer($datatable) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

Paul White · Accepted Answer · 2017-08-15 08:19:32Z

1

In the table I'm trying to import to, I have columns that are extra. i.e: Datestamp, batch number. Having more in the table than the csv file must be the issue. I imported to a temp table that has only the columns defined in the csv and that worked.

edited Aug 15, 2017 at 8:19

Paul White♦

96.1k30 gold badges442 silver badges691 bronze badges

answered Jun 15, 2015 at 16:35

Bytesized

811 gold badge1 silver badge11 bronze badges

Add a comment |

Paul White · Accepted Answer · 2017-08-15 08:24:01Z

1

The original question had \n\r as the row terminator. On Windows, a row terminator is typically \r\n, with the carriage return first and the linefeed second (for obscure but interesting historical reasons). Try that in your BULK INSERT command.

Next, I would next try loading the file in a hex editor to validate the exact content of the line breaks. For example, if the data is Unicode you may need \0\r\0\n as a separator, and/or the DATAFILETYPE ='widechar' flag. Note 0x0A is decimal 10, 0x0D is decimal 13: the ASCII codes for line feed (\n) and carriage return (\r), respectively.

edited Aug 15, 2017 at 8:24

Paul White♦

96.1k30 gold badges442 silver badges691 bronze badges

answered Jun 12, 2015 at 21:02

Jon of All Trades

5,9857 gold badges48 silver badges63 bronze badges

Add a comment |

Seeds · Accepted Answer · 2015-06-12 21:48:16Z

0

try adding ERRORFILE = 'file_name' to your with clause and see if an answer presents itself. (or an error at least.) Usually it will run up to MAXERRORS errors and stop even if there are good lines.

answered Jun 12, 2015 at 21:48

Seeds

1162 bronze badges

Add a comment |

RDFozz · Accepted Answer · 2017-08-04 14:36:33Z

0

Something that can block the import is also a too short field in the destination : for instance, a field of 50 characters, while there are 51 characters in the source. But in fact you will get a message for each line, telling about an invalid character (not really explicit I feel).

edited Aug 4, 2017 at 14:36

RDFozz

11.8k4 gold badges25 silver badges38 bronze badges

answered Aug 4, 2017 at 13:56

Gloops

1

Add a comment |

Paul White · Accepted Answer · 2017-08-15 08:26:05Z

You can either:

Create a temp table, whether session-, instance-, or schema-based, to capture the data; or
Create a view that simply contains the columns that will map 1-1.

Best solution

Have a table with all the living and meta data you need for the records:

CREATE TABLE [STAGE].[File](
    [RowID] [bigint] IDENTITY(1,1) NOT NULL,
    [AUDIT_BatchUID] [uniqueidentifier] NULL,
    [FileID] [uniqueidentifier] NULL,
    [RowData] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Create a view of that table with only the fields you need to be "recipients" of the data from the file.

CREATE VIEW [STAGE].[vw_File]
AS
SELECT        RowData
FROM            STAGE.[File]

Bulk Insert into the View. Mine happens to be dynamic SQL-based but the idea is there.

declare @stmt varchar(max)
select @stmt = '
    bulk insert stage.vw_File
    from ''' + @fileandpath + '''
    with (
        FirstRow = ' + @firstRow + ',
        RowTerminator = ''' + @rowTerminator + '''
    )
'

Sanitize the table data with the appropriate meta-data after the bulk insert.

Stack Exchange Network

Bulk Insert - (0 row(s) affected)

5 Answers 5

Best solution

Your Answer

Hot Network Questions

Bulk Insert - (0 row(s) affected)

5 Answers 5

Best solution

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions