Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LeoC
Starting Member
37 Posts |
 Posted - 2005-10-05 : 16:48:42
|
All,I had a good working BCP and FMT until last month when the data was changed. More fields were added, no problem there.But after some research and the source (MainFrame) input we have a problem I haven't seen before.The problem is basically one (1) more delimiter of course randomly. Mi delimiter is the regular 'pipe ( | )', the problem presents when one of the text fields has "appended" two (2) regular valid data into one field but using the "pipe" within the field itself to append these 2 pieces of data in the same field delimited by the same delimiter.Thus, some rows I have the regular delimiter count (i.e. 94 vs. the wrong ones 95).I wonder if there is a way to use "field quoted delimiter" in the format file. Soon I'll be having the same problem in tables that are much bigger wide and long thus I had to split the load into 2 SQL tables as recomended in this forum.Sample:...|field2|field3|field4a | field4b|field5|... see that field4 is 2 texts, reason why I was thinking on Quoted Field Delimiter but it has to be alloed in a format file.Any ideas, suggestions or recommendations will be GREATLY appreciate it.For other valid reasons we have to stick to BCPs command line with handly created Format file; as I said id was working perfectly until the number of field with the sample 'field4' came up and very likely show the same effect in table that could not handle DTS.Thanks a lot in advance for any response and/or idea to solve the issue.p/s: changing delimiter more than likely will create problems in other areas, so I'm trying to avoid that approach. lec. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-06 : 04:08:09
|
| Seehttp://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.htmlIt shows how to deal with quote delimitted strings in a format file. It's for comma delimtted but easy to change for pipe delimitted.If your files are not very big or performance is not an issue then it's easier to use a function to split up the columns.==========================================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. |
 |
|
|
|
|
|
Help on BCP and the FMT file