www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: sql read tab delimited text file

  1. #1
    Join Date
    Jun 2006
    Posts
    26

    sql read tab delimited text file

    Hi, I am trying to write sql code that will:

    read in a tab delimited text file.

    Does anyone know how I would write this?

    Thanks tons!

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    umm you don't.

    you can, if you have oracle use PL/SQL to read the file, but that's not something that SQL is designed to do natively.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Jun 2006
    Posts
    26

    sql

    can't i transfer that file into excel and then import excel into a sql table. Then perform an update to a table from this new sql table?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you can do lots of things by hand if you want.

    what i'm saying is that there is no native SQL support for opening a filie and transferring the contents, it's not what it's designed for.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  5. #5
    Join Date
    Jun 2006
    Posts
    26

    resolved sql

    well this is what i have been assigned to do SO I HAVE TO DO IT!!!!
    Thanks for not helping me

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,152
    There is no SQL function for reading a tab-delimited file and loading it into a table. There are many ways to accomplish your goal via the programming language of your choice that is able to talk to your database (or simply by writing the result to a .sql file which you could then process). Without knowing which such programming/scripting languages you have available and would like to work with, it's tough to suggest a specific solution.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Jun 2006
    Posts
    26

    sql

    Thank-you so much for the generosity of your help
    can't i transfer the tab delimited file to an excel file and then export exel doc to a new table in sql?

  8. #8
    Join Date
    Feb 2003
    Posts
    2,745
    Actually, there IS native support depending on the programming language and/or database system.

    If you are using ADO with VB, C++, C# or VB.Net you can set up your tab delimited text file as a data source and write select queries against it. If you are using Java you can make it a JDBC data source. Same with Excel, so no need to import to excel. Excel will easily import it though.

    If you are using MS Access (and possibly mySQL on a win32 platform) you can set it up as an ISAM attachment and execute queries as though it were a native table.

    You can write Pass-Through queries in MS Access to treat it as a table. Or set it up as a linked table.

    MSSQL can easily import it into a table via a wide array of tools and MSSQL DTS can execute queries against it as though it were a native table. Can probably set it up as a linked server in MSSQL too.

    So despite some of the above remarks, there is most certainly native support, but we need more information about the tools and platforms at your disposal to be more helpful.

    To query the text file directly as a data source in Java or any of the MS languages (VB, C++, DotNet etc.) create your connection string like that Shown Here at the bottom of the page.

  9. #9
    Join Date
    Feb 2003
    Posts
    2,745
    There is no SQL function for reading a tab-delimited file and loading it into a table
    There is in MSSQL Server, Oracle and DB2.

  10. #10
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by sql123
    well this is what i have been assigned to do SO I HAVE TO DO IT!!!!
    Thanks for not helping me
    no need to be rude

    Quote Originally Posted by russell
    There is in MSSQL Server, Oracle and DB2.
    everything you've posted is outside the scople of actual SQL though.

    of course there's lots of ways to do it - access has a manual way of importing tab delimited, you can use mysql's LOAD DATA INFILE to import it, you can use some nifty PL/SQL (as I pointed out) to import it into oracle and there are certainly similar ways in db2, mssql server and as you pointed out we have no idea what DBMS they're using so we can't tell them which to use.

    what i have been pointing out is that there is no native SQL support for this, since the topic of the conversation says "sql read tab delimited text file"
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  11. #11
    Join Date
    Feb 2003
    Posts
    2,745
    Chazzy, I didn't mean to be rude, though I guess i came off that way, so I apologize. Truth is, we need more info to give sql123 a solid answer. My real point was that you actually can execute queries directly against a text file as a data source, but it all depends on the tools he (she?) is using.

  12. #12
    Join Date
    Jun 2006
    Posts
    26

    sql

    Thank-you everyone for your help with everything. I think I need to pass on the ms access vb.net info you all mentioned due to my lack of knowledge in these tools.

    What I think I might do, is once I have the file in excel, then do the SQL Transformation services to import it into a dummy table. The only thing is I don't know how to do the Transformation Services either. I heard it may not be that complicated and I may have to write some code to import the info. Is this correct?

  13. #13
    Join Date
    Jun 2006
    Posts
    26

    sql

    I just found out i no longer need to worry about getting the info into sql. I just need to write the code to update the table. thanks for the help

  14. #14
    Join Date
    Feb 2003
    Posts
    2,745
    If you are using SQL Server, don't put it in Excel. Tab delimited text file works better, is easier.

    In EM (Enterprise Manager):
    • expand data transformation services
    • Right-click Local PAckages
    • Click New PAckage
    • Drag the MSSQL OLE DB Connection into the work area (looks like a server icon)
    • set the connection properties to the sql server in the dialog
    • drag the text file (source) icon onto the work area
    • key in, or browse to the path of the text file
    • click properties
    • Check the "First row has column names" box, if appropriate
    • create a data transformation task by clicking the icon -- looks like a yellow gear with an arrow in it
    • click the source icon, then the destination icon
    • in the destination tab of the transform data task, you can either choose the table or let mssql create a new one
    • click the transformations tab, mssql will auto-populate this for you if the table schema matches the text file
    • click ok
    • right click a blank spot in the dts and click execute
    Also look into BCP which is the fastest possible way to import text files. It is a command line program, very easy to use.

  15. #15
    Join Date
    Jun 2006
    Posts
    26

    sql

    im just trying to create a new table in sql using the below code but when i execute i get an error, "incorrect syntax near loadtable" do you know why?


    CREATE TABLE loadtable
    (resource_code char(10),
    move_reason_cd smallint(2))
    table created.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles