| Groups | Blog | Home |
sql server (alternate) : On to Bulk Insert issues
USE Alert_db; BULK INSERT funds FROM 'C:\\data\\myData.dat' WITH (FIELDTERMINATOR='\t', KEEPNULLS, ROWTERMINATOR='\r\n'); And I got the following errors. Msg 4864, Level 16, State 1, Line 3 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (f_asset_classes_id). Msg 4866, Level 16, State 8, Line 3 The bulk load failed. The column is too long in the data file for row 1, column 6. Verify that the field terminator and row terminator are specified correctly. Msg 7399, Level 16, State 1, Line 3 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 3 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". One limitation I encountered is that there doesn't seem to be a way to tell MS SQL Server that the fields are optionally enclosed by quotes. That is, text fields are enclosed by quotes while, e.g., numeric fields, are not, and that these optional quotes are NOT to be included in the data in the fields. I do not know what "State 1" vs "State 8" is supposed to mean. The table in question in this example allows nulls in several columns, and in the flat file, nulls are represented by consecutive tabs. Might this be causing trouble for the Bulk Insert statement? In other cases, where I have to use something like bulk insert, involves several columns containing dates. I know MS SQL supports the format used in the file (by reading the documentation for cast operations), but is there an easy way to tell MS SQL which of the supported date formats to use when reading this data. I've read bcp should be useful for this, but I have yet to figure that out. In about half of the cases where I load data from a file, the data is loaded once when the database is first created, and in the rest, there is new data to be loaded every business day; so I need to be able to submit the required command from the command line, and thus invoke it using a perl script. BTW: I have ordered a couple books on T-SQL, but they have yet to arrive. Thanks Ted
Ted (r.ted.byers@rogers.com) writes:
[quoted text, click to view] > One limitation I encountered is that there doesn't seem to be a way to > tell MS SQL Server that the fields are optionally enclosed by quotes. > That is, text fields are enclosed by quotes while, e.g., numeric > fields, are not, and that these optional quotes are NOT to be included > in the data in the fields. That's correct, if optionally means just optionally, so that you could have: 9;Some unquoted data;12;9.234;2004-12-12 19;"Some quoted data";-12;31.4;2003-02-23 But if a text column is consistently quoted, you can handle this with a format file where you specify each field. A format file that fits the second row in the example above could look like: 8.0 5 1 SQLCHAR 0 0 ";\"" 1 col1 "" 2 SQLCHAR 0 0 "\";" 2 col2 "" 3 SQLCHAR 0 0 ";" 3 col3 "" 4 SQLCHAR 0 0 ";" 4 col3 "" 5 SQLCHAR 0 0 "\r\n" 5 col3 "" The first row is the version of the file format. Next is the number of fields in the file. Following lines describe one field each. First column is record number. Second column is data type of the field in the file. For a text file this is always SQLCHAR or always SQLNCHAR for a Unicode file. Other data types are only used with binary formats. The third column is prefix-length, used only for binary files. Fourth column is the length, and is used for fixed-length fields. Fifth field is the terminator, and it is here you specify the quotes. Six column is the database column, with 1 denoting the first column. 0 means that this field is not to be imported. Seventh column is the column name, but it's informational. BCP/BULK INSERT does not use it. Last colunm is the collation for the data in the file. Overall, keep in mind that BCP/BULK INSERT reads a binary file and a row terminator is really only the terminator for the last field. [quoted text, click to view] > I do not know what "State 1" vs "State 8" is supposed to mean. You can consider it as white noise. The state number may tell the SQL Server developers something, but they are not documented. [quoted text, click to view] > The table in question in this example allows nulls in several columns, > and in the flat file, nulls are represented by consecutive tabs. Might > this be causing trouble for the Bulk Insert statement? That should work fine. However, if fields are missing, so that you have six fields on one line, and eight on the next, you lose. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
panic attack (tunc.ovacik@gmail.com) writes:
[quoted text, click to view] > it is a chance for me that such a topic has been opened :)) > so i can ask something that i need to know... > > well i am using BCP command to get a text file into SQL Server 2000 > but i am getting an error message like this one : > "string data, right truncation" > > and i have no idea how i am going to get over this problem!! > in what situations does the sql server 2000 return with such an error? > > what should i do to get over this problem? First start a new thread, so we can keep different problems apart. You can try the -e option to get errors to a file, you will then see which records in the file that provokes this error. I need however add the caveat that not all errors get listed in the error file, and I don't remember if this error gets lists. The two most plausible reasons for the error is 1) the file has data that does not fit the table columns. 2) there is an error with your delimiters, so that BCP gets out of sync. If that does not help, post the CREATE TABLE command for the table and the exact command line for BCP you are using. If you use a format file, please also include a format file. Finally, include a sample of the input file. Best is if the sample produces the error message. If the input file exceeds 80 characters in length include it as an attachment, so it does not get wrecked in news transport. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
hi all again...
it is a chance for me that such a topic has been opened :)) so i can ask something that i need to know... well i am using BCP command to get a text file into SQL Server 2000 but i am getting an error message like this one : "string data, right truncation" and i have no idea how i am going to get over this problem!! in what situations does the sql server 2000 return with such an error? what should i do to get over this problem? thanks a lot Tunc Ovacik ******************************************************************* [quoted text, click to view] Erland Sommarskog wrote:
> Ted (r.ted.byers@rogers.com) writes: > > One limitation I encountered is that there doesn't seem to be a way to > > tell MS SQL Server that the fields are optionally enclosed by quotes. > > That is, text fields are enclosed by quotes while, e.g., numeric > > fields, are not, and that these optional quotes are NOT to be included > > in the data in the fields. > > That's correct, if optionally means just optionally, so that you > could have: > > 9;Some unquoted data;12;9.234;2004-12-12 > 19;"Some quoted data";-12;31.4;2003-02-23 > > But if a text column is consistently quoted, you can handle this with a > format file where you specify each field. A format file that fits the > second row in the example above could look like: > > 8.0 > 5 > 1 SQLCHAR 0 0 ";\"" 1 col1 "" > 2 SQLCHAR 0 0 "\";" 2 col2 "" > 3 SQLCHAR 0 0 ";" 3 col3 "" > 4 SQLCHAR 0 0 ";" 4 col3 "" > 5 SQLCHAR 0 0 "\r\n" 5 col3 "" > > The first row is the version of the file format. Next is the number of > fields in the file. Following lines describe one field each. > > First column is record number. Second column is data type of the field > in the file. For a text file this is always SQLCHAR or always SQLNCHAR > for a Unicode file. Other data types are only used with binary formats. > > The third column is prefix-length, used only for binary files. Fourth > column is the length, and is used for fixed-length fields. Fifth field > is the terminator, and it is here you specify the quotes. > > Six column is the database column, with 1 denoting the first column. 0 > means that this field is not to be imported. Seventh column is the > column name, but it's informational. BCP/BULK INSERT does not use it. > Last colunm is the collation for the data in the file. > > Overall, keep in mind that BCP/BULK INSERT reads a binary file and a > row terminator is really only the terminator for the last field. > > > I do not know what "State 1" vs "State 8" is supposed to mean. > > You can consider it as white noise. The state number may tell the > SQL Server developers something, but they are not documented. > > > The table in question in this example allows nulls in several columns, > > and in the flat file, nulls are represented by consecutive tabs. Might > > this be causing trouble for the Bulk Insert statement? > > That should work fine. However, if fields are missing, so that you > have six fields on one line, and eight on the next, you lose. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks Erland,
To deal with the quotes (because your example doesn't show what happens if the first column in the file is quoted text), I tried the data import wizard from within SQL Server Management Studio. For most of the data files/table combinations, it worked well. However, there are issues, especially related to nulls. 1) With one file, some of which goes into one tabe and some goes into another, the last column contains null values, and consequently the load fails. The key error seems to be: Error 0xc020901c: Data Flow Task: There was an error with input column "Dist_Unit" (67) on input "Destination Input" (51). The column status returned was: "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard) Dist_Unit is the last column and more often than not contains null values. This is especially puzzling since the wizard, when asked to show a preview, properly displays the data without complaint. 2) With a different data file/table, there are two fields containing integers, both of which could contain nulls, and while the wizard will import the data without complaint, it silently converts the nulls to zero. Is there a way to tell the wizard to keep the nulls as nulls? If so, might this fix the problem in item #1? 3) I am trying to populate a lookup table from data used in item #1. Of course, in that file, there will be multiple occurances of most supplier code/supplier name pairs (one for each product supplied by the supplier). This leads to the wizard complaining about violating the primary key. Is there a way to tell the wizard to ignore duplicate records? 4) Each time I tried the wizard, I told it to store a package on the server. However, I can't seem to find these packages. Where should I be looking for them, and can I tell SQL Server Management Studio to export the packages as scripts I can invokve from the commandline? Thanks Ted
BTW: I examined the problematic data files using Open Office's Writer,
configured to show non-printable characters, and invariably the number of fields is correct, with the right number of tabs. Ted
I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this leads to an equally serious problem. All the nulls in that column are silently converted into zero. This represents a major distortion of the meaning of the column. For this column, zero carries a very different meaning from null. Worse, since the column had many records in which the value was zero, it is not possible after the insert to recover the nulls! How can I tell the data import wizard to preserve my nulls? Ted
[quoted text, click to view] Erland Sommarskog wrote: Actually, I have been experimenting with BULK INSERT, bcp, and the> Ted (r.ted.byers@rogers.com) writes: > > To deal with the quotes (because your example doesn't show what happens > > if the first column in the file is quoted text), I tried the data > > import wizard from within SQL Server Management Studio. For most of > > the data files/table combinations, it worked well. However, there are > > issues, especially related to nulls. > > Wait a minute, last night you were using BULK INSERT, now you are using > the Import Wizard which uses SQL Integration Services that I know next > to nothing about. So I cannot assist with that part. > import wizard simultaneously. I find some inconsistency in how they work. With bulk insert, it seems I can tell it to preserve my nulls, and I haven't found out how to do that with the wizard (which is accessable in SQL Server Management Studio by selecting the database and then, from the popup menu selecting either import data or export data). OTOH, the wizard lets me specify in the second dialog that the text fields are enclosed by quotes while it seems I may be only able to do that by creating a format file for use by bcp or bulk insert. [quoted text, click to view] > > 4) Each time I tried the wizard, I told it to store a package on the It is accessable in Mgmt Studio through the popup menu accessible on> > server. However, I can't seem to find these packages. Where should I > > be looking for them, and can I tell SQL Server Management Studio to > > export the packages as scripts I can invokve from the commandline? > > As I understand it, Mgmt Studio does not offer any interface to > Integration Services. To this end you should use Business Intelligence > Development Studio. > each database on the server. [quoted text, click to view] > > I solved the problem with errors (item #1 in my previous post) by more No. On that particular table, there are no default values.> > carefully specifying the data type of the input file columns. But this > > leads to an equally serious problem. All the nulls in that column are > > silently converted into zero. > > Just a stupid check: you don't happen to have a default of 0 on those > columns. > Thanks Ted
Ted (r.ted.byers@rogers.com) writes:
[quoted text, click to view] > To deal with the quotes (because your example doesn't show what happens > if the first column in the file is quoted text), I tried the data > import wizard from within SQL Server Management Studio. For most of > the data files/table combinations, it worked well. However, there are > issues, especially related to nulls. Wait a minute, last night you were using BULK INSERT, now you are using the Import Wizard which uses SQL Integration Services that I know next to nothing about. So I cannot assist with that part. [quoted text, click to view] > 4) Each time I tried the wizard, I told it to store a package on the > server. However, I can't seem to find these packages. Where should I > be looking for them, and can I tell SQL Server Management Studio to > export the packages as scripts I can invokve from the commandline? As I understand it, Mgmt Studio does not offer any interface to Integration Services. To this end you should use Business Intelligence Development Studio. [quoted text, click to view] > I solved the problem with errors (item #1 in my previous post) by more > carefully specifying the data type of the input file columns. But this > leads to an equally serious problem. All the nulls in that column are > silently converted into zero. Just a stupid check: you don't happen to have a default of 0 on those columns. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
many thanks...
my problem has been solved. tunc [quoted text, click to view] Erland Sommarskog wrote:
> panic attack (tunc.ovacik@gmail.com) writes: > > it is a chance for me that such a topic has been opened :)) > > so i can ask something that i need to know... > > > > well i am using BCP command to get a text file into SQL Server 2000 > > but i am getting an error message like this one : > > "string data, right truncation" > > > > and i have no idea how i am going to get over this problem!! > > in what situations does the sql server 2000 return with such an error? > > > > what should i do to get over this problem? > > First start a new thread, so we can keep different problems apart. > > You can try the -e option to get errors to a file, you will then see which > records in the file that provokes this error. I need however add the caveat > that not all errors get listed in the error file, and I don't remember if > this error gets lists. > > The two most plausible reasons for the error is > 1) the file has data that does not fit the table columns. > 2) there is an error with your delimiters, so that BCP gets out of sync. > > If that does not help, post the CREATE TABLE command for the table and the > exact command line for BCP you are using. If you use a format file, please > also include a format file. Finally, include a sample of the input file. > Best is if the sample produces the error message. If the input file > exceeds 80 characters in length include it as an attachment, so it does > not get wrecked in news transport. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Ted (r.ted.byers@rogers.com) writes:
[quoted text, click to view] > Erland Sommarskog wrote: >> As I understand it, Mgmt Studio does not offer any interface to >> Integration Services. To this end you should use Business Intelligence >> Development Studio. >> > It is accessable in Mgmt Studio through the popup menu accessible on > each database on the server. Yeah, I know about those. What I meant to say is that if you want to look inside the packages, you will have enter Business Intelligence Development Studio. (Which I have never visited myself. I'm completely unintelligent when it comes to business.) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Ted (r.ted.byers@rogers.com) writes:
[quoted text, click to view] > Actually, I have been experimenting with BULK INSERT, bcp, and the > import wizard simultaneously. I find some inconsistency in how they > work. With bulk insert, it seems I can tell it to preserve my nulls, > and I haven't found out how to do that with the wizard (which is > accessable in SQL Server Management Studio by selecting the database > and then, from the popup menu selecting either import data or export > data). I tried to use the wizard and import a file which did not have all values for an int column, but I got the same error as you. Overall, the wizard strained my patience, so I gave up after a while. It's certainly more efficient with format files. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |