?
Solved

BULK INSERT ERROR

Posted on 2013-05-29
10
Medium Priority
?
399 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

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!

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 Backup & Restore 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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

800 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