3

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,,,,test@test.org,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

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.

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.

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.

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).

0

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.

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.