Hi to everybody!
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David GrantThe best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
Showing posts with label fixed. Show all posts
Showing posts with label fixed. Show all posts
Thursday, March 29, 2012
Bulk Load: Is there any substitute for the fixed="..." modifier
Hi to everybody!
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David Grant
The best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation
|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
I have to Bulk Load an XML file whose Schema Definition has
some fixed="..." modifiers. After noticing something wrong
with these elements, I've found in this newsgroup that
sqlxml doesn't support the fixed="..." modifier. However,
if I substitute the fixed="VALUE-1" modifier with a
default="VALUE-1" modifier I can't prevent Bulk Load to
accept values different from "VALUE-1".
Does anybody know how to code the fixed="..." modifiers in
the Schema Definition so that XML Bulk Load can accept them?
TIA
Greetings,
David Grant
The best way to acheive this sort of functionality is to validate the
source document before loading via SqlXmlBulkload.
SqlXml does a limited amount of validation of source Xml documents, but it
is not a full validation mechanism. For example, there are cases where the
bulkload component would load invalid source Xml.
Andrew Conrad
Microsoft Corporation
|||Thank you for your quick response, Andrew.
Greetings,
David Grant
>--Original Message--
>The best way to acheive this sort of functionality is to
validate the
>source document before loading via SqlXmlBulkload.
>SqlXml does a limited amount of validation of source Xml
documents, but it
>is not a full validation mechanism. For example, there
are cases where the
>bulkload component would load invalid source Xml.
>Andrew Conrad
>Microsoft Corporation
>.
>
Sunday, March 25, 2012
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!!!!
Saturday, February 25, 2012
Bulk Insert - Fixed Length File
I'm trying to do an insert using Bulk Insert with a fixed length file.
I'm using a format file.
I'm getting the following error message:
Cannot perform bulk insert. Invalid collation name for source column 16
in format file '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'.
I was not able to repeat this. Then again, I was not able to import the
file successfully either, but the error message I got was:
is not what you think it is.
I'm using a format file.
I'm getting the following error message:
Cannot perform bulk insert. Invalid collation name for source column 16
in format file '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'.
Any suggestions are appreciated.
Thanks!
Jennifer
Format File Contents:
8.0
16
1SQLCHAR02""0Space""
2SQLCHAR04""1YearID""
3SQLCHAR02""0Space""
4SQLCHAR02""2PeriodID""
5SQLCHAR02""3CompanyID""
6SQLCHAR01""0Dash""
7SQLCHAR04""0Space""
8SQLCHAR01""0Dash""
9SQLCHAR04""4UnitID""
10SQLCHAR01""0Dash""
11SQLCHAR04""5AccountCode""
12SQLCHAR05""0Space""
13SQLCHAR01""6AccountType""
14SQLCHAR029""0Space""
15SQLCHAR016""7GLAmount""
16SQLCHAR0105"\r\n"0Space""
Bulk Insert Statement:
BULK INSERT FlatFile_GOP
FROM '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GLPAM.GOP'
WITH
(
FORMATFILE =
'\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'
)
Table Definition:
CREATE TABLE [dbo].[FlatFile_GOP] (
[YearID] [smallint] NOT NULL ,
[PeriodID] [smallint] NOT NULL ,
[CompanyID] [smallint] NOT NULL ,
[UnitID] [smallint] NOT NULL ,
[AccountCode] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[GLBalance] [money] NOT NULL
) ON [PRIMARY]
GO
File Contents:
2007 210- -0002-3000 G
196395.10
2007 210- -0002-3700 B
1484.00
2007 210- -0002-3700 G
1571.13
2007 210- -0002-3800 B
157457.00
2007 210- -0002-3800 G
161577.73Jennifer (J.Evans.1970@.gmail.com) writes:
Quote:
Originally Posted by
I'm trying to do an insert using Bulk Insert with a fixed length file.
I'm using a format file.
I'm getting the following error message:
Cannot perform bulk insert. Invalid collation name for source column 16
in format file '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'.
I was not able to repeat this. Then again, I was not able to import the
file successfully either, but the error message I got was:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 15
(GLBalance).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 15
(GLBalance).
which makes me think that the format file was interpreted OK. (And the
reason for the error is simply that your file was mashed in news transport,
and I did not really make any effort to resurrect it.)
Since column 16 is a column you are not even importing the message seems
funny to me. My prime suspicions that the contents of
Quote:
Originally Posted by
'\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'
is not what you think it is.
--
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
Subscribe to:
Posts (Atom)