Post reply

txt import

  • April 20, 2012 at 5:52 pm

    #392080

    After playing in Server management for a hour or so and Google for a while more I decided maybe someone on here may know the answer. so here it goes.

    I have a LOT of .txt files that i need to bring into a database. right now i have 5 access databases doing it. it isn't hard it is just bringing them in then appending them. that being said there is a difficulty. these files have VERY specific character counts for different data. for example 1-13 is last name, 14-20 is first name...and such. I have access pulling these tables in for each month of the year for all 5 databases. it is time consuming and yet the only way i know how to pull in the data, separate it into its distinct pieces.

    so is it possible to bring it into SQL and separate it into its distinct pieces?

  • April 20, 2012 at 6:14 pm

    #1476980

    This is a job for SSIS.

  • April 20, 2012 at 8:48 pm

    #1476993

    andersg98 (4/20/2012)

    This is a job for SSIS.

    +1 however you could also bulk insert into a staging table and use substring functions to break your data into the correct columns and insert into your table (or another staging table).

    and the answer is always it depends with something this vague.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • April 24, 2012 at 4:21 am

    #1478007

    andersg98 (4/20/2012)

    This is a job for SSIS.

    +1 seems like pure ETL. DTS Wizard would do this for you.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 24, 2012 at 9:25 am

    #1478287

    ok i will look into using SSIS for this. Thank you for your help. It looks like i will be learning something new today.

  • April 24, 2012 at 10:26 pm

    #1478679

    You're welcome. 😀

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 26, 2012 at 10:56 am

    #1479919

    ok I was able to import one table!!!! awesome!:-D it shows up in the manager studio db and everything!

    Now the only thing is how do I import more like it without having to go through each file and set the parameters and things again? I have looked for ways to set it to a different flat file but each time i try and set up a "new connection" it says columns haven't been defined for the source. I cant seem to figure out how to attach screen prints though. 🙁

    Thank you for your help again...sorry I don't know more unfortunately.

    This is an amazing tool! I am hoping to get to know it better...in time though. Working full time with school full time means I will be slow going. 🙁

  • April 26, 2012 at 10:08 pm

    #1480170

    What method did you use to import the Data??..."DTS Wizard" or you did it by creating a Job through "BID Studio"?

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 27, 2012 at 3:48 am

    #1480257

    slunt01 (4/26/2012)

    ok I was able to import one table!!!! awesome!:-D it shows up in the manager studio db and everything!

    Now the only thing is how do I import more like it without having to go through each file and set the parameters and things again? I have looked for ways to set it to a different flat file but each time i try and set up a "new connection" it says columns haven't been defined for the source. I cant seem to figure out how to attach screen prints though. 🙁

    Thank you for your help again...sorry I don't know more unfortunately.

    This is an amazing tool! I am hoping to get to know it better...in time though. Working full time with school full time means I will be slow going. 🙁

    Looks like your learning and loving it 🙂

    I'm assuming you are using SSIS? If you are then take a look at the Connection Type mentioned here:

    http://msdn.microsoft.com/en-us/library/ms137830(v=sql.100).aspx

  • April 29, 2012 at 5:17 pm

    #1480999

    andersg98 (4/20/2012)

    This is a job for SSIS.

    Oh, horse muffins! 😛 BULK INSERT with a format file makes this a simple T-SQL task.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 3, 2012 at 4:09 pm

    #1483363

    ok what is a bulk insert?

    See i told you i was new.

    It seems like there is a million ways to do one thing.

  • May 3, 2012 at 4:38 pm

    #1483369

    slunt01 (5/3/2012)

    ok what is a bulk insert?

    See i told you i was new.

    It seems like there is a million ways to do one thing.

    Here is the MSDN article on it http://msdn.microsoft.com/en-us/library/ms188365.aspx Bulk insert allows you to insert from a file and several other sources. you can break your string's down while you insert into a table using substring functions or with the format file like Jeff mentioned.

    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • May 18, 2012 at 9:04 am

    #1489754

    Still working on this. It is hit and miss I think I am getting closer.

    I looked at doing a bulk insert but I couldn't figure out the code to do it. (I have 60 columns in the txt file).

    I have looked at the code for format files and I have tried several ways. I will be trying to do it again today. If all else fails I know I can use Access to get it into the format I need and then import from there. 🙂

    Thank you all for your help.

  • May 18, 2012 at 9:36 am

    #1489777

    You can run BCP without a format file and it will help you create one, guessing at the columns.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 18, 2012 at 9:45 am

    #1489787

    If the file is a delimited file and is in good and reasonable shape, neither BCP or Bulk Insert need a format file.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply