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?
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.
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?
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.
well this is what i have been assigned to do SO I HAVE TO DO IT!!!!
Thanks for not helping me
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
How to Ask Questions the Smart Way
(not affiliated with this site, but well worth reading)
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?
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.
There is in MSSQL Server, Oracle and DB2.
There is no SQL function for reading a tab-delimited file and loading it into a table
no need to be rude
Originally Posted by sql123
everything you've posted is outside the scople of actual SQL though.
Originally Posted by russell
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"
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.
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?
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
If you are using SQL Server, don't put it in Excel. Tab delimited text file works better, is easier.
In EM (Enterprise Manager):
Also look into BCP which is the fastest possible way to import text files. It is a command line program, very easy to use.
- 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
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
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)