|
srosenberg
Starting Member
3 Posts |
 Posted - 2009-04-17 : 10:25:04
|
| I am trying to import a flat ascii file with 48 fields. I have not been able to get SQL to recognize the end of the row. The row terminator is a line feed. If I don't put in the ROWTERMINATOR qualifier (or if I put in ROWTERMINATOR = '\n') it gives me a data conversion error(type mismatch) for the last field of every row. The field is a money field and in the first ten records the value is 0.00I tried regenerating my data with an asterisk at the end each row and put in ROWTERMINATOR = '*\n' This gave me an error saying that the last field in the line was too long. I also tried with ROWTERMINATOR = '*' and got the same result. I have carefully checked that the number of data fields in the input corresponds to the file description. My code is:DROP TABLE PAYHIS3CREATE TABLE PAYHIS3 ( EMPNO VARCHAR(5) NOT NULL, CKDATE DATETIME NOT NULL, CHECKNO VARCHAR(6) NOT NULL, DFWITH MONEY, DSWITH MONEY, DMEDI MONEY, DFICA MONEY, D401P MONEY, DPDMEDSUP MONEY, DPTHLTH MONEY, DCARE MONEY, DCOLONIAL MONEY, DGYM MONEY, DPWDUEES MONEY, DCLERDUES MONEY, DPOLDUES MONEY, DSUNSHINE MONEY, DI457 MONEY, DTM401A MONEY, DAETNA MONEY, DUNWAY MONEY, DTOY MONEY, DPWNEEDS MONEY, DPBUY MONEY, DROTH MONEY, DATTACH MONEY, DMBUY MONEY, DDIRDEP MONEY, DPD1PCT MONEY, DVROTH MONEY, DPHTSING MONEY, DPHTWND MONEY, DPHTWNF MONEY, DPHPWS MONEY, DPHPWD MONEY, DPHPWF MONEY, DPDRET MONEY, DTWNRET MONEY, DLOAN MONEY, DTWN5PCT MONEY, DCRUN MONEY, DTWNSUPP MONEY, DADVANCE MONEY, DGRPLIF MONEY, DNONCASH MONEY, DMAWITH MONEY, DELCPCT MONEY, DPOLBUY MONEY)BULK INSERT PAYHIS3 FROM '\\ROSENSAN\FRONTEND\PAYHIS3.TXT' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n', FIRSTROW = 1) |
|
BULK INSERT problem