Parsing a text file with fixed and delimited fields

[lunaclover]

Hi - I am attempting to create a table using DTS wizard to import from a text file. There are six tables, most of them usually being 3 columns long. In these text files, the first two columns are fixed length, so that's easy.. the problem comes with the last column which just ends at the end of the text on the first row, with a return. This draws the red line there, and cuts off the end of the string in subsequent rows, and concatenates the remainder to the beginning of the next row. This is wrong.

Here's an illustration

Raoel Simenson     649 Broken Arrow Lane     Noble, OK
Lily Halen         52 Old Orchard            Springfie
ld, MOSam Thompson    122 Fulton Rd             Detroi
t, MITammy Harrison       etc etc etc

Does this makes sense? This is what happens when I use fixed length.

The problem is that this cleaning up the text file beforehand needs to be as automated as possible since we are downloading these text files from the FTP site weekly. I have created a DTS package to download from the ftp site.

Does anybody have any suggestions for dealing with problem automatically? I read that SQL Server isn't really supposed to be used as a parser, but there's this article I found called Using DTS to Automate a Data Import Process on SQLTeam.com that suggests otherwise. I am wondering if I need to get that complicated as this article is suggesting. You can find the article at

http://www.sqlteam.com/item.asp?ItemID=10627.

If you so please.

Thanks in advance for any help anyone could provide. I am new to all this so I really appreciate it.

Thanks,
Luna

 

[lunaclover]

Hi - not sure why nobody is responding..

So, I created a txt file that looks like

column           width    
Name             30
Address          40
Location         30

I wrote a stored procedure and created a query using ddl that creates the tables. Now I am trying to create the package.. I connected to the ftp site, and it says the next step is to create the Transform Data Task.. it says if the data is aligned per the file format above, the column marker (black line) will automatically be placed at the 30 position, one at the 70 position, and the eof marker (red line) will be at 100.

This doesn't work with mine, how am I supposed to reference it? How do people learn how to do this?

can somebody please help, I would be forever grateful.

Thanks,
Luna