Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing the data from text files...

Status
Not open for further replies.

jrprogr

Programmer
Jul 20, 2006
74
US
Please help me in writing a query to import the comma separated data in a text file to sqlserver 2000.
Below is the sample data from the text file.
100,1,xx,yy,20030101
100,2,21,t4,20030101
100,3,2p,01,20030101
100,1,xx,yy,20030102
100,2,21,t5,20030102
100,3,2p,01,20030102

I want the output:
100,1,xx,yy,20030101,2,21,t4,20030101,3,2p,01,20030101
100,1,xx,yy,20030102,2,21,t5,20030102,3,2p,01,20030102


 
Use BCP to load the data into a temporary table which is setup in the same format as the text file. Then use T/SQL to change the data format to the way you want it to look by using a Cross Tab Query.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
I dont have access to permission for bulk insert/bcp.
Could please suggest other alternatives..
 
jrprogr, can you maybe use DTS (Data Transformation Services) in EM(Enterprise Manager)to import the text file?

Michael
 
Thanks Michael...
Suggest me the other alternatives where i can't use DTS,
BCP,Bulk insert..Since i dont have permissions execute on Master Db..Since i have to use Xp_shell commands for bulk insert.. and File system for DTS..
 
I think you are out of SQL solutions.

You may be able to throw something together in a different language to parse the CSV file and insert its' contents to a table. I would think that you could talk to your DBA, and explain that you don't want to re-invent the wheel, and possibly get permission to use DTS or bcp.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom
 
I would recommend requesting that the DBA give you bulk insert rights.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
I got the appropariate access..
Now i got the below error on executing the bulk insert..

Could not bulk insert because file 'mycomputername\\c:\test.txt' could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).


Executed Scripts:
bulk insert dbo.test from 'mycomputername\\c:\test.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '')

Please correct me in above scripts...
 
The command should look like this.
Code:
bulk insert dbo.test from '\\mycomputername\c$\test.txt' with (FIELDTERMINATOR='|',ROWTERMINATOR = '')

You will need to make sure that the Windows Account which the SQL Server runs as has Admin Access to your workstation.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top