Importing the data from text files...

[jrprogr]

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

 

[mrdenny]

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)

http://www.mrdenny.com

 

[jrprogr]

I dont have access to permission for bulk insert/bcp.
Could please suggest other alternatives..

 

[micang]

jrprogr, can you maybe use DTS (Data Transformation Services) in EM(Enterprise Manager)to import the text file?

Michael

 

[jrprogr]

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..

 

[AlexCuse]

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

 

[mrdenny]

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)

http://www.mrdenny.com

 

[jrprogr]

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...

 

[mrdenny]

The command should look like this.

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)

http://www.mrdenny.com