| Author |
Topic |
|
mikejohnson
Posting Yak Master
153 Posts |
 Posted - 2004-12-22 : 14:17:38
|
| i'm processing ach files. i know how to create a file and upload it to the correct server. now, i need to know how to take return files and import rows from the file into my database. how do i do this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-22 : 14:26:17
|
| What sort of files? csv?seehttp://www.nigelrivett.net/s_ProcessAllFilesInDir.htmlhttp://www.nigelrivett.net/ImportTextFiles.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-12-22 : 14:59:40
|
| fixed length field files. i have multiple fields in the table but only need to copy the data in the file into one field, how do i do that? right now i'm getting this message:Server: Msg 4866, Level 17, State 66, Line 1Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].The statement has been terminated. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-22 : 15:43:37
|
| Did you follow this?It shouldn't matter what the content of a row is just that it has the correct row terminator.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-12-22 : 16:47:27
|
| i have just a regular text file with line breaks. i tried \n and also char(10)+char(13) or whatever. how does it match up fields with data in the file? my file doesn't contain column names and isn't delimited. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-22 : 19:23:22
|
| You must have some means of extracting the data into fields. If they aren't delimitted then how do you know wher one field ends and the next starts?The line breaks will do for the import but to get the data from the staging table into columns you will need to find some way of deciding which data goes where.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-12-27 : 15:25:56
|
| the file is fixed length. so my goal is just to read in each line into the database and then parse it out from there. any help or do you see a reason i'm getting the error i am? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-27 : 16:09:33
|
| Sounds like you are not specifying the correct row terminator.try char(13) or char(10).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2004-12-29 : 10:12:51
|
| i've tried everything as a row delimeter... here is my code. any ideas y it would fail and get the error message above?BULK INSERT db..table FROM "D:\Inetpub\ACHFiles\Avantis\ReturnedFiles\NWCTRN15623608.121704.144016.txt" WITH (FIELDTERMINATOR = ',',ROWTERMINATOR='char(13) + char(10)') |
|
|
|
|
reading files