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

Jun 5, 2015

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

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

[Code] ....

My format file:

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

[Code] .....

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

My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS

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

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

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

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

View 7 Replies


ADVERTISEMENT

Transact SQL :: Format File For Bulk Insert

May 16, 2012

I need fmt(format ) file for below values

“Stuid”,”Stuname”,”Class”,”DOJ”,”English”,”Math”,”Science”
"S1","Ram","10/31/2011,Monday",40,32,50
"S2","Bala","10/31/2011,Monday",50,45,69
"S3","Sam","10/31/2011,Monday",74,78,79
"S4","Jon","10/31/2011,Monday",65,58,89
"S5","Jos","10/31/2011,Monday",41,25,69
"S6","Jim","10/31/2011,Monday",74,41,41
"S7","Jack","10/31/2011,Monday",98,57,47
"S8","Sate","10/31/2011,Monday",87,73,45
"S9","Brb","10/31/2011,Monday",47,89,65
"S10","Jom","10/31/2011,Monday",14,100,47

View 15 Replies View Related

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

Jun 29, 2015

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

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')

[code]...

View 5 Replies View Related

Error When I Try To Bulk Insert With A Format File

Oct 1, 2007



Msg 4862, Level 16, State 1, Server PATH-SQLDEV, Line 2
Cannot bulk load because the file "c:DATABATCHBCPFormat.fmt" could not be read. Operating system error code (null).

Above is the error I get. The problem is I do not know what is causing this error. It occurs when I attempt to use SQLCMD with bulk insert.

I am using SQLserver 2005 and I have a similar set up in a test database that works, why this format file does not is beyond me, but my experience is when the format file has an error in it, such as a mispelled datatype or a incorrect column number, the error will zero in on that, rather then declare the entire file unreadable. Furthermore if I go into the file, change something so where it is incorrect (like a column number) it will zero in on that error. So I know that the format file can be read. If I knew what this error was all about I would at least know where to begin in fixing it. I have also tried using a very small sample file for the data being inserted. Same error.

Please help

View 2 Replies View Related

SQL Server 2012 :: Bulk Insert Error With XML Format File?

Nov 9, 2014

I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.

Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.

SQL Server Table

[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),

[code]....

View 0 Replies View Related

SSIS BULK INSERT Error: File Format Doest Not Exist

Apr 11, 2007

My colleague is working on bulk insert task from SSIS and since the data file does not contain any valid delimeter one of the suggestion he got is to use a file format to address the issue. Thus a bcp command is used to generate the format file, as per below.



bcp <database name>.dbo.<table name> format nul -T -S <server name> -n -f out.fmt



The file file format was generated, from the data flow we added the BULK INSERT task and set the properties accordingly including the File Format and location of the file. Upon running the task itself we encountered the error as per below.



[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. The file "C:HFISTAT.fmt" does not exist.".

Progress: The Bulk Insert task is completed. - 100 percent complete

Task Bulk Insert Task failed



Have checked the file and it is in C: drive and it is not protected or read-only. Validated the output file and it is as per expected. Any help would be appreciated very much.

View 7 Replies View Related

Msg 4820 Bulk Insert Error -- Unknown Version Of Format File

Mar 27, 2007

Hi all,


ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"


Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.


Any ideas? have you seen this before?


NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.


Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx


TO REPRODUCE:


* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))


* Data File Template:
Age<tab>Firstname<tab>Lastname<return>


* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>


* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');


Thanks in advanced for any feedback.
Cheers!

View 1 Replies View Related

A Msg 4820 Bulk Insert Error -- Unknown Version Of Format File

Mar 27, 2007

Below is an overview of my problem:



ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"


Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.


Any ideas? have you seen this before?


NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.


Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx


TO REPRODUCE:


* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))


* Data File Template:
Age<tab>Firstname<tab>Lastname<return>


* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>


* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');


Thanks in advanced for any feedback.

View 1 Replies View Related

Bulk Insert Using Format File.......

Apr 8, 2001

I want to use the bulk insert statement to insert data from a text file that contains more columns than the target sql table does. I am using SQL 7.0.

I am using a format file, but I can't work out how to achieve the above. SQL books online (and the msdn website) do not describe how to do this, but it is intimated that it can be done.

Any suggestions ?

Regards,

Stuart.

View 1 Replies View Related

Bulk Insert From Csv Using XML Format File

Apr 4, 2008



Hello, I am doing a bulk insert using a XML Format file from a csv file. Most everything works just fine, but my delimiter is a , the problem is one of the column sets sometimes contains a , within "" like this:

value1,"value,2",value3

So when I do my insert it is distorting the column values because unlike excel it is not ignoring the comma within the quotes. Is there any way to set an attribute within the format file to prevent this from happening?

View 2 Replies View Related

BUlk Insert Using Format File

Jan 17, 2008

I Have a fixed width file where the format look like below:
f1 - 16
f2 - 64
f3 - 64..
....etc


and the format file that i created looks like:
8.0
20
1 SQLCHAR 0 16 "" 0 ExtraField
2 SQLCHAR 0 64 "" 0 ExtraField
3 SQLCHAR 0 64 "" 0 ExtraField
4 SQLCHAR 0 16 "" 1 DatabaseName
5 SQLCHAR 0 128 "" 0 ExtraField
6 SQLCHAR 0 24 "" 2 DelivaryDay
7 SQLCHAR 0 4 "" 0 ExtraField
8 SQLCHAR 0 3 "" 0 ExtraField
9 SQLCHAR 0 24 "" 0 ExtraField
10 SQLCHAR 0 3 "" 0 ExtraField
11 SQLCHAR 0 24 "" 0 ExtraField
12 SQLCHAR 0 64 "" 0 ExtraField
13 SQLCHAR 0 24 "" 0 ExtraField
14 SQLCHAR 0 24 "" 0 ExtraField
15 SQLCHAR 0 24 "" 3 CompleteDate
16 SQLCHAR 0 24 "" 0 ExtraField
17 SQLCHAR 0 24 "" 0 ExtraField
18 SQLCHAR 0 24 "" 0 ExtraField
19 SQLCHAR 0 24 "" 0 ExtraField
20 SQLCHAR 0 256 "" 0 ExtraField


I need to take only three coulmns from the file and the text file won't contain any delimeters between the fields.
I tried to execute this using Bulk..Insert and i am getting the error:

Cannot bulk load. Invalid column number in the format file "C:sampleXXXX.fmt"

Any one can help me what is the problem here?
I am using SQL Server 2000.

View 1 Replies View Related

Bulk Insert Format File

Aug 24, 2007

Hi ,
I was wondering if there was a way in a format file to load a host file data field to more than one column in a table?

Thanks

View 1 Replies View Related

Using A Format File With BULK INSERT

May 21, 2008

Hi,

im attempting the following bulk insert statement:
BULK INSERT gtaRatesTemp FROM 'D: estData.CSV' WITH (FIRSTROW = 3, FORMATFILE = 'D: estFormat.Fmt'

testData.CSV example:




"Country","Country Code","City","City Code","Currency","Item","Item Code","Address 1","Address 2","Address 3","Address 4","Telephone","Rating","Location 1","Location 2","Location 3","No. of","Dates","Dates","Days","Days","Min","Min","Basis","Twin","Single","Triple","Quad","Twin for sole use","Child","Child Age","Child Age","Meals included","Distance to City Centre (Kms)","Child Details","Special Conditions"
"","","","","","","","","","","","","","","","","Rooms","From","To","From","To","Pax","Nights","","Price","Price","Price","Price","Price","Price","From","To","","",""
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","14 Jun 2007","30 Jun 2007","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Jul 2007","31 Oct 2007","Mon","Sun","1","2","Room","231.00","231.00","","","231.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Nov 2007","31 Mar 2008","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","14 Jun 2007","30 Jun 2007","Mon","Sun","1","1","Room","412.50","412.50","","","412.50","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; "
"Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","01 Jul 2007","30 Nov 2007","Mon","Sun","1","1","Room","463.00","463.00","","","463.00","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; "
testFormat.Fmt:




8.0
36
1 SQLCHAR 0 50 """ 0 country SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "","" 1 countryCode SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 200 "","" 2 city SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "","" 3 cityCode SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "","" 4 currency SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 500 "","" 5 item SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "","" 6 itemCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 400 "","" 7 address1 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 400 "","" 8 address2 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 400 "","" 9 address3 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 400 "","" 10 address4 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "","" 11 telephone SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "","" 12 rating SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "","" 13 location1 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "","" 14 location2 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "","" 15 location3 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "","" 16 noOfRooms SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "","" 17 datesFrom SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 100 "","" 18 datesto SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 50 "","" 19 daysFrom SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "","" 20 daysTo SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 100 "","" 21 minPAX SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 100 "","" 22 minNights SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 50 "","" 23 basis SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 100 "","" 24 twinPrice SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 100 "","" 25 singlePrice SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 100 "","" 26 triplePrice SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 100 "","" 27 quadPrice SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 100 "","" 28 twinForSoleUsePrice SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 100 "","" 29 childPrice SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 100 "","" 30 childAgeFrom SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 100 "","" 31 childAgeTo SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "","" 32 mealsIncluded SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 50 "","" 33 distanceToCityCentre SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 500 "","" 34 childDetails SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1500 ""
" 35 specialConditions SQL_Latin1_General_CP1_CI_AS
My problem is that once inserted, column 35(childDetails) and 36(specialConditions) are being merged into one. I think it may be an error with one of the values i have for the terminator in the format file but im unsure what exactly.

Does anyone have any ideas?

View 9 Replies View Related

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

Jan 2, 2008

Hey All,

Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.

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




Example of Data

0124015Apple Crate 32.12

0124016Bananna Box 12.56

0124017Mango Carton 15.98

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

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

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




Code in C# Form

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

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

SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.



Thanks for your time,


Hayden.

View 1 Replies View Related

SQL Server 2008 :: Bulk Insert With A Format File?

Mar 5, 2015

I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that how to create one.

View 3 Replies View Related

Bulk Insert From Native Format Data File.

Dec 5, 2006

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

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



 Any one help?

View 5 Replies View Related

Inconsistent Errors Using Bulk Insert With A Format File

May 16, 2006

As part of a c# program, utilizing .Net 2.0, I am calling a sproc via a SqlCommand to bulk load data from flat files to a various tables in a SQL Server 2005 database. We are using format files to do this, as all of the incoming flat files are fixed length. The sproc simply calls a T-SQL BULK INSERT statement, accepting the file name, format file name and the database table as input paramaters. As expected, this works most of the time, but periodically (to often for a production environment), the insert fails. The particular file to fail is essentially random and when I rerun the process, the insert completes successfully.
A sample of the error messages returned is as follows (@sql is the string executed):
Cannot bulk load. Invalid destination table column number for source column 1 in the format file "\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt".
Starting spRAS_BulkInsertData.
@sql = BULK INSERT Raser.dbo.EMODT3_Work FROM '\RASDMNTTRAS_ROOTAmeriHealthworkpdclmsemodt3.20060511.0915.txt.DATA' WITH (FORMATFILE = '\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt');

The format file for this particular example is as follows (I apologize for the length):
8.0
62
1 SQLCHAR 0 1 "" 1 Record_Type SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "" 2 Vendor_Number SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 20 "" 3 Extract_Subscriber_Number SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "" 4 Extract_Member_Number SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 2 "" 5 Claim_Nbr_Branch_Code SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 8 "" 6 Claim_Nbr_Batch_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 3 "" 7 Claim_Nbr_Batch_Sequence_Nbr SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 3 "" 8 Claim_Nbr_Sequence_Number SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 3 "" 9 LINE_NUMBER SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "" 10 Patient_Sex_Code SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 3 "" 11 Patient_Age SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 4 "" 12 G_L_Posting_Tables_Code SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 50 "" 13 G_L_Posting_Tbls_Code_Desc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 2 "" 14 Fund_TYPE SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 1 "" 15 Stop_Loss_Or_Step_Down_Code SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 2 "" 16 Stop_Loss_Fund SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 50 "" 17 Stop_Loss_Fund_Desc SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 8 "" 18 Post_Date SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 1 "" 19 Rebundling_Status_Indicator SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "" 20 Co_Payment_Grouper SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 50 "" 21 Co_Payment_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 8 "" 22 Co_Payment_Accumulator SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 50 "" 23 Co_Payment_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 8 "" 24 Co_Insurance_Grouper SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 50 "" 25 Co_Insurance_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 8 "" 26 Co_Insurance_Accumulator SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 50 "" 27 CI_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 8 "" 28 Coverage_Grouper SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 50 "" 29 Coverage_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 8 "" 30 Coverage_Accumulator SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 50 "" 31 Coverage_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 8 "" 32 Deductible_Grouper SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 50 "" 33 Deductible_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 8 "" 34 Deductible_Accumulator SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 50 "" 35 Deductible_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 8 "" 36 Unit_Grouper SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 50 "" 37 Unit_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 8 "" 38 Unit_Accumulator SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 50 "" 39 Unit_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 8 "" 40 Out_Of_Pocket_Grouper SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 50 "" 41 Out_Of_Pocket_Grouper_Desc SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 8 "" 42 Out_Of_Pocket_Accumulator SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 50 "" 43 Out_Of_Pocket_Acc_Desc SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 3 "" 44 Service_Edit_Code SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 50 "" 45 Service_Edit_Code_Desc SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 8 "" 46 System_Date_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 8 "" 47 Last_Change_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 10 "" 48 Medicare_Termination_Reason_Code SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 10 "" 49 User_ID_MEDMAS SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 10 "" 50 User_ID_Last_Modified SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 8 "" 51 Adjudication_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 9 "" 52 Adjudication_Time SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 10 "" 53 Adjudication_User_ID SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 9 "" 54 A_P_Batch_Number SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 7 "" 55 A_P_Sequence SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 3 "" 56 CPA_Batch_Number SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 8 "" 57 CPA_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 1 "" 58 Manual_Authorization_Flag SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 50 "" 59 Fund_Description SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 1 "" 60 DRG_Inclusion_Indicator SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 1 "" 61 Future_Expansion SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 2 "
" 62 Company_Number SQL_Latin1_General_CP1_CI_AS



Has anyboy run across this before, or have any ideas as to what might be happening?
Thanks in advance.

View 6 Replies View Related

BULK INSERT, Setting Static Data Using The Format File

Mar 2, 2004

Hello dbforums,

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

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

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

More Questions:

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

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

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

Thanks,
Chris

View 6 Replies View Related

Transact SQL :: Additional Column With Bulk Insert?

Aug 11, 2015

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

View 2 Replies View Related

Transact SQL :: CSV File - Bulk Insert Row Terminator

Nov 5, 2010

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

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

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

,
,
,
, CRLF,

View 31 Replies View Related

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

Aug 5, 2015

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

[code]....

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

View 15 Replies View Related

BULK INSERT Format File Problems With SQLNUMERICAL In SQL-Server 2005 Express

May 24, 2006

I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.

The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)

and written the following format file...

9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""

However... actually running this gives the following error...

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

or

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...


Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

or

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Also - there was the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

View 1 Replies View Related

BULK INSERT Format File Problems With SQLNUMERICAL In SQL-Server 2005 Express

May 24, 2006

I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.

The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)

and written the following format file...

9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""

However... actually running this gives the following error...

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

or

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...


Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

or

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

View 1 Replies View Related

BULK INSERT Format File Problems With SQLNUMERICAL In SQL-Server 2005 Express

May 24, 2006

I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.

The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "

Looking with a hex editor, all the above whitespace are 20's.

From the documentation, I've constructed the following table...

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)

To Bulk Insert this, I've written the following...

BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)

and written the following format file...

9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""

However... actually running this gives the following error...

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.

It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.

Even the following trivial example doesn't work for this field of data...

"7999163 "

CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)

and

9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""

or

9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""

which give this error...


Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.

or

9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""

which gives this error...

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,

Any thoughts on this would be greatly appreciated...

Thanks!

View 5 Replies View Related

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

Sep 15, 2015

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

My statement looks like this:

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

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

View 2 Replies View Related

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

Dec 3, 2015

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

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

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

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

I found this snip at

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

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

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

I have verified the SPNs:

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

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

View 8 Replies View Related

SQL Server 2008 :: BCP XML File Format Error Invalid Ordinal For Field 2

Oct 1, 2010

When creating xml fileformat its throwing me error "invalid ordinal".

When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.

C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.

Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
Item Numbervarcharno18 noyesnoSQL_Latin1_General_CP1_CI_AS
Description1nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
Description2nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
UMvarcharno3 yesyesyesSQL_Latin1_General_CP1_CI_AS

View 1 Replies View Related

BULK INSERT Flat File With Only One Column

Jan 30, 2004

Hi,

I have a text file with a single column that i need to bulk insert into a table with 2 colums - an ID (with identity turned on) and col2

my text file looks like:

row1
row2
row3
...
row10

so my bulk insert i have like this:
BULK INSERT test FROM 'd: estBig.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
'
)

but i get the error:

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

However, as you can see from the text file, there is only one column, so i dont have any field terminators.

Any ideas how to make this work?

Thanks.

View 4 Replies View Related

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

Feb 3, 2010

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

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

View 6 Replies View Related

Incorrect Host-column Number Found In BCP Format-file

Jul 20, 2005

Hi guys, would appreciate if you can shed some light on this.Sorry to be a pain, can you tell me what is wrong with the following:for /F %%i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in%%i -fc:cpcp.fmt -T -S CHICKYywhere CHICKYy is the serverbcp.fmt8.00.19461 SQLCHAR 0 20 ", " 0 filler_1 ""2 SQLCHAR 0 8 "
" 1 computer_name ""3 SQLCHAR 0 20 ", " 0 filler_2 ""4 SQLCHAR 0 16 "
" 2 ip_address ""5 SQLCHAR 0 20 ", " 0 filler_3 ""6 SQLCHAR 0 60 "
" 3 operating_system ""pc1.txt and other *.txt format is:JW_193801,192.168.1.1,Windows XP,when I run it I get:C:cp>for /F %i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in%i -fc:cpcp.fmt -T -S CHICKYyC:cp>bcp Inventory..pc in pc1.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc2.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc3.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc4.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc5.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileThe sql table has 3 columns:Sorry to be a pain.-----------------------------------------------------------------------"Are you still wasting your time with spam?...There is a solution!"Protected by GIANT Company's Spam InspectorThe most powerful anti-spam software available.http://mail.spaminspector.com

View 1 Replies View Related

Bulk Insert - Inserting Only One Column From Data File

Sep 29, 2007



Hi,

I have a data file in the folloing format

SubjectId1|class1
SubjectId2|class2
SubjectId3|class3


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

Table Subjects
{

ID (Autoincrement)
SubjectId varchar(20)
}

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

Thank u
~mohan

View 5 Replies View Related

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

Jun 27, 2006

Hi,

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

BULK INSERT rsk_staging
FROM 'c: empulk
sk.txt'
  WITH (
    FIELDTERMINATOR = '',
    ROWTERMINATOR = '
',
    CODEPAGE = 'RAW',
    DATAFILETYPE  = 'char',
    BATCHSIZE = 100000,
    ROWS_PER_BATCH = 1925604,
    TABLOCK
  )

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

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

How can I resolve this problem?

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

View 1 Replies View Related

Problem With BULK INSERT ASCII File Into Nvarchar Column

Aug 21, 2007



Hi,
I have a problem with BULK INSERT. I created the following table:




Code Snippetcreate table Test
(id char(4), name nvarchar(16), last char(1))



I am trying to bulk insert data from ASCII (not unicode) file with only two rows:
0011First name
0018Second name



Since it is a fixed length file, I am using the following format file:




Code Snippet
8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "
" 3 Last HEBREW_CI_AS





With bcp utility everything works just fine!



Code Snippet
bcp Demo.dbo.test in c: est -T -f c: est.fmt





But when I use BULK INSERT in the following form:



Code Snippet
BULK INSERT Test FROM 'c:Test'
WITH
(
FORMATFILE='c:Test.fmt',
CODEPAGE='OEM'
);



I am getting error
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).

Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.
Can anybody explain what is going on here?

I am using MS SQL 2000 and MSDE

Thanks in advance,
Eugene.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved