Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

BULK INSERT ERROR

Posted on 2013-05-29
10
Medium Priority
?
407 Views
Last Modified: 2013-06-04
Hi All,

I have below code. I want to log row of data cause error.

INSERT INTO TMWARRANTYNOADMINIST
(
EntryNo
, WarrantyNo
, CategoryCode
, Flag
, Location
, CrtId
, CrtDate
, UpdId
, UpdDate
)
SELECT
EntryNo
, WarrantyNo
, CategoryCode
, '' AS Flag
, 1 AS Location
, 'ADMINIST' AS CrtId
, '29/05/2013 17:26:02' AS CrtDate
, 'ADMINIST' AS UpdId
, '29/05/2013 17:26:02' AS UpdDate
FROM OPENROWSET(BULK 'D:\EMI NEW\COMPILED\VKOOL\DATA\TIRA.CSV', FIRSTROW = 1, FORMATFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\FORMAT UPLOAD FILE GARANSI.FMT'
 WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\TIRA.TXT'
) ;
) Temp

Incorrect syntax near the keyword 'WITH'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

What is the problem ?

Thank you.
0
Comment
Question by:emi_sastra
  • 6
  • 4
10 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39204512
Hi,

first try with this much.

INSERT INTO TMWARRANTYNOADMINIST
(
EntryNo
, WarrantyNo
, CategoryCode
, Flag
, Location
, CrtId
, CrtDate
, UpdId
, UpdDate
)
SELECT
EntryNo
, WarrantyNo
, CategoryCode
, '' AS Flag
, 1 AS Location
, 'ADMINIST' AS CrtId
, '29/05/2013 17:26:02' AS CrtDate
, 'ADMINIST' AS UpdId
, '29/05/2013 17:26:02' AS UpdDate
FROM OPENROWSET(BULK 'D:\EMI NEW\COMPILED\VKOOL\DATA\TIRA.CSV', FIRSTROW = 1, FORMATFILE = 'D:\EMI NEW\COMPILED\VKOOL\DATA\FORMAT UPLOAD FILE GARANSI.FMT') ;
) Temp

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39205617
Hi  Brichsoft,

I've tried your code before. Since it has problem, I want to check which data cause the problem.

Bulk load: An unexpected end of file was encountered in the data file.

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

What should I do ?

Thank you.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39206866
hi,

it seems your file is not in proper format.
can you share your csv and fmt files.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:emi_sastra
ID: 39206983
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39206985
Please see this and above attachment.

Thank you.
CSV-SAMPLE.csv
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39217651
Hi  Brichsoft,

Any good news  ?

Thank you.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 39218138
Hi,

sorry for late reply.
your csv file is ; delimited.
and you specified comma(,) as your delimiter in format file.

That was causing problem, i guess.

please checkout this format file and try again.


SELECT
EntryNo
, WarrantyNo
, CategoryCode
, '' AS Flag
, 1 AS Location
, 'ADMINIST' AS CrtId
, '29/05/2013 17:26:02' AS CrtDate
, 'ADMINIST' AS UpdId
, '29/05/2013 17:26:02' AS UpdDate
INTO TEMP_TABLE
FROM OPENROWSET(BULK 'C:\CSV-SAMPLE.csv', FIRSTROW = 1, FORMATFILE = 'c:\FORMAT.FMT') AS A ;

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 total points
ID: 39218140
check out format file
format.FMT.txt
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 39218176
Let me try first.

Thank you.
0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 39218262
Hi  Brichsoft,

Yes, the delimiter is not the same with csv.

I wonder why, since it has been used for several months.

Anyway, it works again  now.

Thank you very much for your help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question