reading a fixed width text file

  • Thread starter Thread starter nobody
  • Start date Start date
N

nobody

I'm connecting to and trying to read a fixed width text file. The problem
I'm having is when there is a column with % data in in (like 12.5%). I
would like to read this in as .125, but if I could even get it in as 12.5 I
would be happy. If I specify Text (in the Schema.ini) for the data type,
it reads in fine (as text). If I specify Double, the field comes in blank.
I suppose I could decrease my field width by 1 and add a dummy field with a
width of 1 to "eat up" the % sign, but this sounds like a "kludge" and I'd
like to avoid it if possible.

Here's my code...
Dim MyConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test\;" & _
"Extended Properties=""text;"";"
Dim MyDataSet As New DataSet
Dim MyConnection As New OleDbConnection
Dim MyCommand As New OleDbCommand
Dim MyDataAdapter As New OleDbDataAdapter
Dim MyDataTable As DataTable
Try
MyConnection.ConnectionString = MyConnectionString
MyConnection.Open()
MyCommand.CommandText = "Select * From C:\Test\" & txtFile.Text
MyCommand.Connection = MyConnection
MyDataAdapter.SelectCommand = MyCommand
MyDataAdapter.Fill(MyDataSet, "MyFile")
MyDataTable = MyDataSet.Tables("MyFile")
...
' (I had to include the C:\Test\ in the Select command to get it to
work...
' Leaving it out worked fine for CSV, but not for FixedLength.)

Here's my Schema.ini
[Text.txt]
Format=FixedLength
ColNameHeader=True
Col1=MyField1 Currency Width 9
Col2=MyField2 Double Width 9
Col3=MyField3 Double Width 9
CharacterSet=ANSI

Here's Text.txt
Field1 Field2 Field3
$1236.50 12.5% 5678.90
$1234.50 12.5% 5678.90
$2345.67 45.0% 3682.41
 
Hi,

No, you cannot avoid it just reading data through provider. In your case I
would suggest to read data as is and then manipulate it inside of the
dataset itself. For example, after loading data you could add another
column, which could be populated from the existing one, but in transformed
presentation
 
Back
Top