4 Replies - 7749 Views - Last Post: 11 February 2007 - 10:12 AM Rate Topic: -----

#1 realNoName  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 04-December 06

Insert from text file

Posted 09 February 2007 - 01:29 PM

I am taking a DB management class but i would really like to play with it at home so am trying to copy the db's to my computer so i did a simple select * from collect and saved the results as a csv so i have all the records

at home i made the db
create table collection
(
	BOOK_ID CHAR(5) NOT NULL,
	SCAN_CODE CHAR(6) NOT NULL,
	LIBRARY CHAR(8) NOT NULL,
	BOOK_STATUS CHAR(1) NOT NULL,
	DATE_LAST_OUT DATETIME NOT NULL,
	TIMES_CHECKED_OUT NUMERIC(4) NOT NULL,
	CHECK_OUT_INTERVAL CHAR(1) NOT NULL,
	COST NUMERIC(7,2) NULL,
	YEAR_ACQUIRED CHAR(4) NULL,
	LAST_DATE_UPDATED DATETIME NOT NULL
)

then tried to insert the data but then i get errors and have no idea what i need to do
BULK INSERT collection FROM 'C:\DB\collection.csv' WITH (FIELDTERMINATOR = ',')

Errors

Quote

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (BOOK_ID).
Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 10. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"


Sample of data

Quote

00220,A63982,JCLS,R,1999-07-20 00:00:00.000,5,C,12.59,1981,1990-09-04 00:00:00.000
00807,A64129,JCLS,I,1999-08-02 00:00:00.000,6,C,NULL,1973,1990-10-22 00:00:00.000
00206,A65257,JCLS,I,1999-07-12 00:00:00.000,1,C,NULL,1980,1991-01-30 00:00:00.000
00931,A65258,JCLS,I,1999-12-22 00:00:00.000,9,B,8.95,1975,1990-03-07 00:00:00.000
00669,A65382,JCLS,O,1999-12-19 00:00:00.000,3,C,7.95,1971,1990-09-01 00:00:00.000


Thanks

This post has been edited by realNoName: 09 February 2007 - 01:30 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Insert from text file

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Insert from text file

Posted 10 February 2007 - 11:50 AM

What database software are you using? As the file was exported into a CSV format, each line is likely being viewed as a single entry by the DB - far too long for the fields specified.

Does your DB software have a GUI?
Was This Post Helpful? 0
  • +
  • -

#3 realNoName  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 04-December 06

Re: Insert from text file

Posted 10 February 2007 - 12:01 PM

Saved from msSQL 05 and trying to import into msSQL 05

This post has been edited by realNoName: 10 February 2007 - 12:03 PM

Was This Post Helpful? 0
  • +
  • -

#4 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Insert from text file

Posted 10 February 2007 - 04:12 PM

The table where the data came from what are its' column attributes?

The fields will have to match exactly. Especially using char() fields.

They may only have 5 characters stored in them but if the actual field length is 6 then you will get a mismatch error.
Was This Post Helpful? 0
  • +
  • -

#5 realNoName  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 343
  • Joined: 04-December 06

Re: Insert from text file

Posted 11 February 2007 - 10:12 AM

I got it to work... i did not have to change anything i just added only around 100 records at a time (there was 1769 total) and for some reason that worked
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1