Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: How to create table on text file import?

    Hey guys,
    I have a dilemma and hope someone can help.

    I don't know of any utilities or commands in SQL that do this but I hope someone does.

    What I need to do is something like a bcp import a text file in. I can do that with DTS as well. But what I wanted to do is create a table on the import. So lets say, I am importing a tab-delimited file with column names as the first row that is called ax.txt. On import, it would create the table ax with the column names in the file and then import the data into that table.

    I hope I explained it clearly. Please let me know if there is anything I can use to do this without writing lots of code.

    I have an idea how to do it the long way but hope there is a utility that already does it.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I would do this a bit differently than you have described.

    I would create the table first, then use BULK INSERT to populate the table specifying FIRSTROW = 2 to avoid importing the column names.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61

    Use DTS

    You mention DTS; why don't you want to use that? You could accomplish what you want with two tasks - an execute sql task to create the table and a copy data task to import the .txt file.

    Lempster

  4. #4
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Hi guys,
    Thanks for your input. I am trying to avoid creating table on my own because that means I need to parse the file first to pull out the column names. The problem is, everytime I get the file it may change. It could have more or less columns. Columns could be in different order and different lengths.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Write a specification for the file

    "Here's a completely different file than yesterday - make a program that can handle my constant changes"
    George
    Home | Blog

  6. #6
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Believe me, if it was my file, it would be consistent. This file comes from our client and they refuse to make it consistent. They said we should be able to accept files dynamically (whatever that means). So the only way I see it, is to create a new table every import get the data out and then delete the table.
    So I assume there is nothing that creates the table on import. Correct?

  7. #7
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    I suppose you could write some code (e.g. VB) to parse the file and extract each field into a variable that you could then pass to a stored proc which would create your table and use each variable as a column name......but I think your client is being unreasonable. If they don't know what the format/contents of a file is going to be, how can they make use of it?

    Off topic: georgev; I've got to ask, what does your <3 Engaged! tag line mean?

    Lempster

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the length of the file?

    In any case Create a staging table with 1 column

    You will somehow have to know the offsets if it's not delimited

    bcp the whole file into that one column

    then create a view with the offsets

    then load the destination dataset

    BUT, I would tell them if the can't tell me what the file is, I can't accept it

    Because you need to get the offsets or definition from them in the first place

    Do you have a hard time with dates?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thats exactly what I have been thinking. Thanks guys. Looks like I just have to write some code. Don't feel like it today (Friday).
    Thanks for the input guys.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Lempster
    Off topic: georgev; I've got to ask, what does your <3 Engaged! tag line mean?
    I am recently engaged to the love my life. We will have been engaged for 6 months on Sunday
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    who is not in love at your age? good luck.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •