• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

Sql Query for reading a comma delimited textfile

I want Sql query which can read a comma-delimited text file and create a table for it. I know that it can be done using loop by reading line by line. But I want a query which can directly do the task. I had found that query which can directly read a delimited textfile and create a table for it. But it has been misplaced. That was a direct sql query which also created a table alongwith reading the file. I want that query.
0
ShreyaTrivedi
Asked:
ShreyaTrivedi
  • 2
1 Solution
 
rachitkohliCommented:
assume i have a text file with following tab-delimited data
Name      Age      location
rachit      26      mumbai
nitin      21      chandigarh
rakhi      23      delhi
lokesh      31      calcutta

to insert into the table the code will be:

Create a table like this :
create table txtTable (name varchar(100), age varchar(5), location varchar(100))

To insert it will be :
bulk insert txtTable from 'c:\test\test.txt'

Query it now
select * from txtTable
0
 
ShreyaTrivediAuthor Commented:
How can I assign delimiter to the query? What if I have comma delimited file?
0
 
OetzyCommented:
Hi,
try this:

INSERT INTO TableName
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\TEST\;',
   'SELECT * FROM test1.csv')
0
 
ShreyaTrivediAuthor Commented:
The following query works-
bulk insert txtTable from 'c:\test\test.txt' with (FieldTerminator=',')
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now