Import into SQL Server with format file and bulk insert

Question

Hello everyone I have a difficult problem with using BULK INSERT command when I try to import data from text file.

I found many articles and examples in Internet about importing with BULK INSERT or BCP program, but they not help to me.

The problem:
I make export from Oracle into text file with column delimiter {#} and row delimiter <#> and import it into SQL Server.

The table is (SQL Server):

CREATE TABLE my_DATA
(
ID_PK     NUMERIC(30)  NOT NULL ,
BEGIN_TIME    DATETIME  NULL ,
END_TIME      DATETIME  NULL
);

for Oracle:

CREATE TABLE my_DATA
(
ID_PK     NUMBER(30)  NOT NULL ,
BEGIN_TIME    TIMESTAMP  NULL ,
END_TIME      TIMESTAMP  NULL
);

The file with delimiter is:

ID_PK{#}BEGIN_TIME{#}END_TIME<#>296167{#}01/01/2012 01:30:00.000{#}01/01/2012 02:00:00.000<#>296178{#}01/01/2012 02:00:00.000{#}01/01/2012 02:30:00.000<#>

The format file is:

9.0                         
3                           
1   SQLNUMERIC  0   19  {#} 1   ID_PK   ""
2   SQLDATETIME 0   8   {#} 2   BEGIN_TIME  ""
3   SQLDATETIME 0   8   <#> 3   END_TIME    ""

So when I used command:

BULK INSERT my_DATA 
FROM 'D:\my_DATA.txt' 
WITH 
(CODEPAGE = '1251',
 FIELDTERMINATOR = '{#}', 
 FIRSTROW = 2, 
 ROWTERMINATOR = '<#>' );

It works, but when I try to use format file it does not work:

BULK INSERT my_DATA 
FROM 'D:\my_DATA.txt' 
WITH (CODEPAGE = '1251',
      FORMATFILE ='D:\format_file.txt');

The error is:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (begin_time).

I searched about this problem, try to change datetime to smalldate, try to change length to 23 or 24. It does not work. So I try to import another table without date, I use numeric column and char columns, but I faced with the same problem with numeric column:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (id_pk).

table:

CREATE TABLE unit_table
(
id_pk  NUMERIC(30)  NOT NULL ,
name             NVARCHAR(200)  NULL ,
name_full        NVARCHAR(200)  NULL ,
CONSTRAINT  PK_unit_table_2C1 PRIMARY KEY (bule_biz_unit_level_id_pk)
);

format file:

9.0
3
1       SQLNUMERIC    0       1      "{#}"      1     id_pk                              ""
2       SQLNCHAR      0       11     "{#}"      2     name                                              ""
3       SQLNCHAR      0       11     "{#}"      3     name_full                                         ""

The data file contains only 3 rows (the bulk insert command is the same):

ID_PK{#}NAME{#}NAME_FULL<#>1{#}factory{#}factory<#>2{#}station{#}station<#>

It is interesting if file contains only 2 rows:

ID_PK{#}NAME{#}NAME_FULL<#>1{#}factory{#}factory<#>

The result of bulk insert is:

(0 row(s) affected)

I also try to do example http://msdn.microsoft.com/en-us/library/ms178129.aspx but face with error:

Cannot bulk load because the file "D:\myTest.txt" could not be read. Operating system error code (null).

And the last, I tried to use this with bcp program and face with errors too.

Can anyone help me with my problem or give me at least one working example with bulk insert and format file.

P.S. I use MS SQL Server 2005 updated to last version. OS is Windows 7 x64.

Answer 1

Thanks to all. But the problem was that MS SQL Server requires SQLCHAR type, when you importing from text file and it does not matter what column type in the database. In my problem the solution is replacing any column types, like SQLNUMERIC, SQLDATETIME to SQLCHAR in format file.