BCP/BULK INSERT IMPORT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP/BULK INSERT IMPORT

Author

Topic

lkeeba
Starting Member

17 Posts

Posted - 2004-11-26 : 04:46:52

I am using both Oracle and SQL2K. In Oracle there is the LOAD DATA command, using a control file and it works perfectly.

I am trying to import data into SQL from an excel spreadsheet. I tried to use the DTS but this has failed. Then I tried the bcp (with excel, text file from excel) command at the command prompt, which copies some rows (not everything) and wrongly. Then I used the BULK INSERT (with a FORMATFILE) and an error about unxepected end of file reached.

The syntax (with comments) in ORACLE for the same data is:
Load Data
INFILE 'INFILE.csv'
APPEND INTO TABLE MAPITEM
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( MAPITEM_ID SEQUENCE(MAX,1), comment PK, increment by 1
MAP_ID CONSTANT 2760434,
MAPITEM_CODE,
MAPITEM_SHEETCELL,
MAPITEM_ISDELETED CONSTANT 0,
MAPITEM_ISTERMINATOR CONSTANT 0,
MAPITEM_ISVARIABLEITEM CONSTANT 0)

The command used in SQL2K with the
BULK INSERT mapitem
FROM "c:\bsactl\ZW-CAPITAL.xls"
WITH (CHECK_CONSTRAINTS, FORMATFILE = "C:\BSACTL\bcp.fmt")

comment FORMATFILE
8.0
7
1 SQLBIGINT 0 8 "" 1 MapItem_ID ""
2 SQLBIGINT 0 8 "\t" 2 Map_ID ""
3 SQLCHAR 0 50 "\t" 3 MapItem_Code Latin1_General_CI_AS
4 SQLCHAR 0 10 "\t" 4 MapItem_SheetCell Latin1_General_CI_AS
5 SQLTINYINT 1 1 "\t" 5 MapItem_IsDeleted ""
6 SQLTINYINT 1 1 "\t" 6 MapItem_IsTerminator ""
7 SQLTINYINT 1 1 "" 7 MapItem_IsVariableItem ""

comment DATA (tab separated txt file)
95 1. NOTES AND COIN B12 0 0 0
95 2. BALANCES WITH CENTRAL BANK B13 0 0 0
95 3. CLAIMS ON OR GUARANTEED BY THE GOVERNMENT B14 0 0 0
95 4. CLAIMS SECURED BY CASH B15 0 0 0
95 5. CLAIMS ON OECD GOVERNMENTS B16 0 0 0
95 6. CLAIMS ON OR GUARANTEED BY REGIONAL GOVTS B17 0 0 0
95 7. CLAIMS ON BANKS B18 0 0 0
95 8. CLAIMS ON OTHER LOCAL FINANCIAL INSTITUTIONS B19 0 0 0
95 9. CLAIMS ON PARASTATALS B20 0 0 0
95 10. ASSETS IN TRANSIT B21 0 0 0
95 11. CLAIMS ON LOCAL AUTHORITIES WITHIN REGION B22 0 0 0
95 12. CLAIMS ON MULTILATERAL INSTITUTIONS B23 0 0 0
95 13. CLAIMS ON OTHER LOCAL AUTHORITIES B24 0 0 0

Kindly assist.

lkeeba
Starting Member

17 Posts

Posted - 2004-12-09 : 04:08:37

I have finally found a way, by using the osql command with dts. A longer way than the LOAD DATA utility in Oracle, but it works fine, if you know what you want. Thanks for the responses that never came.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-09 : 06:04:02

osql with dts sounds like a very odd way of doing things but if you're happy with it then fine.

Just a tab seperated file should import with bulk insert without a format file.
The format file you have given won't work though.
Have a look at
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lkeeba
Starting Member

17 Posts

Posted - 2004-12-13 : 05:13:03

Thanks for your concern about osql and dtsrun utilities. What I have done is a complete automation of the import process, all done from the command prompt. I import over 60,000 line items from Excel into both Oracle and SQL2K, so this works very fine. I am happy to say that the Load Data utility in Oracle is superb, but my combination of osql and dtsrun utilities have made my job simpler for the SQL2K import.

I would have tried your way, but it is too late, after struggling to make it work and failed. I realise my effort is going to help me at all times!

==============================================

quote:
Originally posted by nr

osql with dts sounds like a very odd way of doing things but if you're happy with it then fine.

Just a tab seperated file should import with bulk insert without a format file.
The format file you have given won't work though.
Have a look at
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-13 : 05:36:47

Well you could probably do the import in sql server with a single statement but....

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources