select on textfile question

[Peter Proost]

Hi, I'm creating an import module to read data from old textfiles, run some
calculations on them and save them to sql server. I've figured out how to do
a select on textfile using a schema.ini file (my textfile is delimeted by a
| )
But I've got on problem remaining, below you can so two example rows out of
the textfile:

560000||TS|alu dak|0000000067|P400|20|7101|401|0|0|0|0|||||N|J

560039||KM|"De Gaer":
Partoens-Nijs|0000009001|P400|20|54|401|0|0|0|0|||||N|J

The first row is handled just fine, but if I read the second row into a
dataset, I get all NULLS after "De gaer", it doesn't read "De Gaer":
Partoens-Nijs as one column, it seems to trip on the "". Any hint or tip is
much appreciated. I also posted my connectionstring and schema.ini file
below.

Thanks in advance greetz Peter
Private ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"c:\;Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Private conn As New OleDb.OleDbConnection(ConStr)
[TR028C.TXT]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Col1=offnr Text
Col2=datum DateTime
Col3=calcu text
Col4=refer text
Col5=kltnr text
Col6=sercode text
Col7=kcode text
Col8=gcode text
Col9=ktype text
Col10=rp text
Col11=gp text
Col12=ri text
Col13=kw text
Col14=varia text
Col15=adrnr text
Col16=cdlev text
Col17=cdbet text
Col18=pt text
Col19=ofbev text

[Peter Proost]

Just for clarity, there's no vbcrlf after the : in my second example row,
that just happened with posting

560039||KM|"De Gaer":
Partoens-Nijs|0000009001|P400|20|54|401|0|0|0|0|||||N|J
Greetz Peter

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

"Peter Proost" <pp*****@nospam.hotmail.com> schreef in bericht
news:us**************@TK2MSFTNGP09.phx.gbl... Hi, I'm creating an import module to read data from old textfiles, run some calculations on them and save them to sql server. I've figured out how to do a select on textfile using a schema.ini file (my textfile is delimeted by a | )
But I've got on problem remaining, below you can so two example rows out of the textfile:

560000||TS|alu dak|0000000067|P400|20|7101|401|0|0|0|0|||||N|J

560039||KM|"De Gaer":
Partoens-Nijs|0000009001|P400|20|54|401|0|0|0|0|||||N|J

The first row is handled just fine, but if I read the second row into a
dataset, I get all NULLS after "De gaer", it doesn't read "De Gaer":
Partoens-Nijs as one column, it seems to trip on the "". Any hint or tip is much appreciated. I also posted my connectionstring and schema.ini file
below.

Thanks in advance greetz Peter
Private ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"c:\;Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Private conn As New OleDb.OleDbConnection(ConStr)
[TR028C.TXT]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Col1=offnr Text
Col2=datum DateTime
Col3=calcu text
Col4=refer text
Col5=kltnr text
Col6=sercode text
Col7=kcode text
Col8=gcode text
Col9=ktype text
Col10=rp text
Col11=gp text
Col12=ri text
Col13=kw text
Col14=varia text
Col15=adrnr text
Col16=cdlev text
Col17=cdbet text
Col18=pt text
Col19=ofbev text

[Cor Ligthert [MVP]]

Peter,

Your question is not 100% clear for me, do you mean that it is a file with
by a | delimited fields.

Than the normal solution is to split it either with the net split or the
microsoft visual basic split. (AFAIK The last does not create extra space
fields and therefore is nicer to use)

Or do you mean that it is a classic textfile created in old punchcard style,
where than the MID from the Microsoft Visual Basic namespace or the
Substring from the Net Namespace is sufficient.

Cor

[Peter Proost]

Hi Cor, it's a file containing records, and every column is seperated by a |
and every new row in the file is a new record. The file contains 1000 of
rows like my example row:

560000||TS|alu dak|0000000067|P400|20|7101|401|0|0|0|0|||||N|J

This row for example should become one record with 19 columns in an existing
sqlserver table.
I thought if I can do a select on the textfile it would be faster then
reading and splitting every row manualy (is this true?), but to get this to
work I needed a schema.ini file because | is used as seperator.
So I created this routine and the following schema.ini file. Everything
works just fine except when I've got double quotes in one of the colums, for
example:

560039||KM|"De Gaer":|0000009001|P400|20|54|401|0|0|0|0|||||N|J

then everything is ok, untill "De Gaer": if I for example read the record
into a datarow and check it's values I get this:

dr(0) = 560039
dr(1) = NULL
dr(2) = KM
dr(3) = "De Gaer"
dr(4) = NULL
dr(5) = NULL
dr(6) = NULL
and so on

It seems that it can't handle the double quotes, because it gives all NULL
after the last double quote. I hope it's a bit more clear now. But I've got
another question, was I right think that doing selects on textfiles is
faster then reading and splitting them myself?

Thanks and greetz Peter

Private sub read_file

Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"c:\;Extended Properties=""Text;HDR=No;FMT=Delimited"""
Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter
Dim ds As New DataSet

da.SelectCommand.CommandText = "Select * from tr028c.txt"
da.Fill(ds, "TextFile")

End Sub

Schema.ini:
[TR028C.TXT]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)
Col1=offnr Text
Col2=datum DateTime
Col3=calcu text
Col4=refer text
Col5=kltnr text
Col6=sercode text
Col7=kcode text
Col8=gcode text
Col9=ktype text
Col10=rp text
Col11=gp text
Col12=ri text
Col13=kw text
Col14=varia text
Col15=adrnr text
Col16=cdlev text
Col17=cdbet text
Col18=pt text
Col19=ofbev text


"Cor Ligthert [MVP]" <no************@planet.nl> schreef in bericht
news:#Q**************@TK2MSFTNGP12.phx.gbl...

Peter,

Your question is not 100% clear for me, do you mean that it is a file with
by a | delimited fields.

Than the normal solution is to split it either with the net split or the
microsoft visual basic split. (AFAIK The last does not create extra space
fields and therefore is nicer to use)

Or do you mean that it is a classic textfile created in old punchcard style, where than the MID from the Microsoft Visual Basic namespace or the
Substring from the Net Namespace is sufficient.

Cor

[Cor Ligthert [MVP]]

Peter,

I have thought a long while as you, however I would now just split them and
use the indexer to get the fields from the array.

Have a look at those two different split methods.

http://msdn.microsoft.com/library/de...splittopic.asp

http://msdn.microsoft.com/library/de...vafctsplit.asp

Just try them and than I think that you take the second one.

I hope this helps,

Cor


Cor

[Peter Proost]

Hi Cor ,

I've always used the second one, but can I conclude that reading and
splitting the file manualy is faster then performing multiple selects on a
textfile?

Greetz Peter

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

"Cor Ligthert [MVP]" <no************@planet.nl> schreef in bericht
news:ec*************@TK2MSFTNGP14.phx.gbl...

Peter,

I have thought a long while as you, however I would now just split them and use the indexer to get the fields from the array.

Have a look at those two different split methods.

http://msdn.microsoft.com/library/de...us/cpref/html/
frlrfsystemstringclasssplittopic.asp
http://msdn.microsoft.com/library/de...us/vblr7/html/
vafctsplit.asp
Just try them and than I think that you take the second one.

I hope this helps,

Cor


Cor

[Cor Ligthert [MVP]]

Peter,

Did you try it, how many nano seconds you think it will be.

Assuming that your textfile is not something as 10Mb

I think that it is faster and at least consuming less memory. You can split
it row by row and than instert them using the SQL insert with parameters and
execute nonquery into your database.

(You can index the parameters probably the same as your fields and than just
a simple loop).

I hope this helps,

Cor

[Peter Proost]

Hi Cor,

I haven't timed it, but I think I don't need to because I just ran a test
testing both methods, and manualy reading the file was definite faster.

Greetz and thanks Peter