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 |
|
asgast
Posting Yak Master
149 Posts |
 Posted - 2009-01-16 : 10:42:56
|
| Hi all,I have the following problem:Story.I need to extract lots of data from database and I have to be exact about positions, because later this file will be imported on the clients side and I have no knowledge on how it goes on there. No chances of dabbling with the data here.Data Processing department uses our utility to describe the format. I can play with data as much as I want here.Usually the give me the names of the columns length of data that is stored there and positions - where the data entry must start.I sort format and etc.When its time to export data I create fmt file using the description, that was provided. I export data using bcp utility. All works fine :) almost. I can play with data here, some rules apply.ProblemWhen DP wants two columns to have spaces between them they give me column1 starts 100 length 10 column2 starts 510 length 10 it means that there must be 400 spaces of empty space between the end of column1 and start of column2. In that case i just fill the separator in bcp file with that many spaces :) it works until i insert more then 100 spaces there, then i get an error saying unexpected end of file.Any suggestions? insight on the problem? help?Is there any other way to do the extract?P.S. I cant add 200 spaces to column1 instead of separator. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:02:43
|
| why they give you position numbers rather than delimiter info? |
 |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-01-16 : 11:09:57
|
| Another task is to create a datamap for clients application, based on the positions, to be used later. And since they have been working with that application for a long time that is just the way it flows :) IF I can get info on delimeter from them, how will it help me? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:14:55
|
quote:
Originally posted by asgast
Another task is to create a datamap for clients application, based on the positions, to be used later. And since they have been working with that application for a long time that is just the way it flows :) IF I can get info on delimeter from them, how will it help me?
then you can just specify delimiter on format file and then it will distingusih each column values correctly. |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-01-16 : 11:22:55
|
| Its not possible to make static list of delimiters because every time the extract data they use different columns so fmt file varies. I can figure out what kind of delimiter they need based on position and length.AS EXAMPLE i create the following fmt based on their selection QA position 1 length 360, INF3 position 371 length 109.0 2 1 SQLCHAR 0 360 " " 1 QA SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 10 "\r\n" 2 INF3 SQL_Latin1_General_CP1_CI_AS it worksbut if i need QA position 1 length 360, INF3 position 1371 length 10 file would be9.0 2 1 SQLCHAR 0 360 "1010 spaces here" 1 QA SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 10 "\r\n" 2 INF3 SQL_Latin1_General_CP1_CI_AS i get an error about corrupted fmt file this is my problem |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 11:29:32
|
| then what i suggest is use script task to open file using file object and parse the data and check for position numbers to get individual values. |
|
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2009-01-16 : 11:35:01
|
| I thought about it, but the best solution would be to stay with sql only due to personal reasons/task specification. |
|
|
|
|
|
|
|
|
bcp utility and maximum separator