Solved

BULK INSERT ERROR

Posted on 2013-05-29
10
386 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
Comment Utility
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
Comment Utility
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
Comment Utility
hi,

it seems your file is not in proper format.
can you share your csv and fmt files.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Please see this and above attachment.

Thank you.
CSV-SAMPLE.csv
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi  Brichsoft,

Any good news  ?

Thank you.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
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 500 total points
Comment Utility
check out format file
format.FMT.txt
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Let me try first.

Thank you.
0
 
LVL 1

Author Closing Comment

by:emi_sastra
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now