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.
| Author |
Topic |
|
sqlteamForummer
Starting Member
13 Posts |
 Posted - 2005-11-29 : 14:21:47
|
| I have a data.CSV file with 9 columns of data. For example data in the file looks like below:/-------------------------------------fld1 fld2 fld3 fld4 fld5 fld6 fld7 fld8 fld9 "Syracuse","2005/11/22","07:00",12, 23, 78, 120, 222, 231-------------------------------------/I created a table:/-------------------------------------CREATE TABLE dbo.table1(fld1 VARCHAR(40) NULL,fld2 DATETIME NULL,fld3 DATETIME NULL,fld4 INT NULL,fld5 INT NULL,fld6 INT NULL,fld7 INT NULL,fld8 INT NULL,fld9 INT NULL)-------------------------------------/My BULK INSERT is:/-------------------------------------BULK INSERT dbo.table1 FROM 'C:\TaskData\data.CSV' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )-------------------------------------/My Questions are:1)Is it okay if I declare 07:00 as DATETIME.2)When I execute the bulk insert, I get the following error:Msg 4864, Level 16, State 1, Line 1Bulk insert data conversion error (type mismatch) for row 1, column 2 (fld2)"2005/11/22" is fld2. If I take out the quotes manually then it works.If I do not take out the quotes but use it as a VARCHAR then the table stores it with the quotes.Is there a way to eliminate the quotes and store it as DATETIME.3) Not all the flds have quotes -- that is why I am having problems toeliminate the quotes in fld1, fld2 and fld3. Any help would be appreciable. |
|
|
|
|
|
|
|
BULK INSERT fieldterminator