Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. FIle Format Problems

FIle Format Problems

Scheduled Pinned Locked Moved Database
csharpasp-netdatabasecom
11 Posts 3 Posters 1 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • V Vimalsoft Pty Ltd

    Good Day All I have a File Format Defined like this

    9.0
    4
    1 SQLCHAR 0 100 "," 0 ExtraField ""
    2 SQLCHAR 0 100 "," 1 Descr SQL_Latin1_General_CP1_CI_AS
    3 SQLCHAR 0 100 "," 2 ABREV SQL_Latin1_General_CP1_CI_AS

    and i use it like this

    BULK INSERT dbo.TBL_CMPS FROM 'C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv'
    WITH (
    FORMATFILE = 'C:\Format.DAT',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n' );

    and my Table Defination is like this

    CREATE TABLE [dbo].[TBL_CMPS](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DESCR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ABREV] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    and my Error is

    Msg 4823, Level 16, State 1, Line 2
    Cannot bulk load. Invalid column number in the format file "C:\Format.DAT".

    Thank you

    Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

    D Offline
    D Offline
    dan sh
    wrote on last edited by
    #2

    I think if you change the column count after 9.0 to 3, it should work.

    It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

    V 1 Reply Last reply
    0
    • D dan sh

      I think if you change the column count after 9.0 to 3, it should work.

      It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

      V Offline
      V Offline
      Vimalsoft Pty Ltd
      wrote on last edited by
      #3

      i get this

      Msg 4862, Level 16, State 1, Line 1
      Cannot bulk load because the file "C:\Format.DAT" could not be read. Operating system error code (null).

      I have give a Full Permission to Everyone, Admin, ASP.NET Thanks

      Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

      D A 2 Replies Last reply
      0
      • V Vimalsoft Pty Ltd

        i get this

        Msg 4862, Level 16, State 1, Line 1
        Cannot bulk load because the file "C:\Format.DAT" could not be read. Operating system error code (null).

        I have give a Full Permission to Everyone, Admin, ASP.NET Thanks

        Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

        D Offline
        D Offline
        dan sh
        wrote on last edited by
        #4

        Try placing return(enter) after SQL_Latin1_General_CP1_CI_AS.

        It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

        V 1 Reply Last reply
        0
        • D dan sh

          Try placing return(enter) after SQL_Latin1_General_CP1_CI_AS.

          It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

          V Offline
          V Offline
          Vimalsoft Pty Ltd
          wrote on last edited by
          #5

          still the Same Error Thanks

          Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

          1 Reply Last reply
          0
          • V Vimalsoft Pty Ltd

            i get this

            Msg 4862, Level 16, State 1, Line 1
            Cannot bulk load because the file "C:\Format.DAT" could not be read. Operating system error code (null).

            I have give a Full Permission to Everyone, Admin, ASP.NET Thanks

            Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

            A Offline
            A Offline
            Ashfield
            wrote on last edited by
            #6

            Its looking for the file in c:\ on your SQL SERVER machine, not your pc. Have you copied the file there?

            Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

            V 1 Reply Last reply
            0
            • A Ashfield

              Its looking for the file in c:\ on your SQL SERVER machine, not your pc. Have you copied the file there?

              Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

              V Offline
              V Offline
              Vimalsoft Pty Ltd
              wrote on last edited by
              #7

              yes. The First Part of my code is to code this file in that Directory,in debug mode, i can see that the file has been created there and it has proper permissions on it.

              Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

              A 1 Reply Last reply
              0
              • V Vimalsoft Pty Ltd

                yes. The First Part of my code is to code this file in that Directory,in debug mode, i can see that the file has been created there and it has proper permissions on it.

                Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                A Offline
                A Offline
                Ashfield
                wrote on last edited by
                #8

                Just a thought,if you are creating the format file in code before calling your proc, are you sure your code has properly closed the file? While in debug can you stop just before running the proc and see if you can open the file with another program such as notepad? May be total rubbish, but its a thought :)

                Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

                V 1 Reply Last reply
                0
                • A Ashfield

                  Just a thought,if you are creating the format file in code before calling your proc, are you sure your code has properly closed the file? While in debug can you stop just before running the proc and see if you can open the file with another program such as notepad? May be total rubbish, but its a thought :)

                  Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

                  V Offline
                  V Offline
                  Vimalsoft Pty Ltd
                  wrote on last edited by
                  #9

                  Yes the File is totally closed. I have thought of another way to do this. well not always a good idea. The Reason why i use a format File is that there is an Identity Field. Now what i did is that i drop the field and imported the data and recreated the Field and it looks OK. Thanks :)

                  Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                  A 1 Reply Last reply
                  0
                  • V Vimalsoft Pty Ltd

                    Yes the File is totally closed. I have thought of another way to do this. well not always a good idea. The Reason why i use a format File is that there is an Identity Field. Now what i did is that i drop the field and imported the data and recreated the Field and it looks OK. Thanks :)

                    Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                    A Offline
                    A Offline
                    Ashfield
                    wrote on last edited by
                    #10

                    There are many ways to skin a cat - pleased you found one that worked for you :-D

                    Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

                    V 1 Reply Last reply
                    0
                    • A Ashfield

                      There are many ways to skin a cat - pleased you found one that worked for you :-D

                      Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

                      V Offline
                      V Offline
                      Vimalsoft Pty Ltd
                      wrote on last edited by
                      #11

                      Thank you :)

                      Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

                      1 Reply Last reply
                      0
                      Reply
                      • Reply as topic
                      Log in to reply
                      • Oldest to Newest
                      • Newest to Oldest
                      • Most Votes

                      • Login
                      • Don't have an account? Register
                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • World
                      • Users
                      • Groups