Syntax for reading in a txt file

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Kimmnster
    New Member
    • Nov 2006
    • 1

    #1

    Syntax for reading in a txt file

    Hi-

    Can anyone give me the correct syntax to insert a txt file into sql?

    Create table [#something] ([some] varchar (10) null)
    insert into #something from 'c:/txtfile.txt'
    go

    the txt has only 1 field as I have defined above
    I get incorrect syntax error near keyword from
    Thanks
    Kim
  • iburyak
    Recognized Expert Top Contributor
    • Nov 2006
    • 1016

    #2
    Check this out:

    http://dotnet.org.za/thea/archive/2005/02/15/14488.aspx

    Comment

    • rharvieuxsql
      New Member
      • Nov 2006
      • 9

      #3
      Try this:

      CREATE TABLE SomeTable (Results varchar(1000) null)
      INSERT INTO SomeTable(Resul ts)
      exec ('master..xp_cm dshell ''type d:\yourfile.txt ''')

      Comment

      • iburyak
        Recognized Expert Top Contributor
        • Nov 2006
        • 1016

        #4
        Grate solution and works like a charm... :)

        Comment

        • aaryan
          New Member
          • Nov 2006
          • 82

          #5
          hi,
          but it's not working for me.
          when i made the code run in my sql query analyzer, it is taking indefinite time to get processed and eventually i am forced to quit.

          Comment

          • rharvieuxsql
            New Member
            • Nov 2006
            • 9

            #6
            Originally posted by aaryan
            hi,
            but it's not working for me.
            when i made the code run in my sql query analyzer, it is taking indefinite time to get processed and eventually i am forced to quit.
            Try using BULK INSERT:

            CREATE TABLE SomeTable (Results varchar(1000) null)
            BULK INSERT YourDatabase.db o.[SomeTable]
            FROM 'd:\yourfile.tx t'

            Comment

            • iburyak
              Recognized Expert Top Contributor
              • Nov 2006
              • 1016

              #7
              Is it possible that query can't find your file?
              You should understand that query executed on the server and not on your computer.

              Comment

              • aaryan
                New Member
                • Nov 2006
                • 82

                #8
                thank u rharvieuxsql

                Comment

                • fmsupport
                  New Member
                  • Dec 2007
                  • 1

                  #9
                  I realize this thread is about a year old, but I have a question. Is there a way to use the BULK INSERT and remove/ignore the first line of text? The first line of text has the field names.

                  Example text file:

                  "Item" "Quantity" "Price"
                  "item1" 275 20
                  "item2" -22 15.75
                  "item3" -17 22.5
                  ...

                  Running the query manually errors on the first line. The other lines insert, but I would like to avoid the first line error because this will eventually be automated.

                  Comment

                  Working...