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:
"01","02"
The format file looks like the following:
6.0
2
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
ADVERTISEMENT
Aug 5, 2015
USE TEST
GO
/****** BULK INSERT ******/
BULK
INSERT [Table01]
FROM 'C:empdata.csv'
[code]....
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
View Related
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/SQLQA.ad.local:1433 MSSQLSvc/SQLDev.ad.local:1433 MSSQLSvc/SQLQA.ad.local MSSQLSvc/SQLDev.ad.local
I have verified that my SQL connection is TCP and I am getting/using a Kerberos security token.
C:>sqlcmd -S tcp:SQLQA.ad.local,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
Oct 10, 2007
Hi,
I have a data file which consists of data as below,
4
PPU_FFA7485E0D||
T_GLR_DET_11||
While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table,
here is my query iam using to insert the data using bulk insert.
BULK INSERT TABLE_NAME FROM FILE_PATH
WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')
Can any one help on this.
Thanks,
-Badri
View 7 Replies
View Related
Nov 2, 2015
I have around 100 XL Files in a folder ,i want to import all the files dynamically and load all the data in a single table in sql server 2008. Without using SSIS i want to query using openrowset.
View 11 Replies
View Related
Oct 22, 2007
HI,
I am having trouble importing a flat file that was extracted from an AS400 server, into a SQL 2005 DB table using Bulk Insert. This file contains a column (field) that is of a Packed Decimal data type. Data in all other fields is displayed normally when viewing this file in a text editor such as Note Pad or Text Pad, but this one field comes up with unknown encoding: squares, thick vertical lines, basically strange characters and no numeric data.
Does anyone have any experience dealing with file of this sort?
Any insight would be appreciated.
Thanks.
View 14 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
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):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', 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
Jul 11, 2005
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies
View Related
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'
WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=' ',ROWTERMINATOR='
',FIRSTROW=172)
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
May 16, 2012
I need fmt(format ) file for below values
“Stuid”,”Stuname”,”Class”,”DOJ”,”English”,”Math”,”Science”
"S1","Ram","10/31/2011,Monday",40,32,50
"S2","Bala","10/31/2011,Monday",50,45,69
"S3","Sam","10/31/2011,Monday",74,78,79
"S4","Jon","10/31/2011,Monday",65,58,89
"S5","Jos","10/31/2011,Monday",41,25,69
"S6","Jim","10/31/2011,Monday",74,41,41
"S7","Jack","10/31/2011,Monday",98,57,47
"S8","Sate","10/31/2011,Monday",87,73,45
"S9","Brb","10/31/2011,Monday",47,89,65
"S10","Jom","10/31/2011,Monday",14,100,47
View 15 Replies
View Related
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
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
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.
,
,
,
, CRLF,
View 31 Replies
View Related
Nov 14, 2006
I am trying to perform a bulk insert in SQL Query analyzer. This is the Schema for my table:
Taxonomic_Units Table:
INT 4 tsn
CHAR 1 unit_ind1
CHAR 35 unit_name1
CHAR 1 unit_ind2
CHAR 34 unit_name2
CHAR 7 unit_ind3
CHAR 35 unit_name3
CHAR 7 unit_ind4
CHAR 35 unit_name4
CHAR 1 unnamed_taxon_ind
CHAR 12 usage
CHAR 50 unaccept_reason
CHAR 40 credibility_rtng
CHAR 10 completeness_rtng
CHAR 7 currency_rating
SMALLINT 2 phylo_sort_seq
DATETIME 8 initial_time_stamp
INT 4 parent_tsn
INT 4 taxon_author_id
INT 4 hybrid_author_id
SMALLINT 2 kingdom_id
SMALLINT 2 rank_id
DATETIME 4 update_date
CHAR 3 uncertain_prnt_ind
I use the following SQL Statement to BULK INSERT:
BULK INSERT itis.taxonomic_units
FROM '<dir path to input file>/taxonomic_units.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|',
KEEPIDENTITY,
KEEPNULLS
)
Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement:
50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996||
The error is:
Server: Msg 4864, Level 16, State 1, Line 1Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).
Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
View 3 Replies
View Related
Dec 5, 2007
i want to import/copy a varchar field numeric data in to number field pls suggest the solution
one thing more can i convert field type of a table how?
jto it
View 5 Replies
View Related
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])
GO
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
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:
10.0
7
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:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
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
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)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
View 5 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
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?
Thanks,
-Badri
View 5 Replies
View Related
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
Nov 3, 2007
Hi
I am currently developing my first database driven application and I have stumbled over some quite simple issue. I'll describe my database design first:
I have one table named images(id (identity), name, description) and one table named albums (id, name, description). Since I'd like to establish a n:n connection between these, I defined an additional table ImageInAlbum (idImage, idAlbum). The relation between these tables works as expected (primary keys, foreign keys appear to be ok).
Now I'd like to insert data via a stored procedure in sql server 2005 and I'm not sure how this procedure will look like.
To add a simple image to a given album, I am trying to do the following:
* Retrieve name, description from the UI
* Insert a new row into images with this data
* Get the ID from the newly created row
* Insert a new row into "ImageInAlbum" with the ID just retrieved and a fixed Id from the current album.
I know how I would do the first two things, but I am not used to Stored Procedures syntax yet to know how to do the other things.
Any help is appreciated ... even if it means telling me that I am doing something terribly wrong
View 9 Replies
View Related
Apr 18, 2008
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
View 8 Replies
View Related
Sep 21, 2006
Hello--
I'm importing data from an Access table and, of course, one field is a primary key. This field needs to be an autonumber. The problem is the data I'm importing isn't sequential.
Can I import the data, then alter the table to make the column increment or is there a way to create the table and make the field increment, but import non-sequential data?
HELP!
Thanks,
grimey
View 2 Replies
View Related
Nov 6, 2007
Hi,I'm trying to import data to my database on the live server from my local server. However when I do this it doesn't seem to be importing the properties for the fields in the tables. How can I import the properties of the fields too?Thanks,Curt.
View 1 Replies
View Related
Mar 15, 2006
hi Dear All
How can i import data in SQL Server 2000 which contains the value of date field from 1800 yrs to till now
eg. 15/12/1860, 10/10/1896, 10/10/2006
but if i change the year of date value to 19?? then it's imported.
anybody can help me please!
Thanks in advance
Rahman
View 3 Replies
View Related
Aug 9, 2015
I’m retrieving Yahoo quotes into my database and have run into an issue when the dates sometimes change format in the csv file retrieved.
I am retrieving yahoo quotes via powershell, then running a package to import data to my table. This generally works expect when the yahoo date format changes.
In the yahoo csv file, the dates normally come through in dd/mm/yyyy format. I find when a quote is old the format changes to mm/dd/yyyy, just for that particular quote.
When this happens, the package fails because the quote date format does not match my destination table format. i.e. mm/dd/yyyy vs dd/mm/yyyy
When this occurs, I would like to skip the records in mm/dd/yyyy format altogether and have the rest of the quotes imported.
One approach I can think of is to import the dates as a text type and do some data validation / conversion once imported but it feels like adding unnecessary steps.
Is there some other way I can achieve this within the process I already have?
View 4 Replies
View Related
May 28, 2015
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
View 7 Replies
View Related
Oct 30, 2007
Problem importing data from flat file into decimal(9,2) field. The data in the flat file is 000001453 and I am copying it to a decimal(10,2) field and instead of showing up in the 0000014.53 it comes across as 0001453.00. I tried defining the input columns a few different ways but none seemed to work. How do I do this with SSIS or do I need to write a SP and use convert? Thanks.
View 5 Replies
View Related
Dec 27, 2007
Hi!
I'm building a web application. I need to read data from a text or excel file and process the data and then store the result records into database. The record number is big. I can store the data record into database (SQL Server 2005) one at a time. I think it's slow. Is there any way to insert the data in bulk.
Thanks!
ccy
View 4 Replies
View Related
Apr 18, 2001
SQL Server 7.0 doesn't seem to support data files for bulk insert that have quoted text fields.
e.g.
" 1","Farmer","Joe","AAA","Smith John","",20001001,
I've tried using the format file to strip out the quotes. But, this doesn't seem to work.
My format file looks like this:
4.2
9
1 SQLCHAR 0 0 """ 0 dummy1
2 SQLCHAR 0 9 "","" 2 EmployeeID
3 SQLCHAR 0 35 "","" 3 LastName
4 SQLCHAR 0 35 "","" 4 FirstName
5 SQLCHAR 0 10 "","" 5 Category
6 SQLCHAR 0 35 "","" 6 Supervisor
7 SQLCHAR 0 5 ""," 7 OpCode
8 SQLCHAR 0 8 "," 8 HireDate
9 SQLCHAR 0 8 "
" 9 TermDate
Any idea on how I can bulk insert a data file where some of the fields are qutoed.
View 2 Replies
View Related
Jun 25, 2014
I have imported data in my table using the bulk insert command. I was supposed to fill specific columns of my table with that data so I used a view to put them in the column I wanted.
The table looks like this now:
id | id_param | val_param
+-----------+--------------+
1 | no_tel | 742062141
2 | sex | 1
3 | age | 23
4 | no_tel | 765234157
5 | sex | 1
6 | age | 34
When I want to select only the val_param that is=1 for the id_param=sex using this interogation:
select * from bd_rox where id_param='sex' and val_param='1'
it returns no value and I don`t know why.The wanted result should look like this:
id | id_param | val_param
+-----------+--------------+-
2 | sex | 1
5 | sex | 1
View 9 Replies
View Related
Jan 30, 2008
I manage a legacy system that dumps it's data into a number of different databases (same schema) on a nightly basis using bulk insert. I need to formulate a strategy for efficiently aggregating that data into a single database right after these nightly extractions complete. Here is my current stategy:
1. Duplicate the legacy system's database schema and add an identifier column to specify which database the data loaded from.
2. Each night, delete all records in the table.
3. Each night, for each database:
3a. Set each table's default value to a value that references the current database being loaded.
3b. Use the legacy system's flat files and format files to bulk insert into the database.
3c. Clear the default value.
What other steps would faciliate performance? Dropping and recreating the indexes? Does anyone forsee faults in this strategy?
Thanks,
Matt
View 3 Replies
View Related
