I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GO
Change the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)
|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:
> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>
|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:
Showing posts with label import. Show all posts
Showing posts with label import. Show all posts
Thursday, March 29, 2012
Bulk loading with OpenXML
I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GOChange the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:
> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:
>
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GOChange the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:
> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:
>
Sunday, March 25, 2012
Bulk Insert XML?
Is there a way to import a large XML file into SQL Server 2000/2005 using
T-SQL?
Looking for some utility like Bulk Insert/bcp...
- RSQLXML 3 SP3.
Comes with a great utility called SQLXML Bulk Load. Should do exactly what
you're after.
HTH. Ryan
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:53153384-7C83-40A5-9885-7DF2833DB41C@.microsoft.com...
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||In SQL 2005 the OPENROWSET function has been improved:
http://msdn2.microsoft.com/en-us/library/ms190312(SQL.90).aspx
Look at SINGLE_BLOB, SINGLE_CLOB and SINGLE_NLOB.
ML
http://milambda.blogspot.com/|||Use OPENXML. thats the best way for insert of XML through T-SQL.
Checkout in BOL for more info. Hope this helps.
--
"Rakesh" wrote:
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||I have a couple of examples:
http://www.sqlservercentral.com/columnists/sholliday/
http://spaces.msn.com/sholliday/ 9/22/2005
..
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:76E7E737-C5F9-435A-9CBA-298429652D4C@.microsoft.com...
> Use OPENXML. thats the best way for insert of XML through T-SQL.
> Checkout in BOL for more info. Hope this helps.
> --
>
>
> "Rakesh" wrote:
>
using
T-SQL?
Looking for some utility like Bulk Insert/bcp...
- RSQLXML 3 SP3.
Comes with a great utility called SQLXML Bulk Load. Should do exactly what
you're after.
HTH. Ryan
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:53153384-7C83-40A5-9885-7DF2833DB41C@.microsoft.com...
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||In SQL 2005 the OPENROWSET function has been improved:
http://msdn2.microsoft.com/en-us/library/ms190312(SQL.90).aspx
Look at SINGLE_BLOB, SINGLE_CLOB and SINGLE_NLOB.
ML
http://milambda.blogspot.com/|||Use OPENXML. thats the best way for insert of XML through T-SQL.
Checkout in BOL for more info. Hope this helps.
--
"Rakesh" wrote:
> Is there a way to import a large XML file into SQL Server 2000/2005 using
> T-SQL?
> Looking for some utility like Bulk Insert/bcp...
> - R|||I have a couple of examples:
http://www.sqlservercentral.com/columnists/sholliday/
http://spaces.msn.com/sholliday/ 9/22/2005
..
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:76E7E737-C5F9-435A-9CBA-298429652D4C@.microsoft.com...
> Use OPENXML. thats the best way for insert of XML through T-SQL.
> Checkout in BOL for more info. Hope this helps.
> --
>
>
> "Rakesh" wrote:
>
using
Bulk Insert with Mirroring
Can anyone suggest a bulk insert solution in a mirroring environment?
Basically, I'd like to import a text or excel file using SSIS.
Do all the "Flat File" and "Excel" data flow sources use bulk inserts?
Just wondering if I would need to use a "staging" table or something like that since bulk inserts won't be logged when dbs are mirrored. I'm assuming I'd be able to import data using the dataflow sources into a sql destination "staging" table, then do a "INSERT INTO [production] SELECT * FROM [staging]". I'm assuming this will be logged (but slow).
Any help would be appreciated.
BULK INSERT is fully logged in FULL mode, so you should not need to do the intermediate step.
BULK INSERT with format file
Hi all,
I have a fixed width text file that I need to import into SQL Server. The
file is very wide and extremely long, so I dont want to go through and
manually create the columns in DTS. I do however want to try and use BULK
INSERT to copy the records into my table. I thought the only way would be t
o
create a format file based on my fixed width text file so it could read the
appropriate columns. The problem is I have never done this and cant seem to
get it working. I guess I need to know if I can use BULK INSERT with a
format file to do this operation or I have to manually size out the columns?
The error I keep getting is some kind of EOF message. I have no idea with I
am creating the format file correctly either, but I made it look exactly wha
t
was on BOL. Thanks in advance for anyones help!!!!Post a few rows of the file, the format file you have created, and the error
msg you are getting...
"Andre" wrote:
> Hi all,
> I have a fixed width text file that I need to import into SQL Server. The
> file is very wide and extremely long, so I dont want to go through and
> manually create the columns in DTS. I do however want to try and use BULK
> INSERT to copy the records into my table. I thought the only way would be
to
> create a format file based on my fixed width text file so it could read th
e
> appropriate columns. The problem is I have never done this and cant seem
to
> get it working. I guess I need to know if I can use BULK INSERT with a
> format file to do this operation or I have to manually size out the column
s?
> The error I keep getting is some kind of EOF message. I have no idea with
I
> am creating the format file correctly either, but I made it look exactly w
hat
> was on BOL. Thanks in advance for anyones help!!!!|||longkimber dba qwestcsql
lessdavid dba qwestcsql
rondtimme dba qwestcsql
8.0
5
1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
"CBretana" wrote:
> Post a few rows of the file, the format file you have created, and the err
or
> msg you are getting...
> "Andre" wrote:
>|||OK, You said this was su[pposed to be a "Fixed Width" data file, if so,
then there ARE NO field terminators, enter an empty string "" for the
terminators in all but the last field. Second, The BCP automatically adds
the Carriage Return, when you specify the new Line... (\n) so not specify
(\r\n) or you will get two carriage returns...
Finally, the three data rows you passed DO NOT have the same number of
characters in them... a fixed width file MUST have exactly the same number
of characters (BYTES) In each row... R U Sure you have a fixed width file '
"Andre" wrote:
> longkimber dba qwestcsql
> lessdavid dba qwestcsql
> rondtimme dba qwestcsql
> 8.0
> 5
> 1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
> 3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
>
> "CBretana" wrote:
>|||Oh I was typing in the data and added a character by accident. Yes there ar
e
no field terminators. I was giving a file that looks this like with a data
dictionary with how long each field should be. OK, I will do the empty
string, but what do I put for the last line? Also, I cant write out the
entire error message, but it says at the end some kind of EOF error message.
If you give me your email I can take a screen shot and send it to you.
Thanks for all your assistance.
"CBretana" wrote:
> OK, You said this was su[pposed to be a "Fixed Width" data file, if so,
> then there ARE NO field terminators, enter an empty string "" for the
> terminators in all but the last field. Second, The BCP automatically add
s
> the Carriage Return, when you specify the new Line... (\n) so not specify
> (\r\n) or you will get two carriage returns...
> Finally, the three data rows you passed DO NOT have the same number of
> characters in them... a fixed width file MUST have exactly the same numbe
r
> of characters (BYTES) In each row... R U Sure you have a fixed width file
'
> "Andre" wrote:
>|||Yes, send me the actual file, or if it's too big, the first thousand lines o
r
so... And the format file, and the error msg screen shot...
Send to cbretana@.areteind.com
"Andre" wrote:
> Oh I was typing in the data and added a character by accident. Yes there
are
> no field terminators. I was giving a file that looks this like with a dat
a
> dictionary with how long each field should be. OK, I will do the empty
> string, but what do I put for the last line? Also, I cant write out the
> entire error message, but it says at the end some kind of EOF error messag
e.
> If you give me your email I can take a screen shot and send it to you.
> Thanks for all your assistance.
> "CBretana" wrote:
>|||In addition to the other poster's comments there is one gotcha with BULK
INSERT and format files. Your format file must contain a hard return at the
end or else you will get an error. This error is different depending on the
format version, but it often makes no sense.
So if you are editing your format file in notepad go to the end and hit "Ent
er."
Seriously.
> longkimber dba qwestcsql
> lessdavid dba qwestcsql
> rondtimme dba qwestcsql
> 8.0
> 5
> 1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
> 3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
> "CBretana" wrote:
>|||There is a shortcut to creating format files that I discovered and have used
religiously ever since.
Create a dummy DTS package and create a "Bulk Insert" task on the package.
On the properties page for the task, select the table (you will need a SQL
connection task first), then click "Use Format File" and then click the
Generate button. Browse to your input file and enter a name for the format
file. Then click next and follow the prompts. You will have to indicate
where the fields end (same dialogs as when importing a flat file to Excel).
After you finish the 'generate' dialogs...the format file will be created
and you can delete the Bulk Insert icon from the DTS package or just close
DTS without saving the package at all.
At this point, if you need to add, modify or delete fields...it is easy to
do manually because all the goofy formatting requirements of this file are
taken care of.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:CC4A24C5-D822-4FE2-A724-58D094D298AE@.microsoft.com...
> Hi all,
> I have a fixed width text file that I need to import into SQL Server. The
> file is very wide and extremely long, so I dont want to go through and
> manually create the columns in DTS. I do however want to try and use BULK
> INSERT to copy the records into my table. I thought the only way would be
to
> create a format file based on my fixed width text file so it could read
the
> appropriate columns. The problem is I have never done this and cant seem
to
> get it working. I guess I need to know if I can use BULK INSERT with a
> format file to do this operation or I have to manually size out the
columns?
> The error I keep getting is some kind of EOF message. I have no idea with
I
> am creating the format file correctly either, but I made it look exactly
what
> was on BOL. Thanks in advance for anyones help!!!!
I have a fixed width text file that I need to import into SQL Server. The
file is very wide and extremely long, so I dont want to go through and
manually create the columns in DTS. I do however want to try and use BULK
INSERT to copy the records into my table. I thought the only way would be t
o
create a format file based on my fixed width text file so it could read the
appropriate columns. The problem is I have never done this and cant seem to
get it working. I guess I need to know if I can use BULK INSERT with a
format file to do this operation or I have to manually size out the columns?
The error I keep getting is some kind of EOF message. I have no idea with I
am creating the format file correctly either, but I made it look exactly wha
t
was on BOL. Thanks in advance for anyones help!!!!Post a few rows of the file, the format file you have created, and the error
msg you are getting...
"Andre" wrote:
> Hi all,
> I have a fixed width text file that I need to import into SQL Server. The
> file is very wide and extremely long, so I dont want to go through and
> manually create the columns in DTS. I do however want to try and use BULK
> INSERT to copy the records into my table. I thought the only way would be
to
> create a format file based on my fixed width text file so it could read th
e
> appropriate columns. The problem is I have never done this and cant seem
to
> get it working. I guess I need to know if I can use BULK INSERT with a
> format file to do this operation or I have to manually size out the column
s?
> The error I keep getting is some kind of EOF message. I have no idea with
I
> am creating the format file correctly either, but I made it look exactly w
hat
> was on BOL. Thanks in advance for anyones help!!!!|||longkimber dba qwestcsql
lessdavid dba qwestcsql
rondtimme dba qwestcsql
8.0
5
1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
"CBretana" wrote:
> Post a few rows of the file, the format file you have created, and the err
or
> msg you are getting...
> "Andre" wrote:
>|||OK, You said this was su[pposed to be a "Fixed Width" data file, if so,
then there ARE NO field terminators, enter an empty string "" for the
terminators in all but the last field. Second, The BCP automatically adds
the Carriage Return, when you specify the new Line... (\n) so not specify
(\r\n) or you will get two carriage returns...
Finally, the three data rows you passed DO NOT have the same number of
characters in them... a fixed width file MUST have exactly the same number
of characters (BYTES) In each row... R U Sure you have a fixed width file '
"Andre" wrote:
> longkimber dba qwestcsql
> lessdavid dba qwestcsql
> rondtimme dba qwestcsql
> 8.0
> 5
> 1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
> 3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
>
> "CBretana" wrote:
>|||Oh I was typing in the data and added a character by accident. Yes there ar
e
no field terminators. I was giving a file that looks this like with a data
dictionary with how long each field should be. OK, I will do the empty
string, but what do I put for the last line? Also, I cant write out the
entire error message, but it says at the end some kind of EOF error message.
If you give me your email I can take a screen shot and send it to you.
Thanks for all your assistance.
"CBretana" wrote:
> OK, You said this was su[pposed to be a "Fixed Width" data file, if so,
> then there ARE NO field terminators, enter an empty string "" for the
> terminators in all but the last field. Second, The BCP automatically add
s
> the Carriage Return, when you specify the new Line... (\n) so not specify
> (\r\n) or you will get two carriage returns...
> Finally, the three data rows you passed DO NOT have the same number of
> characters in them... a fixed width file MUST have exactly the same numbe
r
> of characters (BYTES) In each row... R U Sure you have a fixed width file
'
> "Andre" wrote:
>|||Yes, send me the actual file, or if it's too big, the first thousand lines o
r
so... And the format file, and the error msg screen shot...
Send to cbretana@.areteind.com
"Andre" wrote:
> Oh I was typing in the data and added a character by accident. Yes there
are
> no field terminators. I was giving a file that looks this like with a dat
a
> dictionary with how long each field should be. OK, I will do the empty
> string, but what do I put for the last line? Also, I cant write out the
> entire error message, but it says at the end some kind of EOF error messag
e.
> If you give me your email I can take a screen shot and send it to you.
> Thanks for all your assistance.
> "CBretana" wrote:
>|||In addition to the other poster's comments there is one gotcha with BULK
INSERT and format files. Your format file must contain a hard return at the
end or else you will get an error. This error is different depending on the
format version, but it often makes no sense.
So if you are editing your format file in notepad go to the end and hit "Ent
er."
Seriously.
> longkimber dba qwestcsql
> lessdavid dba qwestcsql
> rondtimme dba qwestcsql
> 8.0
> 5
> 1 SQLCHAR 0 4 "\0" 1 lastname SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 5 "\0" 2 firstname SQL_Latin1_General_CP1_CI_A
S
> 3 SQLCHAR 0 4 "\0" 3 type SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 8 "\0" 4 company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 3 "\r\n" 5 code SQL_Latin1_General_CP1_CI_AS
> "CBretana" wrote:
>|||There is a shortcut to creating format files that I discovered and have used
religiously ever since.
Create a dummy DTS package and create a "Bulk Insert" task on the package.
On the properties page for the task, select the table (you will need a SQL
connection task first), then click "Use Format File" and then click the
Generate button. Browse to your input file and enter a name for the format
file. Then click next and follow the prompts. You will have to indicate
where the fields end (same dialogs as when importing a flat file to Excel).
After you finish the 'generate' dialogs...the format file will be created
and you can delete the Bulk Insert icon from the DTS package or just close
DTS without saving the package at all.
At this point, if you need to add, modify or delete fields...it is easy to
do manually because all the goofy formatting requirements of this file are
taken care of.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:CC4A24C5-D822-4FE2-A724-58D094D298AE@.microsoft.com...
> Hi all,
> I have a fixed width text file that I need to import into SQL Server. The
> file is very wide and extremely long, so I dont want to go through and
> manually create the columns in DTS. I do however want to try and use BULK
> INSERT to copy the records into my table. I thought the only way would be
to
> create a format file based on my fixed width text file so it could read
the
> appropriate columns. The problem is I have never done this and cant seem
to
> get it working. I guess I need to know if I can use BULK INSERT with a
> format file to do this operation or I have to manually size out the
columns?
> The error I keep getting is some kind of EOF message. I have no idea with
I
> am creating the format file correctly either, but I made it look exactly
what
> was on BOL. Thanks in advance for anyones help!!!!
Bulk Insert vs. Data Flow Task (different row results using flat file source)
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?
It seems that there's a factor of 3 in between the two components, which could mean that Flat File Connection Manager is interpereting 3 rows as 1. This might be related with the row delimiter in the Flat File connection manager.
Can you try to use preview in the Flat File connection manager, and see if rows show up correctly?
|||Yeah, thanks for the reply. I've checked the preview and it looks fine. I've also tracked down some of the skipped rows and there's nothing signficant about them. They look exactly the same as all the others. Perhaps there is a hidden character or something? Is this possible?|||
Can you compare the rows imported by FlatFile with the rows imported by Bulk insert task and see if there is any difference between them?
Thanks,
Ovidiu Burlacu
Can you put a data viewer in your pipeline and see what rows pass through at execution time?
Thanks,
Ovidiu Burlacu
Bulk Insert Utility
I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.
SubhasishHi!
Bulk Insert task is a kind of task that can be included into a DTS package.
I believe you CANT import from an excel file using an stored procedure, you have to create a DTS package
see ya
Lorena
Originally posted by subhasishray
I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.
Subhasish|||Can I do it with the help of BCP?|||No, it needs to be a csv (or some delimited) or fixed width file...|||So either save the xls as csv or call the dts package from your stored procedure. Any reason why you do not want to use dts ?
SubhasishHi!
Bulk Insert task is a kind of task that can be included into a DTS package.
I believe you CANT import from an excel file using an stored procedure, you have to create a DTS package
see ya
Lorena
Originally posted by subhasishray
I need to import data from an excel file to a SQL SERVER databse with the help of BUlk insert utility (with out running a DTS).Can anybody tell me the code?I need to place the code in a Stored Procedure.
Subhasish|||Can I do it with the help of BCP?|||No, it needs to be a csv (or some delimited) or fixed width file...|||So either save the xls as csv or call the dts package from your stored procedure. Any reason why you do not want to use dts ?
BULK INSERT UTF8 File ERROR 4864 on first row first column
we try to import a UTF8 File (at first with SSIS BULK INSERT but now with SQL to track down the errors) and always face the same issue - the first row does not import with error "Msg 4864, Level 16, State 1, Line 10 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (PSGT_ID)."
The Statements see below. We need to use utf8 because thets the only we can get - with ansi oder unicode it works - so why not utf8? (Codepage 65001?)
--CREATE TABLE [BULKTEST](
-- [PSGT_ID] [numeric](38, 0) NOT NULL primary key,
-- [PSGT_PSG_ID] [numeric](38, 0) NOT NULL,
-- [PSGT_PSP_ID] [numeric](38, 0) NOT NULL,
-- [PSGT_KURZTEXTM] [nvarchar](5) COLLATE Latin1_General_CI_AS NULL,
-- [PSGT_TEXT] [nvarchar](40) COLLATE Latin1_General_CI_AS NULL, -
- [PSGT_AENDDATUM] [datetime] NOT NULL)
truncate table [BULKTEST]
BULK INSERT [BULKTEST] FROM 'C:\test.txt' WITH( CHECK_CONSTRAINTS, CODEPAGE='65001', DATAFILETYPE='char', FIELDTERMINATOR='|', ROWTERMINATOR='\n')
select * from [BULKTEST]
As test data you could use something like this
1|2|3|4|5|2006-01-01
2|2|3|4|5|2006-01-01
3|2|3|4|5|2006-01-01
4|2|3|4|5|2006-01-01
in notepad in a text file stored as UTF8!
We really need help on this!
Best regards, HANNES
Try using the DATAFILETYPE = 'widechar'. You can also try using a format file.|||I have tried widechar and also a format file - but it stays the same.
Maybe we are a little bit further - could it by that notepad incorrectly adds some characters at the beginning of the file?
Like  - if i display them on the comandline with type <filename>?
LG HANNES
|||Check this Blog http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx in this case.Thursday, March 22, 2012
Bulk insert Unicode data
Hi,
I have to import Unicode data from a txt file to a sql server table.
I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with
Datafiletype = WIDECHAR but without success.
The error is "Cannot perform bulk insert. Invalid collation name for source
column xx in format file".
Format file is :
8.0
19
1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
11 SQLMONEY 0 8 "~" 12 Price ""
12 SQLFLT8 0 8 "~" 13 VAT ""
13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
17 SQLINT 0 4 "~" 17 Pagina ""
18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
19 SQLINT 0 0
(~ is field separator)
Thanks!
MBHi
It is not clear what you are trying to do with field 19? if it is not
required then check out
http://msdn.microsoft.com/library/d...>
bcp_9y43.asp
At a guess try:
18 SQLNCHAR 0 250 "" 18 ImageName Latin1_General_CI_A
19 SQLCHAR 0 0 "\r\n" 0 Dump
John
"Michela Burello" wrote:
> Hi,
> I have to import Unicode data from a txt file to a sql server table.
> I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with
> Datafiletype = WIDECHAR but without success.
> The error is "Cannot perform bulk insert. Invalid collation name for sourc
e
> column xx in format file".
> Format file is :
> 8.0
> 19
> 1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
> 2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
> 3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
> 4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
> 5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
> 6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
> 7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
> 8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
> 9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
> 10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
> 11 SQLMONEY 0 8 "~" 12 Price ""
> 12 SQLFLT8 0 8 "~" 13 VAT ""
> 13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
> 14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
> 15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
> 16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
> 17 SQLINT 0 4 "~" 17 Pagina ""
> 18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
> 19 SQLINT 0 0
> (~ is field separator)
> Thanks!
> MB|||Hi,
thank you for your answer but I still have problems!
I tried to bulk insert without field 19.
The error is "Cannot perform bulk insert. Invalid collation name for source
column 18 in format file".
My format file is now:
8.0
18
1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
11 SQLMONEY 0 8 "~" 12 Price ""
12 SQLFLT8 0 8 "~" 13 VAT ""
13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
17 SQLINT 0 4 "~" 17 Pagina ""
18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
My query is:
bulk insert MyTable
from 'myfile.txt'
with ( fieldterminator = '~',
firstrow = 2,
DATAFILETYPE = 'widechar',
FORMATFILE = 'myformatfile.fmt',
codepage = 1200
)
The table MyTable has this structure:
BatchGroup nvarchar 50
ProductID varchar 100 1
ProducerIDERP nvarchar 100
ProducerName nvarchar 100
ProductDefinitionName varchar 100
Name nvarchar 500 1
MeasureEquivalenceFactor float 8
MeasureUnitID nvarchar 10 1
MeasureUnitIDAlt nvarchar 10
PricingCategoryName nvarchar 100
SupplierName nvarchar 100
Price money 8
VAT float 8
DisplayName nvarchar 500
Tipo varchar 500
SottoTipo varchar 500
Pagina int 4
ImageName varchar 500
ItemID int 4 (identity)
Thanks
MB
"John Bell" wrote:
> Hi
> It is not clear what you are trying to do with field 19? if it is not
> required then check out
> http://msdn.microsoft.com/library/d...
t_bcp_9y43.asp
> At a guess try:
> 18 SQLNCHAR 0 250 "" 18 ImageName Latin1_General_CI_A
> 19 SQLCHAR 0 0 "\r\n" 0 Dump
>
> John
> "Michela Burello" wrote:
>|||On Thu, 28 Apr 2005 00:06:03 -0700, Michela Burello <Michela
Burello@.discussions.microsoft.com> wrote:
>The error is "Cannot perform bulk insert. Invalid collation name for source
>column xx in format file".
(snip)
>18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
Hi Michela,
There is no collation "Latin1_General_CI_A". You need to add either an S
or an I at the end of this line.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
This one works for me!!
8.0
19
1 SQLNCHAR 2 100 "~" 1
BatchGroup SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 100 "~" 2
ProductID SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 200 "~" 3
ProducerIDERP SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 200 "~" 4
ProducerName SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 100 "~" 5
ProductDefinitionName SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 1000 "~" 6
Name SQL_Latin1_General_CP1_CI_AS
7 SQLFLT8 1 8 "~" 7
MeasureEquivalenceFactor ""
8 SQLNCHAR 2 20 "~" 8
MeasureUnitID SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 20 "~" 9
MeasureUnitIDAlt SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 200 "~" 10
PricingCategoryName SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 200 "~" 11
SupplierName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "~" 12
Price ""
13 SQLFLT8 1 8 "~" 13
VAT ""
14 SQLNCHAR 2 1000 "~" 14
DisplayName SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 500 "~" 15
Tipo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 500 "~" 16
SottoTipo SQL_Latin1_General_CP1_CI_AS
17 SQLINT 1 4 "~" 17
Pagina ""
18 SQLCHAR 2 500 "" 18
ImageName SQL_Latin1_General_CP1_CI_AS
19 SQLINT 0 0 "\r\n" 19
ItemID ""
Any the statement:
bulk insert MyTable
from 'C:\temp\myfile.txt'
with ( firstrow = 1,
FORMATFILE = 'C:\temp\myformatfile.fmt'
)
John
I have to import Unicode data from a txt file to a sql server table.
I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with
Datafiletype = WIDECHAR but without success.
The error is "Cannot perform bulk insert. Invalid collation name for source
column xx in format file".
Format file is :
8.0
19
1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
11 SQLMONEY 0 8 "~" 12 Price ""
12 SQLFLT8 0 8 "~" 13 VAT ""
13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
17 SQLINT 0 4 "~" 17 Pagina ""
18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
19 SQLINT 0 0
(~ is field separator)
Thanks!
MBHi
It is not clear what you are trying to do with field 19? if it is not
required then check out
http://msdn.microsoft.com/library/d...>
bcp_9y43.asp
At a guess try:
18 SQLNCHAR 0 250 "" 18 ImageName Latin1_General_CI_A
19 SQLCHAR 0 0 "\r\n" 0 Dump
John
"Michela Burello" wrote:
> Hi,
> I have to import Unicode data from a txt file to a sql server table.
> I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with
> Datafiletype = WIDECHAR but without success.
> The error is "Cannot perform bulk insert. Invalid collation name for sourc
e
> column xx in format file".
> Format file is :
> 8.0
> 19
> 1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
> 2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
> 3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
> 4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
> 5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
> 6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
> 7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
> 8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
> 9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
> 10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
> 11 SQLMONEY 0 8 "~" 12 Price ""
> 12 SQLFLT8 0 8 "~" 13 VAT ""
> 13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
> 14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
> 15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
> 16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
> 17 SQLINT 0 4 "~" 17 Pagina ""
> 18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
> 19 SQLINT 0 0
> (~ is field separator)
> Thanks!
> MB|||Hi,
thank you for your answer but I still have problems!
I tried to bulk insert without field 19.
The error is "Cannot perform bulk insert. Invalid collation name for source
column 18 in format file".
My format file is now:
8.0
18
1 SQLNCHAR 0 50 "~" 1 BatchGroup Latin1_General_CI_AS
2 SQLNCHAR 0 50 "~" 2 ProductID Latin1_General_CI_AS
3 SQLNCHAR 0 100 "~" 3 ProducerIDERP Latin1_General_CI_AS
4 SQLNCHAR 0 100 "~" 4 ProducerName Latin1_General_CI_AS
5 SQLNCHAR 0 50 "~" 5 ProductDefinitionName Latin1_General_
CI_AS
6 SQLNCHAR 0 500 "~" 6 Name Latin1_General_CI_AS
7 SQLNCHAR 0 500 "~" 14 DisplayName Latin1_General_CI_AS
8 SQLFLT8 0 8 "~" 7 MeasureEquivalenceFactor ""
9 SQLNCHAR 0 10 "~" 8 MeasureUnitID Latin1_General_CI_AS
10 SQLNCHAR 0 10 "~" 9 MeasureUnitIDAlt Latin1_General_CI_AS
11 SQLMONEY 0 8 "~" 12 Price ""
12 SQLFLT8 0 8 "~" 13 VAT ""
13 SQLNCHAR 0 100 "~" 10 PricingCategoryName Latin1_General_C
I_AS
14 SQLNCHAR 0 100 "~" 11 SupplierName Latin1_General_CI_AS
15 SQLNCHAR 0 250 "~" 15 Tipo Latin1_General_CI_AS
16 SQLNCHAR 0 250 "~" 16 SottoTipo Latin1_General_CI_AS
17 SQLINT 0 4 "~" 17 Pagina ""
18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
My query is:
bulk insert MyTable
from 'myfile.txt'
with ( fieldterminator = '~',
firstrow = 2,
DATAFILETYPE = 'widechar',
FORMATFILE = 'myformatfile.fmt',
codepage = 1200
)
The table MyTable has this structure:
BatchGroup nvarchar 50
ProductID varchar 100 1
ProducerIDERP nvarchar 100
ProducerName nvarchar 100
ProductDefinitionName varchar 100
Name nvarchar 500 1
MeasureEquivalenceFactor float 8
MeasureUnitID nvarchar 10 1
MeasureUnitIDAlt nvarchar 10
PricingCategoryName nvarchar 100
SupplierName nvarchar 100
Price money 8
VAT float 8
DisplayName nvarchar 500
Tipo varchar 500
SottoTipo varchar 500
Pagina int 4
ImageName varchar 500
ItemID int 4 (identity)
Thanks
MB
"John Bell" wrote:
> Hi
> It is not clear what you are trying to do with field 19? if it is not
> required then check out
> http://msdn.microsoft.com/library/d...
t_bcp_9y43.asp
> At a guess try:
> 18 SQLNCHAR 0 250 "" 18 ImageName Latin1_General_CI_A
> 19 SQLCHAR 0 0 "\r\n" 0 Dump
>
> John
> "Michela Burello" wrote:
>|||On Thu, 28 Apr 2005 00:06:03 -0700, Michela Burello <Michela
Burello@.discussions.microsoft.com> wrote:
>The error is "Cannot perform bulk insert. Invalid collation name for source
>column xx in format file".
(snip)
>18 SQLNCHAR 0 250 "\r\n" 18 ImageName Latin1_General_CI_A
Hi Michela,
There is no collation "Latin1_General_CI_A". You need to add either an S
or an I at the end of this line.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
This one works for me!!
8.0
19
1 SQLNCHAR 2 100 "~" 1
BatchGroup SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 100 "~" 2
ProductID SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 200 "~" 3
ProducerIDERP SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 200 "~" 4
ProducerName SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 100 "~" 5
ProductDefinitionName SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 1000 "~" 6
Name SQL_Latin1_General_CP1_CI_AS
7 SQLFLT8 1 8 "~" 7
MeasureEquivalenceFactor ""
8 SQLNCHAR 2 20 "~" 8
MeasureUnitID SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 20 "~" 9
MeasureUnitIDAlt SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 200 "~" 10
PricingCategoryName SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 200 "~" 11
SupplierName SQL_Latin1_General_CP1_CI_AS
12 SQLMONEY 1 8 "~" 12
Price ""
13 SQLFLT8 1 8 "~" 13
VAT ""
14 SQLNCHAR 2 1000 "~" 14
DisplayName SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 500 "~" 15
Tipo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 500 "~" 16
SottoTipo SQL_Latin1_General_CP1_CI_AS
17 SQLINT 1 4 "~" 17
Pagina ""
18 SQLCHAR 2 500 "" 18
ImageName SQL_Latin1_General_CP1_CI_AS
19 SQLINT 0 0 "\r\n" 19
ItemID ""
Any the statement:
bulk insert MyTable
from 'C:\temp\myfile.txt'
with ( firstrow = 1,
FORMATFILE = 'C:\temp\myformatfile.fmt'
)
John
Bulk Insert Row Terminator
Hi there, I'm trying to import a cobol file (.dat) which has a line feed as the row delimiter. Using the TransactSQL Bulk Insert with a row terminator of '\n' is not working for me. Does anyone know the equivilant row terminator of a LF? (Using the Import Export wizard I supply a {LF} and it likes that fine). I would like to use the Bulk Insert Statement for more control of the data. Any help is greatly appreciated.
Thanks,If it's just a line feed (hex 0A), then you should be able to use '\l\' as the rowterminator. (Note that is a lowercase "L".)
Terri|||Ryan..did that work for you?
Bulk Insert remove extra quotes
I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.
Example 1: """"This is a just a test."""
Results should be "This is just a test"
Example 2: """This is only a test"" and nothing more"
Results should be:"This is only a test" and nothing more.
Now.
If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.
Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?
Below is my syntax
SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'
Thanks
Conrad...
Thanks for the information.
When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.
On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.
Keep in mind, that the quotes may not appeared in all of the fields.
Thanks
Conrad...
|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||Umachandar Jayachandran - MS wrote:
As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.
Hello,
I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.
Any links to documentation or examples would be welcome!
Thanks a lot
Detlef
Bulk Insert remove extra quotes
I am using Bulk Insert to Import a Tab Delimeted file that contains quotes(") in some of the fields.
Example 1: """"This is a just a test."""
Results should be "This is just a test"
Example 2: """This is only a test"" and nothing more"
Results should be:"This is only a test" and nothing more.
Now.
If I use DTS Import Wizard process and select "Double Quotes" as the Text Qualifier, I get the result I am expecting. The extra quotes are gone.
Question: Is there a way or a switch in Bulk Insert that I could use to get rid of the extra quotes?
Below is my syntax
SET @.sql ='BULK INSERT '+ @.table_name + ' FROM "' + @.conversion_data_in + '\' + @.source_file + '"
WITH ( DATAFILETYPE='NATIVE' FIELDTERMINATOR = ''\t'',ROWTERMINATOR = ''\n'')'
Thanks
Conrad...
Thanks for the information.
When you use DTS Import Wizard, it asks you to select "Text Qualifier". If you select "Double Quotes {"}" and Tab Delimiter on the next screen, the data gets imported without the extra quotes.
On the Other hand, I do not know, how to remove the extra quotes using BCP or Bulk Insert.
Keep in mind, that the quotes may not appeared in all of the fields.
Thanks
Conrad...
|||As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.|||Umachandar Jayachandran - MS wrote:
As I said, you will have to use a format file to strip the text qualifiers but these have to exist on all values of a particular column for example. So you cannot have a value for one column in a row with text qualifiers and another row having no text qualifiers. If this doesn't work then you will have to stick with the DTS approach or use another utility or program to format the data file so that you can use BCP/BULK INSERT directly.
Hello,
I have a similar problem that might be solved with a format file. In my files, the decimal separator is a comma and the format for datetime is dd.mm.yyyy. But I do not find any documentation on that topic. I found a general description of XML format files at http://msdn2.microsoft.com/en-us/library/ms189327.aspx but nothing with respect to stripping or replacing characters.
Any links to documentation or examples would be welcome!
Thanks a lot
Detlef
bulk insert question? Another way?
hi guys,
I have a text file contains about 1 million rows, I tried to import to a
table but I received data conversion errors for some rows, however, the row
number is like 504500 for example, I want to troubleshoot this row but the
only way I know is to open the text file (300MB) which will take forever to
open on notepad, I'm not sure if there is an easy way to troubleshoot such
a problem. Another thing is, I set Batchsize=10000, so I guess if I
received errors for 20 rows, it would mean that 200000 rows (20 *10000)
didn't get inserted into table, correct?
ThanksKevin,
Get a copy of TextPad or one of the many shareware or freeware text editors
that can handle large files with ease.
Another alternative is to import the file into a one-column staging table on
your SQL Server, and then scrub the data before inserting it into the target
table.
Steve Kass
Drew University
kevin wrote:
>hi guys,
> I have a text file contains about 1 million rows, I tried to import to
a
>table but I received data conversion errors for some rows, however, the ro
w
>number is like 504500 for example, I want to troubleshoot this row but the
>only way I know is to open the text file (300MB) which will take forever to
>open on notepad, I'm not sure if there is an easy way to troubleshoot such
>a problem. Another thing is, I set Batchsize=10000, so I guess if I
>received errors for 20 rows, it would mean that 200000 rows (20 *10000)
>didn't get inserted into table, correct?
>Thanks
>
>
>
I have a text file contains about 1 million rows, I tried to import to a
table but I received data conversion errors for some rows, however, the row
number is like 504500 for example, I want to troubleshoot this row but the
only way I know is to open the text file (300MB) which will take forever to
open on notepad, I'm not sure if there is an easy way to troubleshoot such
a problem. Another thing is, I set Batchsize=10000, so I guess if I
received errors for 20 rows, it would mean that 200000 rows (20 *10000)
didn't get inserted into table, correct?
ThanksKevin,
Get a copy of TextPad or one of the many shareware or freeware text editors
that can handle large files with ease.
Another alternative is to import the file into a one-column staging table on
your SQL Server, and then scrub the data before inserting it into the target
table.
Steve Kass
Drew University
kevin wrote:
>hi guys,
> I have a text file contains about 1 million rows, I tried to import to
a
>table but I received data conversion errors for some rows, however, the ro
w
>number is like 504500 for example, I want to troubleshoot this row but the
>only way I know is to open the text file (300MB) which will take forever to
>open on notepad, I'm not sure if there is an easy way to troubleshoot such
>a problem. Another thing is, I set Batchsize=10000, so I guess if I
>received errors for 20 rows, it would mean that 200000 rows (20 *10000)
>didn't get inserted into table, correct?
>Thanks
>
>
>
Bulk Insert Question.
Hello,
I have a question about bulk inserting text files. I have text files I import into a database, the files have alot of fields. The fields are terminated by ",", and the rows are terminated by /r. When I do the bulk insert, it puts each row into one row, I get no errors. Any help on this would be great.
BULK INSERT tblTracking
FROM 'C:\FileName.txt'
WITH
(
CHECK_CONSTRAINTS,
BATCHSIZE = 10000,
DATAFILETYPE = 'char',
KEEPNULLS,
FIELDTERMINATOR = '","',
ROWTERMINATOR='\r',
TABLOCK
)
hi,
please have a look at format files to b used with bluk operations..
regards
Tuesday, March 20, 2012
bulk insert problems
hi
situation is like this:
I have csv file I would like to import into table. However I have problems with code page 852 so I changed it to 850. Here is the command
bulk insert ticket_dump_remedy from 'c:\work\ticket_dump_remedy_040308.csv'
with
(
CODEPAGE = 850,
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
After that execution ends with error:
Bulk insert data conversion error (type mismatch) for row 2, column 2 (Create Date).
Create Date is defined as datetime...
Example of file row is this one...
"Case ID+","Create Date","Type+"
"APHD00000031378","12/2/2003 10:13:00 AM","General"OK, so you've got bad data...
Load the data to a stage table that has varchar for all data types
The use ISNUMERIC and ISDATE to test to see the bad rows...
ALTER The table after the load to add an identity column to confirm the row the badat data is on|||thank you for your help and time
but this csv file comes every day and my goal is update database according to this file so I will have to look for another solution :(|||Do you have a process to audit the file?
How big is it?
Do you have headers and trailers with the data?
Since it's csv, does it have a column list in row 1?
If you make a stage table, all varchar, you can check the file out before you apply it.|||it's file about 3000 rows
unfortunatelly I don't have any process to check if the csv is ok...
I will have xls file with the same data so probably I will try to connect to xls file via ODBC and get data from there...hopefully it will work..
anyway thank you for your time|||I have tried the excel file and it's working... I converted csv to excel and than uploaded data from there and somehow it's working. Don't ask me how...
situation is like this:
I have csv file I would like to import into table. However I have problems with code page 852 so I changed it to 850. Here is the command
bulk insert ticket_dump_remedy from 'c:\work\ticket_dump_remedy_040308.csv'
with
(
CODEPAGE = 850,
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
After that execution ends with error:
Bulk insert data conversion error (type mismatch) for row 2, column 2 (Create Date).
Create Date is defined as datetime...
Example of file row is this one...
"Case ID+","Create Date","Type+"
"APHD00000031378","12/2/2003 10:13:00 AM","General"OK, so you've got bad data...
Load the data to a stage table that has varchar for all data types
The use ISNUMERIC and ISDATE to test to see the bad rows...
ALTER The table after the load to add an identity column to confirm the row the badat data is on|||thank you for your help and time
but this csv file comes every day and my goal is update database according to this file so I will have to look for another solution :(|||Do you have a process to audit the file?
How big is it?
Do you have headers and trailers with the data?
Since it's csv, does it have a column list in row 1?
If you make a stage table, all varchar, you can check the file out before you apply it.|||it's file about 3000 rows
unfortunatelly I don't have any process to check if the csv is ok...
I will have xls file with the same data so probably I will try to connect to xls file via ODBC and get data from there...hopefully it will work..
anyway thank you for your time|||I have tried the excel file and it's working... I converted csv to excel and than uploaded data from there and somehow it's working. Don't ask me how...
bulk insert problem
hi guys, I have a data file that I like to import to a table.
the column delimiter is @., row delimiter is \n, line feed.
But here is the problem, we have a column called "comment",
basically user can enter any characters to it, so if "comment" column
contains
@. character, then sql server think it's an extra column. However, that's
why before @. character we put \ , it's \@. in comment column now. BUT How
come it doesn't do escape for @. character? Is it possible to create an
exception for delimiter in case if Sql server sees \@. , then it'll ignore it
and will not treat it as delimiter?
thanksWhy not do a global replace of @. with some other sequence of characters that
is not already found in the file? Import the file and then do another
replace in the column to put the characters back to @..
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
> hi guys, I have a data file that I like to import to a table.
> the column delimiter is @., row delimiter is \n, line feed.
> But here is the problem, we have a column called "comment",
> basically user can enter any characters to it, so if "comment" column
> contains
> @. character, then sql server think it's an extra column. However,
> that's
> why before @. character we put \ , it's \@. in comment column now. BUT
> How
> come it doesn't do escape for @. character? Is it possible to create an
> exception for delimiter in case if Sql server sees \@. , then it'll ignore
> it
> and will not treat it as delimiter?
> thanks
>|||in this case, it's possible to write perl script to do global replacement in
windows?
This way I can make it automate process.
"Andrew J. Kelly" wrote:
> Why not do a global replace of @. with some other sequence of characters th
at
> is not already found in the file? Import the file and then do another
> replace in the column to put the characters back to @..
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
>
>|||I thought this might have been a one time import but I don't see why you
can't use perl (or some other utility) to automate this.
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:6D17EF5E-0150-4F38-80BB-91D6F086CA70@.microsoft.com...
> in this case, it's possible to write perl script to do global replacement
> in
> windows?
> This way I can make it automate process.
>
> "Andrew J. Kelly" wrote:
>sql
the column delimiter is @., row delimiter is \n, line feed.
But here is the problem, we have a column called "comment",
basically user can enter any characters to it, so if "comment" column
contains
@. character, then sql server think it's an extra column. However, that's
why before @. character we put \ , it's \@. in comment column now. BUT How
come it doesn't do escape for @. character? Is it possible to create an
exception for delimiter in case if Sql server sees \@. , then it'll ignore it
and will not treat it as delimiter?
thanksWhy not do a global replace of @. with some other sequence of characters that
is not already found in the file? Import the file and then do another
replace in the column to put the characters back to @..
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
> hi guys, I have a data file that I like to import to a table.
> the column delimiter is @., row delimiter is \n, line feed.
> But here is the problem, we have a column called "comment",
> basically user can enter any characters to it, so if "comment" column
> contains
> @. character, then sql server think it's an extra column. However,
> that's
> why before @. character we put \ , it's \@. in comment column now. BUT
> How
> come it doesn't do escape for @. character? Is it possible to create an
> exception for delimiter in case if Sql server sees \@. , then it'll ignore
> it
> and will not treat it as delimiter?
> thanks
>|||in this case, it's possible to write perl script to do global replacement in
windows?
This way I can make it automate process.
"Andrew J. Kelly" wrote:
> Why not do a global replace of @. with some other sequence of characters th
at
> is not already found in the file? Import the file and then do another
> replace in the column to put the characters back to @..
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
>
>|||I thought this might have been a one time import but I don't see why you
can't use perl (or some other utility) to automate this.
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:6D17EF5E-0150-4F38-80BB-91D6F086CA70@.microsoft.com...
> in this case, it's possible to write perl script to do global replacement
> in
> windows?
> This way I can make it automate process.
>
> "Andrew J. Kelly" wrote:
>sql
Bulk insert problem
Hi guys
I am using the Bulk insert command to import a flat file to the SQL server
2005.
My flat file contains about 1 million rows.
I have already created an empty table to insert the rows from the flat file.
But I dont want to import the first row into my table in the database.
Is there a way I can give some some sort of condition in my bulk insert
command so that the first row from the flat file does not get imported'
Here is my bulk insert statement:
BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
(FIELDTERMINATOR = ',')
Cheers
Mita"mita" <mita@.discussions.microsoft.com> wrote in message
news:F742B532-8FA9-4AC1-89AC-7E50FC757619@.microsoft.com...
> Hi guys
> I am using the Bulk insert command to import a flat file to the SQL server
> 2005.
> My flat file contains about 1 million rows.
> I have already created an empty table to insert the rows from the flat
> file.
> But I dont want to import the first row into my table in the database.
> Is there a way I can give some some sort of condition in my bulk insert
> command so that the first row from the flat file does not get imported'
> Here is my bulk insert statement:
> BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
> (FIELDTERMINATOR = ',')
>
Read the entry in Books Online for BULK INSERT.
David
I am using the Bulk insert command to import a flat file to the SQL server
2005.
My flat file contains about 1 million rows.
I have already created an empty table to insert the rows from the flat file.
But I dont want to import the first row into my table in the database.
Is there a way I can give some some sort of condition in my bulk insert
command so that the first row from the flat file does not get imported'
Here is my bulk insert statement:
BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
(FIELDTERMINATOR = ',')
Cheers
Mita"mita" <mita@.discussions.microsoft.com> wrote in message
news:F742B532-8FA9-4AC1-89AC-7E50FC757619@.microsoft.com...
> Hi guys
> I am using the Bulk insert command to import a flat file to the SQL server
> 2005.
> My flat file contains about 1 million rows.
> I have already created an empty table to insert the rows from the flat
> file.
> But I dont want to import the first row into my table in the database.
> Is there a way I can give some some sort of condition in my bulk insert
> command so that the first row from the flat file does not get imported'
> Here is my bulk insert statement:
> BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
> (FIELDTERMINATOR = ',')
>
Read the entry in Books Online for BULK INSERT.
David
Bulk insert problem
Hi guys
I am using the Bulk insert command to import a flat file to the SQL server
2005.
My flat file contains about 1 million rows.
I have already created an empty table to insert the rows from the flat file.
But I dont want to import the first row into my table in the database.
Is there a way I can give some some sort of condition in my bulk insert
command so that the first row from the flat file does not get imported'
Here is my bulk insert statement:
BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
(FIELDTERMINATOR = ',')
Cheers
Mita"mita" <mita@.discussions.microsoft.com> wrote in message
news:F742B532-8FA9-4AC1-89AC-7E50FC757619@.microsoft.com...
> Hi guys
> I am using the Bulk insert command to import a flat file to the SQL server
> 2005.
> My flat file contains about 1 million rows.
> I have already created an empty table to insert the rows from the flat
> file.
> But I dont want to import the first row into my table in the database.
> Is there a way I can give some some sort of condition in my bulk insert
> command so that the first row from the flat file does not get imported'
> Here is my bulk insert statement:
> BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
> (FIELDTERMINATOR = ',')
>
Read the entry in Books Online for BULK INSERT.
David
I am using the Bulk insert command to import a flat file to the SQL server
2005.
My flat file contains about 1 million rows.
I have already created an empty table to insert the rows from the flat file.
But I dont want to import the first row into my table in the database.
Is there a way I can give some some sort of condition in my bulk insert
command so that the first row from the flat file does not get imported'
Here is my bulk insert statement:
BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
(FIELDTERMINATOR = ',')
Cheers
Mita"mita" <mita@.discussions.microsoft.com> wrote in message
news:F742B532-8FA9-4AC1-89AC-7E50FC757619@.microsoft.com...
> Hi guys
> I am using the Bulk insert command to import a flat file to the SQL server
> 2005.
> My flat file contains about 1 million rows.
> I have already created an empty table to insert the rows from the flat
> file.
> But I dont want to import the first row into my table in the database.
> Is there a way I can give some some sort of condition in my bulk insert
> command so that the first row from the flat file does not get imported'
> Here is my bulk insert statement:
> BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
> (FIELDTERMINATOR = ',')
>
Read the entry in Books Online for BULK INSERT.
David
Monday, March 19, 2012
Bulk Insert not importing all records
Hi,
I am using the bulk insert to import over 26,000,000 records about 55 bytes
long. The insert seems to only want to import 1,780,000 records. I'm not
getting any errors and this works fine on smaller files. I have increased my
commandtimout to 260000 so I don't think that is the problem. Any help would
be appreciated.
Thanks,
EllieI don't know what your problem is, but I would be inclined to try
running multiple passes, using FIRSTROW and LASTROW to load a million
at a time. There is no guarantee that the same problem, whatever it
is, will not show up anyway though.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 15:51:45 -0400, "Ellie" <nospam@.nospam.net> wrote:
>Hi,
>I am using the bulk insert to import over 26,000,000 records about 55 bytes
>long. The insert seems to only want to import 1,780,000 records. I'm not
>getting any errors and this works fine on smaller files. I have increased m
y
>commandtimout to 260000 so I don't think that is the problem. Any help woul
d
>be appreciated.
>Thanks,
>Ellie
>|||Ellie (nospam@.nospam.net) writes:
> I am using the bulk insert to import over 26,000,000 records about 55
> bytes long. The insert seems to only want to import 1,780,000 records.
> I'm not getting any errors and this works fine on smaller files. I have
> increased my commandtimout to 260000 so I don't think that is the
> problem. Any help would be appreciated.
In additions to Roy's suggestion, try the -e option. This option generates
an error file, this may reveal something.
Rather than running multiple passes with -F and -L, you can set the
batch size with -b.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is my bulk insert command. It works fine in query analyzer but not in
my VB6 application.
BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
Where would I put the -e option?
Thank you for your help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B81E3E4CCAYazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> In additions to Roy's suggestion, try the -e option. This option generates
> an error file, this may reveal something.
> Rather than running multiple passes with -F and -L, you can set the
> batch size with -b.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Here is my bulk insert command. It works fine in query analyzer but not in
> my VB6 application.
Hmmm. It's not that the VB6 application times out? The default timeout
from client APIs is 30 secounds.
> BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
> WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
> Where would I put the -e option?
Sorry, I did not observer that you were using BULK INSERT. My advice was
for command-line BCP.
BULK INSERT does not seem to have any option corresponding to -e. Anyway, if
it works from Query Analyzer, I don't think there are any errors in the
file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure what you mean by that. How would I change it? I made a change to
the commandtimeout to 0 so it is unlimited.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B8A74A8E5A5Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> Hmmm. It's not that the VB6 application times out? The default timeout
> from client APIs is 30 secounds.
>
> Sorry, I did not observer that you were using BULK INSERT. My advice was
> for command-line BCP.
> BULK INSERT does not seem to have any option corresponding to -e. Anyway,
> if
> it works from Query Analyzer, I don't think there are any errors in the
> file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Not sure what you mean by that. How would I change it? I made a change to
> the commandtimeout to 0 so it is unlimited.
If you already have set the commandtimeout to 0, I'm afraid that I have
no idea what is going on.
If you have the proper access on the server, you could set up a Profiler
trace, and include the events in the Errors and Exception group, to
see if this brings up something.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for your help. I have managed to get all of the records imported by
changing the batchsize from 10000 to 100000. I don't know why that would
make a difference though.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B917BE14B6Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> If you already have set the commandtimeout to 0, I'm afraid that I have
> no idea what is going on.
> If you have the proper access on the server, you could set up a Profiler
> trace, and include the events in the Errors and Exception group, to
> see if this brings up something.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Thanks for your help. I have managed to get all of the records imported by
> changing the batchsize from 10000 to 100000. I don't know why that would
> make a difference though.
Neither have I!
I'm glad to hear that you got it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I am using the bulk insert to import over 26,000,000 records about 55 bytes
long. The insert seems to only want to import 1,780,000 records. I'm not
getting any errors and this works fine on smaller files. I have increased my
commandtimout to 260000 so I don't think that is the problem. Any help would
be appreciated.
Thanks,
EllieI don't know what your problem is, but I would be inclined to try
running multiple passes, using FIRSTROW and LASTROW to load a million
at a time. There is no guarantee that the same problem, whatever it
is, will not show up anyway though.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 15:51:45 -0400, "Ellie" <nospam@.nospam.net> wrote:
>Hi,
>I am using the bulk insert to import over 26,000,000 records about 55 bytes
>long. The insert seems to only want to import 1,780,000 records. I'm not
>getting any errors and this works fine on smaller files. I have increased m
y
>commandtimout to 260000 so I don't think that is the problem. Any help woul
d
>be appreciated.
>Thanks,
>Ellie
>|||Ellie (nospam@.nospam.net) writes:
> I am using the bulk insert to import over 26,000,000 records about 55
> bytes long. The insert seems to only want to import 1,780,000 records.
> I'm not getting any errors and this works fine on smaller files. I have
> increased my commandtimout to 260000 so I don't think that is the
> problem. Any help would be appreciated.
In additions to Roy's suggestion, try the -e option. This option generates
an error file, this may reveal something.
Rather than running multiple passes with -F and -L, you can set the
batch size with -b.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is my bulk insert command. It works fine in query analyzer but not in
my VB6 application.
BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
Where would I put the -e option?
Thank you for your help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B81E3E4CCAYazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> In additions to Roy's suggestion, try the -e option. This option generates
> an error file, this may reveal something.
> Rather than running multiple passes with -F and -L, you can set the
> batch size with -b.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Here is my bulk insert command. It works fine in query analyzer but not in
> my VB6 application.
Hmmm. It's not that the VB6 application times out? The default timeout
from client APIs is 30 secounds.
> BULK INSERT dataconflict.dbo.tblTemp from 'c:\output\tt060302.txt'
> WITH (BATCHSIZE=10000, FORMATFILE='c:\temp\Temp.fmt')
> Where would I put the -e option?
Sorry, I did not observer that you were using BULK INSERT. My advice was
for command-line BCP.
BULK INSERT does not seem to have any option corresponding to -e. Anyway, if
it works from Query Analyzer, I don't think there are any errors in the
file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure what you mean by that. How would I change it? I made a change to
the commandtimeout to 0 so it is unlimited.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B8A74A8E5A5Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> Hmmm. It's not that the VB6 application times out? The default timeout
> from client APIs is 30 secounds.
>
> Sorry, I did not observer that you were using BULK INSERT. My advice was
> for command-line BCP.
> BULK INSERT does not seem to have any option corresponding to -e. Anyway,
> if
> it works from Query Analyzer, I don't think there are any errors in the
> file.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Not sure what you mean by that. How would I change it? I made a change to
> the commandtimeout to 0 so it is unlimited.
If you already have set the commandtimeout to 0, I'm afraid that I have
no idea what is going on.
If you have the proper access on the server, you could set up a Profiler
trace, and include the events in the Errors and Exception group, to
see if this brings up something.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks for your help. I have managed to get all of the records imported by
changing the batchsize from 10000 to 100000. I don't know why that would
make a difference though.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B917BE14B6Yazorman@.127.0.0.1...
> Ellie (nospam@.nospam.net) writes:
> If you already have set the commandtimeout to 0, I'm afraid that I have
> no idea what is going on.
> If you have the proper access on the server, you could set up a Profiler
> trace, and include the events in the Errors and Exception group, to
> see if this brings up something.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ellie (nospam@.nospam.net) writes:
> Thanks for your help. I have managed to get all of the records imported by
> changing the batchsize from 10000 to 100000. I don't know why that would
> make a difference though.
Neither have I!
I'm glad to hear that you got it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, March 11, 2012
Bulk Insert in 2005
After upgrading our servers to 2005, I am getting a problem with Bulk Insert
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).
Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan
|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegro ups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>
where it will does not import a file that is located on another server. I
am using UNC paths.
I have looked into BOL and investgated the changes in security but Ive
turned up nothing. The sql server process runs with a domain account that
has full rights on the remote UNC path...in fact i made it a local admin on
that box!
Running in 2000 compatibility doesnt help. i get this message..
Cannot bulk load because the file "filename" could not be opened. Operating
system error code 5(Access is denied.).
Hello, JL
See the topic "Security Considerations for Using Transact-SQL to Bulk
Import Data" in Books Online 2005:
http://msdn2.microsoft.com/ms186286.aspx
Razvan
|||When I use sa account, it works...because it is using the service account.
But I still cannot get it to work using any domain account..even one which
is a domain admin. Somehow its not passing the security rights along.
I really want to stop using sql server authentication
JL
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138175309.065558.27770@.g49g2000cwa.googlegro ups.com...
> Hello, JL
> See the topic "Security Considerations for Using Transact-SQL to Bulk
> Import Data" in Books Online 2005:
> http://msdn2.microsoft.com/ms186286.aspx
> Razvan
>
Subscribe to:
Posts (Atom)