April 13, 2004 at 2:42 pm
I ahve a text data which has 1000 records there are 10 such text files.I am pasting just 2 records to show how the data is.
The records end with The word END.
u see each of the cloumn is seperated by delimiter with >.The
a> ,y> are used to seperate the columns.
Could u tell me how do i import this data using DTS or othere way using SQL SERVER.
"
a>Alien, S. K. J.
a>Wattendorf, R. J.
y>1987
t>The triploid grass carp: status and management implications.
j>Fisheries
p>
u>
k>Grass carp, Ctenopharyngodon idella, management
v>12
i>4
g>20-24
b>
l>
e>End
a>Aliyev, D. S.
y>1965
t>The reproduction of the grass carp Ctenopharyngodon idella and of the silver and bighead carps Hypophthalmichthys molitrix and Aristichthys nobilis established in the Amur Darya basin.
j>Journal of Ichthyology
p>
u>
k>Grass carp, Ctenopharyngodon idella, reproduction, Amur Darya basin, Hypophthalmichthys molitrix, Aristichthys nobilis
v>5
i>4
g>37
b>
l>
e>End
Thanks
MAnish
April 13, 2004 at 6:15 pm
Your files need to look like a reasonable input file. You cannot have more than one type of delimiter, and the fields should be in 1 row to be called a row.
Perhaps you should ask the person who generated your source file to try and at least give you a source file that adheres to some standard for delimited files.
Other than writing a conversion application to fix the source file first to a standard delimited file, I don't think there is a way of getting your data into SQL
April 15, 2004 at 2:37 pm
Since the data file is not a standard format one, your first attemp should be to format it according to the need and then use it. The solution i like to suggest might sound bit amature, worth giving a try
Create a DTS program
1 step - parse the main file and create a formatted temp file out of it. That could be done by having some logic like this..
The output would be having a row of data having '~' as delimiter.
Set f = fs.OpenTextFile ( sPATH, ForReading, True)
Do While f.AtEndOfStream <> True
sRead = fChem.Readline
strLine = ""
Column_Name = mid(sRead, 1,InStr(1,sRead,">")-1)
Column_Value = mid(sRead, InStr(1,sRead,">")+1, Len(sRead))
if Column_Value = "End" Then
Set fTemp = fs.OpenTextFile ( sPATH, ForWriting, True)
fTemp.WriteLine strLine
End If
strLine = strLine & "~" & Column_Value
Loop
2 step - Load the formatted file into the table
Hope this help you!!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply