Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8950
  • Last Modified:

Problems with BULK INSERT format file!

SQL2005 database, completely empty table

I'm trying to import data from a flat file into a table.  Currently the flat file has FEWER datafields than the table has columns.  (This is an older set of data.  Newer data will use all the columns.)

Also, the table has an IDENTITY column.

Here is my table:

CREATE TABLE [dbo].[line](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [medialabel] [char](50) COLLATE Latin1_General_CI_AS NOT NULL,
      [logfile] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
      [filename] [char](256) COLLATE Latin1_General_CI_AS NOT NULL,
      [fileextension] [char](50) COLLATE Latin1_General_CI_AS NULL,
      [server] [char](50) COLLATE Latin1_General_CI_AS NOT NULL,
      [drive] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
      [folder] [char](256) COLLATE Latin1_General_CI_AS NOT NULL,
      [size] [int] NULL,
      [datetimestr] [char](25) COLLATE Latin1_General_CI_AS NULL,
      [datetime] [datetime] NULL,
 CONSTRAINT [PK_line] PRIMARY KEY CLUSTERED

Here is a line from my datafile:

'Local20050720', 'BEX377.txt', 'readme', 'txt', 'DEGAS', 'D', 'DataDump3\Empty2042_2043'

Table:  11 columns, first one is IDENTITY.

File row:  7 fields, all in order, the first field corresponding to the Table's MEDIALABEL

The autogenerated format file looks like this:

9.0
11
1       SQLCHAR       0       12      ","     1     id                             ""
2       SQLCHAR       0       50      ","     2     medialabel                     Latin1_General_CI_AS
3       SQLCHAR       0       10      ","     3     logfile                        Latin1_General_CI_AS
4       SQLCHAR       0       256     ","     4     filename                       Latin1_General_CI_AS
5       SQLCHAR       0       50      ","     5     fileextension                  Latin1_General_CI_AS
6       SQLCHAR       0       50      ","     6     server                         Latin1_General_CI_AS
7       SQLCHAR       0       10      ","     7     drive                          Latin1_General_CI_AS
8       SQLCHAR       0       256     ","     8     folder                         Latin1_General_CI_AS
9       SQLCHAR       0       12      ","     9     size                           ""
10      SQLCHAR       0       25      ","     10    datetimestr                    Latin1_General_CI_AS
11      SQLCHAR       0       24      "\r\n"   11    datetime  

(I changed \t to , for field terminator)

If I use that format file I get these errors:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (id).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".

I've tried playing around with the format file but I keep getting errors.

Can anyone help me with this format file so I can import this data?

Thanks.



0
gateguard
Asked:
gateguard
  • 4
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
9.0
11
1       SQLCHAR       0       4        ""        1     id                                                SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50       ""        2     medialabel                                        SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       10       ""        3     logfile                                           SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       256      ""        4     filename                                          SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50       ""        5     fileextension                                     SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50       ""        6     server                                            SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       10       ""        7     drive                                             SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       256      ""        8     folder                                            SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       4        ""        9     size                                              SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR       0       25       ""        10    datetimestr                                       SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR       0       8        "\r\n"    11    datetime                                          SQL_Latin1_General_CP1_CI_AS

0
 
gateguardAuthor Commented:
Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "E:\sqlinputfiles\ee.fmt" could not be read. Operating system error code (null).

It's a mis-named error.  There is no problem reading the file.  I can open it from windows explorer and if I open a command prompt and type:   type E:\sqlinputfiles\ee.fmt         then I get this output:

C:\>type E:\sqlinputfiles\ee.fmt
9.0
11
1       SQLCHAR       0       4        ""        1     id
                         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50       ""        2     medialabel
                         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       10       ""        3     logfile
                         SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       256      ""        4     filename
                         SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50       ""        5     fileextension
                         SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50       ""        6     server
                         SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       10       ""        7     drive
                         SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       256      ""        8     folder
                         SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       4        ""        9     size
                         SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR       0       25       ""        10    datetimestr
                         SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR       0       8        "\r\n"    11    datetime
                         SQL_Latin1_General_CP1_CI_AS


It just doesn't like your file.  You have no field terminators.  Is that right?

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
oops you are correct , i forgot to put the limitator

9.0
11
1       SQLCHAR       0       4        ","        1     id                                                SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50       ","        2     medialabel                                        SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       10       ","        3     logfile                                           SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       256      ","        4     filename                                          SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50       ","        5     fileextension                                     SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50       ","        6     server                                            SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       10       ","        7     drive                                             SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       256      ","        8     folder                                            SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       4        ","        9     size                                              SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR       0       25       ","        10    datetimestr                                       SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR       0       8        "\r\n"    11    datetime                                          SQL_Latin1_General_CP1_CI_AS
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gateguardAuthor Commented:
Now we're back to the original errors I was getting:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (id).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".

I think there are two problems:

1) the IDENTITY column in the table
2) the lack of three data fields in the data file  

I've tried every way I can think of to change the format file but I always get errors.

It's extremely frustrating.

How do you deal with IDENTITY columns?
How do you import fewer datafields into a table with more columns?

Those are the two questions.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have some problems:

'Local20050720', 'BEX377.txt', 'readme', 'txt', 'DEGAS', 'D', 'DataDump3\Empty2042_2043'

means that the delimitor is not "," , but "','" actually.
also, the leading and trailing ' will be handled explicitely.

now: do you really need the input file with the single quotes? can't you get the file without those quotes?

>1       SQLCHAR       0       4        ","        1     id                                                SQL_Latin1_General_CP1_CI_AS
is wrong, because you do not import the id field. the ID value is generated automatically

let me show you a useful article from MS in regards to the BULK INSERT format files:
http://msdn2.microsoft.com/en-us/library/ms190396.aspx
for example, skip a column:
http://msdn2.microsoft.com/en-us/library/ms179250.aspx


finally:
I noticed that you posted 2 "follow-up" questions, which you should NOT do. keep 1 question for 1 issue.
0
 
gateguardAuthor Commented:
I see the error with the delimiter.  Thanks for that.  I changed the file, but it still doesn't help.  To make things easier, I have simplified everything.

Here is the new table, with no identity column:

CREATE TABLE [dbo].[lineshort](
      [medialabel] [char](50) COLLATE Latin1_General_CI_AS NOT NULL,
      [logfile] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
      [filename] [char](256) COLLATE Latin1_General_CI_AS NOT NULL,
      [fileextension] [char](50) COLLATE Latin1_General_CI_AS NULL,
      [server] [char](50) COLLATE Latin1_General_CI_AS NOT NULL,
      [drive] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
      [folder] [char](256) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

Here is the data file, host.txt  (it only has one row, and here's the row):

Local20050720, BEX377.txt, readme, txt, DEGAS, D, DataDump3\Empty2042_2043

Here is the BULK INSERT command:

BULK INSERT lineshort FROM 'E:\sqlinputfiles\host.txt'

Here are the errors:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".

Here is the BULK INSERT command with a format file:

BULK INSERT lineshort FROM 'E:\sqlinputfiles\host.txt' with (formatfile = 'E:\sqlinputfiles\eeshort.fmt')

Here is the format file (eeshort.fmt):

9.0
7
1       SQLCHAR       0       50       ","        1     medialabel                                        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       10       ","        2     logfile                                           SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       256      ","        3     filename                                          SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       50       ","        4     fileextension                                     SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50       ","        5     server                                            SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       10       ","        6     drive                                             SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       256      "\r\n"        7     folder

Here are the errors for that run:

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 2 (logfile).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".

At this point, I don't even care about my data anymore.  I would like to just see any BULK INSERT that works.

Could somebody please post a simple CREATE TABLE command, and a single-row of flat-file data, and a BULK INSERT command that puts that row of data into that table?

I'd love to have just one success right now, even if it's somebody else's.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
9.0
7
1       SQLCHAR       0       0       ","        1     medialabel                                        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       0       ","        2     logfile                                           SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       0      ","        3     filename                                          SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       0       ","        4     fileextension                                     SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       0       ","        5     server                                            SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       0       ","        6     drive                                             SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       0      "\r\n"        7     folder                                     SQL_Latin1_General_CP1_CI_AS

and make all your columns in the table varchar instead of char...
0
 
gateguardAuthor Commented:
Once I changed the delimiter from comma to tab, all my problems vanished.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now