Transact SQL :: Bulk Insert Arabic Data From CSV File To DB

Aug 5, 2015

/****** BULK INSERT  ******/
INSERT [Table01]
FROM 'C:empdata.csv'


I am using above code to insert csv file data which consist of arabic data as well.  Upload is successful however Arabic field data is uploaded with invalid characters and getting the following error Msg 4864, Level 16, State 1, Line 3...Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

View 15 Replies


Transact SQL :: Bulk Insert When Data File Is On Network Share

Dec 3, 2015

I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.) I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there. My SQL is in the form of:

Bulk Insert [table name] From '[server][share][filename]' With (FirstRow = 2, FormatFile='FormatFile.xml')

Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error:

Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file "[server][share][filename]" could not be opened. Operating system error code 5(Access is denied.).

I found this snip at

BULK INSERT (Transact-SQL)Security Account Delegation (Impersonation), which says, in part (emphasis mine):

To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation.

How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work. Later we tried constrained delegation and it still does not work.

I have verified the SPNs:

C:>setspn adsvc_sqlRegistered ServicePrincipalNames for CN=SVC_SQL,OU=Service Accounts,OU=Users,OU=ad domain,DC=ad,DC=local:        MSSQLSvc/        MSSQLSvc/        MSSQLSvc/        MSSQLSvc/
I have verified that my SQL connection is TCP and I am getting/using a Kerberos security token.
C:>sqlcmd -S,1433 -E1> Select dec.net_transport, dec.auth_scheme From sys.dm_exec_connections As dec Where session_id = @@Spid;2>
gonet_transport auth_scheme------------- -----------TCP KERBEROS(1 rows affected)1>

If I move the source file to a local drive (on the SQL server), all works fine, but I must be able to read from a file share?

View 8 Replies View Related

Transact SQL :: Format File For Bulk Insert

May 16, 2012

I need fmt(format ) file for below values


View 15 Replies View Related

Transact SQL :: CSV File - Bulk Insert Row Terminator

Nov 5, 2010

I have a CSV file that I am trying to bulk load into a temp table. The data in the file is all jumbled together, as in, there does not appear to be a row terminator. However, I do see a bunch of little rectangular boxes that I assume are the row terminators.

When I run the bulk insert, the data is treated as one string. For example... If I have 10 columns in the table, the 10 columns will be populated, but the remainder of the data is dumped into the last column.

Here are the row terminators I have used so far that haven't worked.


View 31 Replies View Related

Transact SQL :: How To Do Bulk Insert Into Temp Table From Text File

Sep 15, 2015

How do I do a bulk insert into a temp table from a text file. Text file looks like that:
ver_id TYPE
E57AB326-803C-436E-B491-398A255C919A 58
34D2A601-6BBA-46B1-84E1-3A805FDA3812 58
986E140C-62F1-48F1-B428-3571EBF00DA0 58

My statement looks like this:

CREATE TABLE [dbo].[tblTemp]([ver_id]  [nvarchar](255), [TYPE] [smallint]) 
BULK INSERT [dbo].[tblTemp]
FROM 'C:v2.txt'
I keep receiving errors.

The error I receive is: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (TYPE).

View 2 Replies View Related

Transact SQL :: Using Bulk Insert - Invalid Column Number In Format File Error

Jun 5, 2015

I try to import data with bulk insert. Here is my table:

CREATE TABLE [data].[example](
 col1 [varchar](10) NOT NULL,
 col2 [datetime] NOT NULL,
 col3 [date] NOT NULL,
 col4 [varchar](6) NOT NULL,
 col5 [varchar](3) NOT NULL,

[Code] ....

My format file:

1  SQLCHAR 0  10 "@|@" 2 Col2 ""
1  SQLCHAR 0  10 "@|@" 3 Col3 ""
2  SQLCHAR 0  6 "@|@" 4 Col4 Latin1_General_CI_AS

[Code] .....

The first column should store double (in col2 and col3) in my table

My file:

My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)

Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".

I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter

Removed this line from format file
1  SQLCHAR 0  10 "@|@" 2 Col2 ""
Nothing works ....

View 7 Replies View Related

Transact SQL :: How To Bulk Insert Rows From Text File Into A Wide Table Which Has 1400 Columns

Feb 3, 2010

we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008. 

We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns.  The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table? 

View 6 Replies View Related

Transact SQL :: BULK INSERT Not Importing Correct Data For Field?

May 14, 2015

I am using a BCP format file to import a CSV file. The file looks like the following:


The format file looks like the following:

1      SQLCHAR    0      0       """         0    ""
2      SQLINT       0      0       "",""     1   MROS
3      SQLINT       0      0       ""
"   2   MROF

When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02.  These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file.  The results was that the 01 became 12592 and the 02 became 12848.  where these numbers are coming from?

View 7 Replies View Related

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

Oct 12, 2007

i have a file which consists data as below,


Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.


but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this?


View 5 Replies View Related

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

Jun 29, 2015

I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
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, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".



View 5 Replies View Related

Bulk Insert From Txt File Less Data Than Columns

May 23, 2007

Hi, I´m trying to bulk insert files that looks like this:


each file can have up to 10 data fileds per line, and each file will have same number of data fileds in particular file, let´s say 3 like above. Second file could have let´s say 10 and that is maximum.

I read the file and insert data with fieldterminator in temp table from witch I insert data to other tables regarding some parameters inside.

Now problem is:
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
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, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

That is because I´m trying to insert 3 fields of data in temporary table which is made of 10 columns (It have to be 10 because next file could have 10 fileds of data). If the temp table has same number of columns like text file has data fields than it works.

What is solution for this problem?
Can I bulk insert NULL in columns for which I don't have data?

I can also import each line of text file to one column (with delimiter inside) but than I don´t know how to insert that data to correct tables or even to one table but to seperate data fields to columns with fieldterminator which is , in this case.

I'm new to SQL and I would apriciate any help.
Thank you

View 3 Replies View Related

Bulk Insert Into Table With More Columns Than Data Within File

Jun 17, 2007

Hey all

I have a bulk insert situation that would be nice to be able to pull off. I have a flat file with 46 columns that are to go into a table. The table, I want to have a 47th column to be updated later on by means of a stored proc saying if the import into the system was sucessful or not. I have the rowterminator set as '"' thinking that would tell SQL to begin on the next row, leaving the importstatus column null but i still receive an error.

First of all, is this idea possible within this insert statement. Secondly, if so, what would be the syntax to tell the insert statement to skip that particular column. It is the last column listed in the table so it just needs to start on the next row after it inserts the last bit of data in the flatfile.

If this is not possible, is it possible to bulk insert into a temp table?


View 1 Replies View Related

T-SQL (SS2K8) :: Ignore First Row In Data File In Bulk Insert?

Feb 17, 2015

I use bulk insert to fill a table

I use code bellow

bulk insert dbo.test
from 'c: est.txt'
with(FIRSTROW=2,FORMATFILE='c: est.xml'

but data inserted to table start from 3throw.

View 2 Replies View Related

Bulk Insert - Flat File Data Into Table

Mar 12, 2015

I am running a set of SQL statements on a SQL server, to insert flat file data into a SQL table. The flat file is already FTP'ed to the SQL server. I seem to be getting an error, which is possibly pointing to a permissions issue

The statements:

BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer]
FROM 'c:jedox_dailyjdcom4401.txt'

The error is :
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "c:jedox_dailyjdcom4401.txt" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 1815)

If it is permissions issue, how do I overcome this?

View 1 Replies View Related

Bulk Insert - Inserting Only One Column From Data File

Sep 29, 2007


I have a data file in the folloing format


I just wanted to insert only SubjectIds into my table 'Subjects' which has the follwing schama ignoring the classes
The row delimeter is "
" and the column delimeter is ' | '

Table Subjects

ID (Autoincrement)
SubjectId varchar(20)

Can any one provide the format file for doing this or suggest anyway to do this?
Please do note that the file may contain millions of records

Thank u

View 5 Replies View Related

Bulk Insert From Native Format Data File.

Dec 5, 2006

With "bcp MyDatabase.dbo.MyTable out C:MyFile.Dat -n -T" command line, I could get an exported data file. And I can also import this file  into MyTable using 'BULK INSERT MyDatabase.dbo.MyTable FROM 'C:MyFile.dat' WITH (DATAFILETYPE='native');' query statement.

Now, I want to make my own data file just like made by bcp above. Although I could make file of 'char' type, 'native' type file is needed for performance and other reasons. And the format file should not be used.

 Any one help?

View 5 Replies View Related

Skip The First Line Of The Data File - Bulk Insert

Oct 1, 2007

I have a data file and the contents of it are as follows

2 -- This is the header indicating the no of records in my files

The content of format file is as follows. This is to skip first column of the all the rows and get only Subs (i.e s1 and s2 )


1 SQLCHAR 0 100 "|" 0 ID ""

2 SQLCHAR 0 100 "
" 1 Subs ""

Here is my query to get all the Subs from my data file


FORMATFILE = 'FormatFile.fmt',


But this query retuns only s2 where i was expeting s1 and s2. The reason being is that the firts row i.e header doesn't follow the format
Can any one please let me know how to skip the first line in the data file and get the result as required


View 6 Replies View Related

BULK INSERT, Setting Static Data Using The Format File

Mar 2, 2004

Hello dbforums,

I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file?

The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid.

ALTER TRIGGER DICastRaw1hrInsertGuid
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK))

More Questions:

- The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ...

ALTER TRIGGER DICastRaw1hrInsertDate
ON Alphanumericdata.dbo.DICastRaw1hr
DECLARE @ID as integer
SELECT @ID = i.recordid from inserted i
UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE recordid = @ID

- I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this?


View 6 Replies View Related

Bulk Insert Fails. Column Is Too Long In The Data File

Jun 27, 2006


for testing purposes I'm inserting a flat file into a sql-server table using BULK INSERT unsig the following code:

BULK INSERT rsk_staging
FROM 'c: empulk
  WITH (
    DATAFILETYPE  = 'char',
    BATCHSIZE = 100000,
    ROWS_PER_BATCH = 1925604,

I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following error:

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

How can I resolve this problem?

EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.

View 1 Replies View Related

Csv File Import: Bulk Insert Data Conversion Error (type Mismatch)

Sep 27, 2004


Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.

column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int

pId has been set to primary key with auto_increment.

My csv file has 2 columns of data and it looks like follows:

program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"

Now i use BULK INSERT like this


to import data into my table in SQL server and it gives me this error

"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"

I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...

Please help me out guys and please post a snippet of code if you have.

Thank You.

View 2 Replies View Related

Bulk Insert Vs. Data Flow Task (different Row Results Using Flat File Source)

Nov 2, 2006

I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).

With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.

I used two different flat file sources and got the following:

Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows

Anyone have any guess as to why this is happening?

View 9 Replies View Related

SQL Server 2012 :: Unable To Load Data From Flat File To Table Using Bulk Insert Statement

Apr 3, 2015

I am unable to load data from flat file to sql table using bulk insert sql statement

My code:-

DECLARE @filePath VARCHAR(200)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'

[Code] .....

View 1 Replies View Related

Bulk Insert - Bulk Load Data Conversion Error

Jan 17, 2008

Im having some issues with bulk insert.

This is the table:

CREATE TABLE [dbo].[tmp_GA_status](

[GA_recno] [int] NOT NULL,

[GA_desc] [varchar](40) NULL


This is the file (unicode):

and this is the sql:

bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'

', DATAFILETYPE='widechar')

so yeah, pretty simple. But whatever I do I get this;

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).

So what am I doing wrong ?

View 13 Replies View Related

Transact SQL :: Bulk Insert 0 Rows Affected

May 15, 2015

I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected. 

BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt' 

The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option. 

View 7 Replies View Related

Transact SQL :: Additional Column With Bulk Insert?

Aug 11, 2015

Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.

View 2 Replies View Related

Transact SQL :: Bulk Insert From Remote Server?

Nov 20, 2015

SQL Server 2012

I want to be able to run the following command from SSMS (as an ad-hoc query).

BULK INSERT Database_Name.dbo.Table_Name FROM 'serverfile.txt' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0x0a', MAXERRORS = 0);

When I do I get:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "serverfile.txt" could not be opened. Operating system error code 5(Access is denied.).

I have full access to the file.I can do the same command successfully if the file is stored on a local drive on the server.

According to my DBA I can not run it with a remote file location because I don't have the SA permission. His solution is for me to create a job that runs the command. I have done so and the job works correctly.

Is he correct that there is no way for me to be able to run it from SSMS without SA permissions?

View 5 Replies View Related

XML Format File For Bulk Insert Of Text File With Fixed Length Columns

Jan 2, 2008

Hey All,

Similar to a previous post (, I am trying to import data into a SQL Table.

I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:

Example of Data

0124015Apple Crate 32.12

0124016Bananna Box 12.56

0124017Mango Carton 15.98

0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money

How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?

I don't know if it makes any difference, but I've been calling SQL from my code by doing this:

Code in C# Form

SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;

I am running Visual Studio C# Express 2005 and SQL Server Express 2005.

Thanks for your time,


View 1 Replies View Related

Bulk Insert From A .csv File

May 5, 2006

Hi folks,

I have a small problem - I am unable to load data from a .csv file into a table in SQL Server. Here is the command I am running:
BULK INSERT CCSProgramParticipation FROM 'c: est.csv'

Data in test.csv is the following format: (date fields can be blank)

What I see is the data does get loaded; however, data from the following row is getting inserted in the last field of a particular row (previous row) - it seems like the rowterminator is being ignored.

Has anyone encountered this issue? Please let me know your thoughts on this.

Thanks so much!


View 6 Replies View Related

Bulk Insert From CSV File

Oct 9, 2013

I am bulk inserting from a csv file using

FROM 'C:UsersRobsDocumentsSoccer2011-2012SC1.csv'

Works fine. Most of my csv files have the same number of columns, but some have 4 less. The files contain the same column headings as the full size ones(only 4 less). Is there a way when bulk inserting for sql to either skip these, or ignore the error.

View 2 Replies View Related

How To Bulk Insert From A Txt File?

Mar 28, 2008

Well hi here is my text file

and a table with Id , name, surname, email

the ID is a autoincrement thats why it gives me error any way to to skip this ID so let the sql create it automaticaly for every row?

View 3 Replies View Related


Jul 20, 2005

Hi there,I have some text files saved using a UTF-8 encoding. The "BULK INSERT"statment in Sql Server 2000 is failing with a column length error. Savingthe file as ASCII removes the problem. However, I would like to import filesin UTF-8 format. I understand that the BCP tool, when used from the commandline, can take an "-F UTF8" argument, which allows it to work. Can this bedone from the SQL> prompt with the BULK INSERT statement?Cheers,Tobin

View 2 Replies View Related

Bulk Insert Command With Dbf File

Dec 28, 2007

hi friends
i am using bulk insert command for txt files
but now i want to use bulk insert command for dbf files
so plz any one can tell me how to use this command for dbf files
thanx in advance.........

View 1 Replies View Related

Bulk Insert With Dynamic File Name

Feb 15, 2006

Hi everyone,I am trying to bulk insert some data from a csv file to a table. I can do it as part of a button on click event, but don't know how to do it using a stored procedure. This is what I have,ALTER PROCEDURE dbo.TestImportData    (
    @filename varchar(50)    )
    BULK INSERT dbo.[TestTable]
   FROM @filename
    /* SET NOCOUNT ON */
    RETURNI get the error message "Incorrect syntax near '@filename', Incorrect syntax near 'with'). What am I doing wrong? What should I do? Please help!

View 1 Replies View Related

Copyrights 2005-15, All rights reserved