| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2008-05-14 : 10:57:35
|
| Greetings,Is there a stored procedure out there that inserts into a table from a tab,space or comma delimited file with multiple rows and column headers?I was gonna do this but too much security issues...BULK INSERT dbo.ARDISOptimizations FROM 'C:\MickeyMouseIt.txt' WITH ( FIELDTERMINATOR = '\t', --ROWTERMINATOR = '\n', ROWTERMINATOR = '''+CHAR(10)+''' , CODEPAGE = 'RAW', DATAFILETYPE = 'CHAR' ) Thanks!!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-14 : 11:16:23
|
| Did you try using bcp utility? |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-05-14 : 11:19:05
|
| Yes I did try but all the security issues rear their ugly head there too. I am just a temp application developer for a new IT shop for a huge company that just implemented SQL server so the security issue is an uphill battle. so i just need to move on with a different approach :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-14 : 11:34:10
|
| Ok. Can you use SSIS. then use SSIS export import for importing this file to table. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-05-14 : 14:41:38
|
| well the BULK INSERT is user app initiated not scheduled. also that bcp utility is so picky but I am getting close to a solution. One problem was the import file that is spit out by a third party tool was not properly delimited. so back to the drawing board.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-14 : 14:44:30
|
quote:
Originally posted by yosiasz
well the BULK INSERT is user app initiated not scheduled. also that bcp utility is so picky but I am getting close to a solution. One problem was the import file that is spit out by a third party tool was not properly delimited. so back to the drawing board..
You might need to use format file if you want to specify customised delimiter info. Refer this:-http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 14:47:01
|
quote:
Originally posted by yosiasz
Yes I did try but all the security issues rear their ugly head there too.
Could you explain? bcp doesn't require much in terms of security, unlike BULK INSERT. bcp would require the same permissions as SSIS or DTS.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-05-14 : 15:30:25
|
| AWESOME bcp working!! it was this easy!?!?! ok how do i exclude the column names or should I remove those from the import file?Thanks a lot!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-14 : 15:33:16
|
| If you want to exclude columns, you can either modify the import file or import the data into a staging table and then grab just what you need. I suppose you could also use a format file, but I hate format files!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
|
bulk insert