Steve Goram (10/29/2013)
This is my file (fixed lengths):
item 1 tire
color black
price 50
item 2 car jack
color blue
price 35
item 3 screwdriver
color red
price 3
My idea was to import it to a (temp) table first and copy the position number (which is on position 20 in the item line) to a new column. Then fill up the position numbers to indicate which property (color, price) belongs to pos 1,2,3. Maybe there is an easier way to import the data.
Unless the file has trailing spaces on each line, it's not actually "fixed length". If it does have such trailing spaces, let me know how long that second field is.
We could greatly simplify this process using what I call a "Multi-line Definition" in a BCP format file and it would automatically include the necessary "pivot" to strip out the header information and put all of the information for each item in a correct column. Let me know if you're interested in that and I'll give it a shot for ya after work.
--Jeff Moden
Change is inevitable... Change for the better is not.