| Author |
Topic |
|
blessedame
Starting Member
9 Posts |
 Posted - 2003-08-07 : 14:28:17
|
| I have a txt file that contains data exported from SAP.Each row should have 28 fields, all fixed length.I have noticed that for the first few rows, if fields 26,27 and 28 are null values, the row ends prematurely such that instead of putting a CRLF after field 28, it does so after field 25.However when you get to row 12, for example, if all fields have data in them, the CRLF happens after field 28.This is the case throughout the whole file which has about 6500 rows.Can someone please tell me how i can import this file somehow such that SQL rather put the CRLF after field 28 in all cases.I also would need to save this as a .dts since I need to run this package every day.Any help would be great.Thanks |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-07 : 14:54:33
|
| Maybe you could load in to a staging table that has 1 column defined as varchar(7500)...the use a sproc or some sql to break it upt and load it...But I don't remember seeing a fixed width having that problem...where is the data coming from?Brett8-)SELECT POST=NewId() |
 |
|
|
blessedame
Starting Member
9 Posts |
Posted - 2003-08-07 : 14:57:56
|
| Thanks.The data is coming from SAP.I have been told SAP does what I described in the post.For the life of me, I couldn't figure out how to use sql tobreak this type of data up.Any help?Thanks |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-08-07 : 15:52:08
|
| If indeed the file is formatted incorrectly (it is as described in your post) and has in effect a variable # of columns per row, you could use DTS and specify 24 column mappings through the normal "Copy Data" transform, and then an ActiveX transform for the 25th. From there, use VBScript to determine how many actual columns reside in the "25th" column and make the appropriate "DTSDestination()=" statements there.Jonathan{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-07 : 16:15:35
|
quote:
Originally posted by blessedame
Thanks.The data is coming from SAP.I have been told SAP does what I described in the post.For the life of me, I couldn't figure out how to use sql tobreak this type of data up.Any help?Thanks
It's a report, isn't it...who extracted the data, you or someone else...Brett8-)SELECT POST=NewId() |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-07 : 16:21:45
|
| Easiest to use bcp to import into a single column table (maybe a global temp table). From there use substring to break into fields and insert into the production table.If you are doing a lot of this you might want to put the row format in a table so that you don't have to do any coding for new files.==========================================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. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-08-07 : 16:26:26
|
| Assuming the data is less than 8000 bytes wide ...Jonathan{0} |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-07 : 16:37:47
|
quote:
Originally posted by nr
Easiest to use bcp to import into a single column table (maybe a global temp table). From there use substring to break into fields and insert into the production table.If you are doing a lot of this you might want to put the row format in a table so that you don't have to do any coding for new files.
damn that sounds familiar...quote:
Assuming the data is less than 8000 bytes wide ...
Table per segment then...doubt it's that big...would have to go to many tables.I still think the extract should be corrected. I'll bet a  it's a report and not a real extract/unload...Not too much fixed width in client server...Brett8-)SELECT POST=NewId() |
|
|
|
blessedame
Starting Member
9 Posts |
Posted - 2003-08-08 : 09:13:53
|
| problem solved.I inserted the whole data into one single column (table1)then I ran this statement:Insert into table2 (col1, col2, Col3, col4, col5)Select left(Field1, 2), substring (field1, 3, 4), substring (field1, 7,4), substring (field1, 11, 4), substring (field1, 15, 4) from table 1Thanks to everyone who gave this a shot. |
|
|
|
|
Importing fixed length fields