| Author |
Topic |
|
rikleo2001
Posting Yak Master
185 Posts |
 Posted - 2005-10-24 : 18:55:46
|
| Hello Experts,I have a file of type DCI(Data control Input) similar to Text File.I would like to open it in my Stored procedure and Import into system one by one row, as I need to do some process on every row.I tried the Follwoing.select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\temp\;','select * from nmktcd02.dci')The Follwoing error I got, I checked nowhere the file was opened.Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.]Thanks in advanceSKR |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 19:09:18
|
| Import the rows into a staging table all at one time. You can then perform row by row queries from the staging table to do any of your calculations.Tara |
 |
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2005-10-24 : 19:13:44
|
| Thanks for prompt reply.But I never created those tables before? could please give me at least some link or guidelines.Much appriciatedSKR |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 19:17:59
|
| A staging table is just a table that matches your input file. So if it has 15 columns in your file then so does your staging table. It can make things easier if you just use varchar for each of the columns. The table is created manually.Tara |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2005-10-24 : 19:29:36
|
| Thanks Again for your valuable time.I have fixed Length Text File.Every Single Line has fixed length. So for that reason I created a One Field Table nvarchar(4000) AND IMPORTED directly into the that table! works fine, but I lost the order, which I want....Any more ideas?Tahnks in AdvanceSKR |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-24 : 19:55:11
|
| The order was preserved, however there's no way to guarantee retrieval of that same order with a select statement. So you'll need to add an identity column or some other column that signifies order. Identity would be easiest that way you can have SQL Server manage that column. I believe you can tell the import to ignore this column.How many rows are in your file?Tara |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2005-10-24 : 20:40:43
|
| 30000 Rows only. and it is talking 7-9 minsI tried with Identity column as well, but no luck, still having disorder Data.Please let me know what should I do apart from that? any other ideas? I would simple like to read that file in Cursor.I tried that Command, but I don't know how to assign it to cursor?exec master..xp_cmdshell 'copy c:\temp\nmktcd02.dci c:\temp\nmktcd02.txt'Waiting for favourable reply.Thanks so muchSKR |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-25 : 12:33:45
|
| The command that you posted is just copying a file to a new name. It isn't doing anything. You can't import and cursor through it at the same time. Somehow you are going to have to import this file into a staging table and have some kind of column that can be referenced for the sorting of your data.Tara |
|
|
|
|
Importing DCI (Text Files) into TEMP tables