Solved

BULK INSERT ERROR

Posted on 2013-05-29
10
397 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

696 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