Bulk Insert Format file prblem
-
Hi,
When I execute below statement it throws below errors.
DECLARE @SQLString nvarchar(2000)
SET @SQLString = 'BULK INSERT US..test1
FROM ''D:\test.dat''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n'',
FORMATFILE = ''D:\nnf.txt'',
FIRSTROW = 2
)'
PRINT @SQLString
EXEC SP_EXECUTESQL @SQLString
ERRORS:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
I have created format file using bcp statement.
Exec master..xp_cmdShell 'bcp US..TEST1 format -c -f "D:\nnf.txt" -S SERVERNAME -U SA -N -T -t "|"'
Can someone help me in this?
Regards,
Mahesh
-
just check if the bulk insert works as a single TSQL statement. I hope that hsould. if so the problem is
EXEC SP_EXECUTESQL @SQLString
change as
EXEC SP_EXECUTESQL(@SQLString)
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
1. What is structure of Test1 table?
2. Can you provide sample data (1 or 2 rows) from test.dat file?
3. SQL 2000/SQL 2005?
4. Can you check SQL ERRORLOG (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG) for any error message?
-
Test1 table contains around 40 fields with VARCHAR(255) and also I tried with proper data types. still same error.
Can't provide the data
using SQL 2000.
NO Log file.
-
Anybody can help me in this?
-
Yes... why have you defined both a delimiter and a row teminator when you are using a format file?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How to upload my sample file to this email thread? I want to give my sample text file and also table format file, so that any of you can help me in this.
Regards,
Mahesh
-
Copy... paste... don't post a bazillion lines.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Okay.
Table Format
8.0
46
1 SQLNCHAR 2 510 "|" 1 Token SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 510 "|" 2 Symbol SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 510 "|" 3 Series SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 510 "|" 4 InstrumentType SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 2 510 "|" 5 IssuedCapital SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 510 "|" 6 PermittedToTrade SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 2 510 "|" 7 CreditRating SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 2 510 "|" 8 SecurityStatus1 SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 510 "|" 9 Eligibility1 SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 2 510 "|" 10 SecurityStatus2 SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "|" 11 Eligibility2 SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 2 510 "|" 12 SecurityStatus3 SQL_Latin1_General_CP1_CI_AS
13 SQLNCHAR 2 510 "|" 13 Eligibility3 SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 2 510 "|" 14 SecurityStatus4 SQL_Latin1_General_CP1_CI_AS
15 SQLNCHAR 2 510 "|" 15 Eligibility4 SQL_Latin1_General_CP1_CI_AS
16 SQLNCHAR 2 510 "|" 16 BoardLotQty SQL_Latin1_General_CP1_CI_AS
17 SQLNCHAR 2 510 "|" 17 TickSize SQL_Latin1_General_CP1_CI_AS
18 SQLNCHAR 2 510 "|" 18 Name SQL_Latin1_General_CP1_CI_AS
19 SQLNCHAR 2 510 "|" 19 IssueRate SQL_Latin1_General_CP1_CI_AS
20 SQLNCHAR 2 510 "|" 20 IssueStartDate SQL_Latin1_General_CP1_CI_AS
21 SQLNCHAR 2 510 "|" 21 IssueIPDate SQL_Latin1_General_CP1_CI_AS
22 SQLNCHAR 2 510 "|" 22 IssueMaturityDate SQL_Latin1_General_CP1_CI_AS
23 SQLNCHAR 2 510 "|" 23 FreezePercent SQL_Latin1_General_CP1_CI_AS
24 SQLNCHAR 2 510 "|" 24 ListingDate SQL_Latin1_General_CP1_CI_AS
25 SQLNCHAR 2 510 "|" 25 ExpulsionDate SQL_Latin1_General_CP1_CI_AS
26 SQLNCHAR 2 510 "|" 26 ReAdmissionDate SQL_Latin1_General_CP1_CI_AS
27 SQLNCHAR 2 510 "|" 27 ExDate SQL_Latin1_General_CP1_CI_AS
28 SQLNCHAR 2 510 "|" 28 RecordDate SQL_Latin1_General_CP1_CI_AS
29 SQLNCHAR 2 510 "|" 29 NoDeliveryStartDate SQL_Latin1_General_CP1_CI_AS
30 SQLNCHAR 2 510 "|" 30 NoDeliveryEndDate SQL_Latin1_General_CP1_CI_AS
31 SQLNCHAR 2 510 "|" 31 ParticipateInIndex SQL_Latin1_General_CP1_CI_AS
32 SQLNCHAR 2 510 "|" 32 AON SQL_Latin1_General_CP1_CI_AS
33 SQLNCHAR 2 510 "|" 33 MinFill SQL_Latin1_General_CP1_CI_AS
34 SQLNCHAR 2 510 "|" 34 WarningPercent SQL_Latin1_General_CP1_CI_AS
35 SQLNCHAR 2 510 "|" 35 BookClosureStartDate SQL_Latin1_General_CP1_CI_AS
36 SQLNCHAR 2 510 "|" 36 BookClosureEndDate SQL_Latin1_General_CP1_CI_AS
37 SQLNCHAR 2 510 "|" 37 Dividend SQL_Latin1_General_CP1_CI_AS
38 SQLNCHAR 2 510 "|" 38 Rights SQL_Latin1_General_CP1_CI_AS
39 SQLNCHAR 2 510 "|" 39 Bonus SQL_Latin1_General_CP1_CI_AS
40 SQLNCHAR 2 510 "|" 40 Varchar(255)erest SQL_Latin1_General_CP1_CI_AS
41 SQLNCHAR 2 510 "|" 41 AGM SQL_Latin1_General_CP1_CI_AS
42 SQLNCHAR 2 510 "|" 42 EGM SQL_Latin1_General_CP1_CI_AS
43 SQLNCHAR 2 510 "|" 43 Remark SQL_Latin1_General_CP1_CI_AS
44 SQLNCHAR 2 510 "|" 44 LocalDBUpdateDateTime SQL_Latin1_General_CP1_CI_AS
45 SQLNCHAR 2 510 "|" 45 DeleteFlag SQL_Latin1_General_CP1_CI_AS
46 SQLNCHAR 2 510 "" 46 FaceValue SQL_Latin1_General_CP1_CI_AS
Data File Records
NEATCM|8.07.00|867850731
11567|0559F16|GC|4|6.00000000000000E+08|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN5.59%2016|559|770774400|865382400|1149465600|19|867837282|0|0|865036800|833846400|833587200|833587200|0|1|1|0|0|0|0|0|0|1|0|0|INT 0.48/31|867866253|N|10000
11570|0564A19|GC|4|5.00000000000000E+08|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN5.64%2019|564|757468800|867801600|1230854400|24|867837282|0|0|867456000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 0.05/3|867866253|N|10000
11589|0569I18|GC|4|1.11300000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOI LOAN 5.69% 2018|569|748915200|859248000|1222300800|10|867837282|0|0|858988800|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 1.58/100|867866253|N|10000
10839|0575E03|GC|4|3.78430000000000E+07|0|- -|3|0|3|0|3|0|3|0|10|1|GOI LOAN 5.75% 2003|575|137635200|721526400|737164800|264|737200537|0|0|736646400|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 2.32/145|744056123|N|10000
11572|0587A10|GC|4|5.00000000000000E+08|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN5.87%2010|587|725932800|867801600|946857600|20|867837282|0|0|867456000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 0.05/3|867866252|N|10000
11574|0587H22|GC|4|1.10000000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN5.87%2022|587|746496000|857088000|1346112000|11|867837282|0|0|856656000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 2.07/127|867866253|N|10000
11590|0601C28|GC|4|1.50000000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOI LOAN 6.01% 2028|601|744768000|859248000|1522022400|8|867837282|0|0|858988800|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 1.67/100|867866253|N|10000
11568|0605F19|GC|4|1.10000000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN6.05%2019|605|739843200|866073600|1244764800|10|867837282|0|0|865641600|834537600|834192000|834192000|0|1|1|0|0|0|0|0|0|1|0|0|INT 0.39/23|867866254|N|10000
11566|0613F28|GC|4|1.10000000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN6.13%2028|613|739152000|865382400|1528156800|11|867837282|0|0|865036800|833846400|833587200|833587200|0|1|1|0|0|0|0|0|0|1|0|0|INT 0.53/31|867866254|N|10000
11560|0617F23|GC|4|1.40000000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOI LOAN 6.17% 2023|617|739843200|866073600|1370995200|8|867837282|0|0|865641600|834537600|834192000|834192000|0|1|1|0|0|0|0|0|0|1|0|0|INT 0.39/23|867866254|N|10000
11575|0618I05|GC|4|2.30000000000000E+09|0|- -|3|0|3|0|3|0|3|0|10|1|GOILOAN6.18%2005|618|715478400|810172800|810172800|4|809862908|0|0|809913600|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS|810067573|N|10000
11561|0625A18|GC|4|1.68868000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOI LOAN 6.25% 2018|625|725932800|867801600|1199318400|7|867837282|0|0|867456000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 0.05/3|867866253|N|10000
11562|0630D23|GC|4|7.00000000000000E+08|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN6.30%2023|630|734313600|860544000|1365465600|17|867837282|0|0|860112000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 1.51/86|867866253|N|10000
11571|0635A20|GC|4|1.10000000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN6.35%2020|635|725932800|867801600|1262390400|10|867837282|0|0|867456000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 0.05/3|867866253|N|10000
10854|0650F04|GC|4|4.11960000000000E+07|0|- -|3|0|3|0|3|0|3|0|10|1|GOI LOAN 6.50% 2004|650|109382400|772156800|772156800|242|771846922|0|0|771897600|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS|771875761|N|10000
10824|0650G03|GC|4|1.50000000000000E+07|0|- -|3|0|3|0|3|0|3|0|10|1|GOI LOAN 6.50% 2003|650|144115200|743644800|743644800|666|742989337|0|0|743212800|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 1.28/71|744056128|N|10000
10825|0650J05|GC|4|4.64970000000000E+07|0|- -|3|0|3|0|3|0|3|0|10|1|GOI LOAN 6.50% 2005|650|118281600|812592000|812592000|215|812549104|0|0|796521600|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS|812575534|N|10000
10855|0665D09|GC|4|5.88680000000000E+08|0|- -|1|0|2|0|2|1|2|0|10|1|GOI LOAN 6.65% 2009|665|702432000|860198400|923356800|16|867837282|0|0|859680000|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 1.66/90|867866286|N|10000
11573|0672B14|GC|4|1.12736000000000E+09|0|- -|1|0|2|0|2|1|2|0|10|1|GOILOAN6.72%2014|672|730512000|856742400|1077667200|9|867837282|0|0|856483200|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|ACCR INT/DAYS 2.45/131|867866253|N|10000
Let me know if you require any more details.
Regards,
Mahesh
-
First problem is the header record...
NEATCM|8.07.00|867850731
...which does not have the same number of delimiters as the other rows. Will always throw an error or cause a skip of the first detail record. Wouldn't be surprised if there were a noncompliant footer record, as well.
Also, delimiter may be wrong for column 46... should probably be \r\n but not sure because the HTML corrupts the copy and paste.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
1. If you look at my Bulk Insert statement I am ignoring header record by giving "FIRSTROW = 2"
2. When I generate file format using BCP command it is generating as I have given, even I tried by keeping delimiter in 46 column.
Regards,
Mahesh
-
1. Doesn't matter if the header doesn't have the same number of delimiters. BCP is picky that way.
2. That's because of what I said before... you must delimit the end of row marker in the last column of the format file, for starters. You may still get the error if the file has a footer with less than the correct number of delimiters or the file has an extra cr/lf at the end of the file.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
File doesn't have any footer.
Will you be able to give me the right query for this problem?
To create file format
Exec master..xp_cmdShell 'bcp US..TEST1 format -c -f "D:\nnf.txt" -S SERVERNAME -U SA -N -T -t "|"'
To Import the data into table in specified format
DECLARE @SQLString nvarchar(2000)
SET @SQLString = 'BULK INSERT US..test1
FROM ''D:\test.dat''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n'',
FORMATFILE = ''D:\nnf.txt'',
FIRSTROW = 2
)'
PRINT @SQLString
EXEC SP_EXECUTESQL @SQLString
Regards,
Mahesh
-
Can anyone help me in this?
Mahesh
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply