Databases, Typed Datasets, and Flat Files oh my!

Ben

So, at my place of employment, we use a national standard to transmit
data between certain applications. This standard consists of a fixed
width, flat file 4500-some-odd chars wide that contain 375-some-odd
columns (Not delimited). This standard is not changing anytime soon, so
I attempting to write a library of some sort to read and write these
files within our SQL databases (which use 160 of those columns, but
completely ignore the rest).

Now, the way I would attempt to understand this is: Flat file gets
sucked into dataset, dataset gets shoved into database (or vicea
versa). So... All of the stuff I see for typed datasets is "XML! XML!
XML!" but I'm just looking at a way of specifying a dataset in which
there is a table that says "Field1 is chars 1-10, Field2 is chars
10-14..." etc etc etc.

So, people infinitely more talented then me, how would you attempt to
do this?

Nov 21 '05 #1

Subscribe Reply

1721

Marina

As far as I know, there is no way to do this other then to manually read the
file, get the right characters, and then manually add the rows to the
dataset or database. There is no built in mechanism I am aware of us to
process these kinds of files.

"Ben" <sp******@innis mir.net> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

So, at my place of employment, we use a national standard to transmit
data between certain applications. This standard consists of a fixed
width, flat file 4500-some-odd chars wide that contain 375-some-odd
columns (Not delimited). This standard is not changing anytime soon, so
I attempting to write a library of some sort to read and write these
files within our SQL databases (which use 160 of those columns, but
completely ignore the rest).

Now, the way I would attempt to understand this is: Flat file gets
sucked into dataset, dataset gets shoved into database (or vicea
versa). So... All of the stuff I see for typed datasets is "XML! XML!
XML!" but I'm just looking at a way of specifying a dataset in which
there is a table that says "Field1 is chars 1-10, Field2 is chars
10-14..." etc etc etc.

So, people infinitely more talented then me, how would you attempt to
do this?

Nov 21 '05 #2

Cor Ligthert [MVP]

Ben,

I don't think that it will give you more effort than placing it directly in
your sqlsever with a sql insert command and a sqlnonquery.

However you can get the schema from your datatabase with

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

Than you can use the normal mid or substring commands as you now probably
already use to to fill the dataitems in the new created rows.

If you have less than 100 columns, than you can use the commandbuilder to
create the commands and update.

I hope this helps,

Cor

Nov 21 '05 #3

AMDRIT

I would do something like this: (Not in anyway tested.! And could be
seriously optimized and featured.)

Public Class FlatFile

Event ReadProgress(By Val PercentComplete As Integer)
Event ReadComplete()
Event ReadFailed()
Event ReadStarted()

'Reads a flat file based on a template
'Template file looks like
'Just one line with the column size
'15,30,10,1,3,4 ,5,6,9

Private m_DBFileName As String
Private m_SFileName As String
Private m_DataRows As ArrayList
Private m_StreamReader As System.IO.Strea mReader

Private m_HasRead As Boolean

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As
String, ByVal Create As Boolean)

'Validate DB file exits
If System.IO.File. Exists(DBFileNa me) Then
Else
If Not Create Then Throw New ApplicationExce ption("Bad DB File
Name")
End If

'remember the DB file location
m_DBFileName = DBFileName

'Validate Schema file exits
If System.IO.File. Exists(SchemaFi leName) Then
'remember the Schema file location
m_SFileName = SchemaFileName
Else
Throw New ApplicationExce ption("Bad Schema File Name")
End If

End Sub

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As
String)

MyClass.New(DBF ileName, SchemaFileName, False)

End Sub

Private ReadOnly Property DBReader() As System.IO.Strea mReader
Get
If m_StreamReader Is Nothing Then
m_StreamReader = New System.IO.Strea mReader(m_DBFil eName)
End If
Return m_StreamReader
End Get
End Property

Private ReadOnly Property Schema() As Integer()
Get

Dim objTempReader As System.IO.Strea mReader
Dim sLine As String
Dim asEntries() As String
Dim aiEntries() As Integer

objTempReader = New System.IO.Strea mReader(m_SFile Name)

sLine = objTempReader.R eadLine()

objTempReader.C lose()

objTempReader = Nothing

If Not sLine Is Nothing Then
asEntries = sLine.Trim.Spli t(",")

ReDim aiEntries(asEnt ries.Length - 1)

For i As Integer = 0 To asEntries.Lengt h - 1
aiEntries(i) = CType(asEntries (i), Integer)
Next

End If

End Get
End Property

Public Sub ReadData()

'Read it only once
If m_HasRead Then Exit Sub

Dim sLine As String

'File Related
Dim oFileInfo As System.IO.FileI nfo
Dim iFileSize As Integer

'Db Related
Dim iRowSize As Integer
Dim iRowCount As Integer
Dim aCols() As Integer

'Get file size
oFileInfo = New System.IO.FileI nfo(m_DBFileNam e)
iFileSize = oFileInfo.Lengt h

'Load our schema
aCols = Me.Schema

'Determine row size
For Each i As Integer In aCols
iRowSize += i
Next

'Determine row count
iRowCount = Int(iFileSize / iRowCount)
RaiseEvent ReadStarted()

sLine = DBReader.ReadLi ne
ParseLine(sLine )

Do While Not sLine Is Nothing
sLine = DBReader.ReadLi ne
ParseLine(sLine )
Loop

RaiseEvent ReadComplete()

m_HasRead = True

End Sub

Private Sub ParseLine(ByVal sLine As String)

Dim Record() As String
Dim aCols() As Integer
Dim iPos As Integer

aCols = Me.Schema

ReDim Record(aCols.Le ngth - 1)

For i As Integer = 0 To aCols.Length - 1
Record(i) = sLine.Substring (iPos, aCols(i))
iPos += aCols(i)
Next

m_DataRows.Add( Record)

End Sub

Public Function Add(ByVal Value() As String) As Integer

ValidateRow(Val ue)

Return m_DataRows.Add( Value)

End Function

Public Property Row(ByVal Index As Integer) As String()
Get
Return m_DataRows(Inde x)
End Get
Set(ByVal Value() As String)

ValidateRow(Val ue)

m_DataRows(Inde x) = Value

End Set
End Property

Private Sub ValidateRow(ByV al Value() As String)
Dim aCols() As Integer

aCols = Me.Schema

'Validate the updated values
If aCols.Length <> Value.Length Then
Throw New ApplicationExce ption(String.Fo rmat("Invalid Row Length"))
End If

For i As Integer = 0 To aCols.Length - 1

If Value(i).Length > aCols(i) Then
Throw New ApplicationExce ption(String.Fo rmat("Invalid Data Length:
Max Data Lenght for column{0} is {1}", i, aCols(i)))
ElseIf Value(i).Length > aCols(i) Then
'Pad the data with spaces
Value(i) = Value(i).PadRig ht(aCols(i), " ")
End If

Next
End Sub

Public Sub SaveDB()
Dim sw As System.IO.Strea mWriter
Dim sLine As System.Text.Str ingBuilder

'Close our read connection
m_StreamReader. Close()
m_StreamReader = Nothing

'Delete our old file
System.IO.File. Delete(m_DBFile Name)

sw = New System.IO.Strea mWriter(m_DBFil eName)

For Each s() As String In m_DataRows
sLine = New System.Text.Str ingBuilder
For i As Integer = 0 To s.Length - 1
sLine.Append(s( i))
Next
sw.WriteLine(sL ine.ToString)
Next

sw.Close()

End Sub

Protected Overrides Sub Finalize()
MyBase.Finalize ()

If Not m_StreamReader Is Nothing Then
m_StreamReader. Close()
End If

m_StreamReader = Nothing

End Sub

End Class
"Ben" <sp******@innis mir.net> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

So, at my place of employment, we use a national standard to transmit
data between certain applications. This standard consists of a fixed
width, flat file 4500-some-odd chars wide that contain 375-some-odd
columns (Not delimited). This standard is not changing anytime soon, so
I attempting to write a library of some sort to read and write these
files within our SQL databases (which use 160 of those columns, but
completely ignore the rest).

Now, the way I would attempt to understand this is: Flat file gets
sucked into dataset, dataset gets shoved into database (or vicea
versa). So... All of the stuff I see for typed datasets is "XML! XML!
XML!" but I'm just looking at a way of specifying a dataset in which
there is a table that says "Field1 is chars 1-10, Field2 is chars
10-14..." etc etc etc.

So, people infinitely more talented then me, how would you attempt to
do this?

Nov 21 '05 #4

AMDRIT

I modified it a bit

Public Class FlatFile

Event ReadProgress(By Val PercentComplete As Integer)
Event ReadComplete()
Event ReadFailed()
Event ReadStarted()
Event SaveComplete()

'Reads a flat file based on a template
' 1. A non full Template file looks like
' 15,30,10,1,3,4, 5,6,9
' 2. A full Schema Template looks like
' ~ColumnName, PadDirection, DataLength
' OderID,2,9
' OrderDate,2,8

Private m_DBFileName As String
Private m_SFileName As String
Private m_FullSchema As Boolean
Private m_DataRows As ArrayList
Private m_HasRead As Boolean
Private m_aCols() As Column, m_ColCount As Integer

#Region " Column Descriptor"

Public Class Column

Public Enum PadDirections
pdLeft = 1
pdRight = 2
End Enum

Private m_Ordinal As Integer
Private m_ColumnName As String = String.Empty
Private m_PadDirection As PadDirections
Private m_Length As Integer

#Region " Constructors"

Public Sub New(ByVal Ordinal As Integer, ByVal ColumnName As String,
ByVal PadDirection As PadDirections, ByVal Length As Integer)
m_Ordinal = Ordinal
m_ColumnName = ColumnName
m_PadDirection = PadDirection
m_Length = Length
End Sub

Public Sub New(ByVal Ordinal As Integer, ByVal PadDirection As
PadDirections, ByVal Length As Integer)
MyClass.New(Ord inal, String.Format(" Column{0}", Ordinal),
PadDirection, Length)
End Sub

Public Sub New(ByVal Ordinal As Integer, ByVal Length As Integer)
MyClass.New(Ord inal, String.Format(" Column{0}", Ordinal),
PadDirections.p dRight, Length)
End Sub

#End Region

#Region " Fields"

Public ReadOnly Property Ordinal() As Integer
Get
Return m_Ordinal
End Get
End Property

Public ReadOnly Property ColumnName() As String
Get
Return m_ColumnName
End Get
End Property

Public ReadOnly Property PadDirection() As PadDirections
Get
Return m_PadDirection
End Get
End Property

Public ReadOnly Property Length() As Integer
Get
Return m_Length
End Get
End Property

#End Region

End Class

#End Region

#Region " Constructors"

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As String,
ByVal FullSchema As Boolean, ByVal Create As Boolean)

'Validate DB file exits
If System.IO.File. Exists(DBFileNa me) Then
Else
If Not Create Then Throw New ApplicationExce ption("Bad DB File Name")
End If

'remember the DB file location
m_DBFileName = DBFileName

'Validate Schema file exits
If System.IO.File. Exists(SchemaFi leName) Then
'remember the Schema file location
m_SFileName = SchemaFileName
Else
Throw New ApplicationExce ption("Bad Schema File Name")
End If

m_FullSchema = FullSchema

m_DataRows = New ArrayList

m_ColCount = -1

End Sub

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As String,
ByVal FullSchema As Boolean)

MyClass.New(DBF ileName, SchemaFileName, FullSchema, False)

End Sub

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As String)

MyClass.New(DBF ileName, SchemaFileName, False, False)

End Sub

#End Region

#Region " Data Row Implementation"

Public Function Add(ByVal Value() As String) As Integer

ValidateRow(Val ue)

Return m_DataRows.Add( Value)

End Function

Public Property Row(ByVal Index As Integer) As String()
Get
Return m_DataRows(Inde x)
End Get
Set(ByVal Value() As String)

ValidateRow(Val ue)

m_DataRows(Inde x) = Value

End Set
End Property

Public Function RowCount() As Integer
Return m_DataRows.Coun t
End Function

Public Sub Remove(ByVal Datarow() As String)
m_DataRows.Remo ve(Datarow)
End Sub

Public Sub RemoveAt(ByVal Index As Integer)
m_DataRows.Remo veAt(Index)
End Sub

Private Sub ValidateRow(ByV al Value() As String)
Dim aCols() As Column

aCols = Me.Schema

'Validate the updated values
If aCols.Length <> Value.Length Then
Throw New ApplicationExce ption(String.Fo rmat("Invalid Row Length"))
End If

For i As Integer = 0 To aCols.Length - 1
If Value(i) = String.Empty Then
'Pad the data with spaces
If aCols(i).PadDir ection = Column.PadDirec tions.pdLeft Then
Value(i) = Value(i).PadLef t(aCols(i).Leng th, " ")
Else
Value(i) = Value(i).PadRig ht(aCols(i).Len gth, " ")
End If
ElseIf Value(i).Length > aCols(i).Length Then
Throw New ApplicationExce ption(String.Fo rmat("Invalid Data Length:
Max Data Lenght for column{0} is {1}", i, aCols(i)))
ElseIf Value(i).Length < aCols(i).Length Then
'Pad the data with spaces
If aCols(i).PadDir ection = Column.PadDirec tions.pdLeft Then
Value(i) = Value(i).PadLef t(aCols(i).Leng th, " ")
Else
Value(i) = Value(i).PadRig ht(aCols(i).Len gth, " ")
End If
End If

Next
End Sub

#End Region

#Region " Column Implementation"

Public ReadOnly Property ColumnCount() As Integer
Get
Return m_ColCount
End Get
End Property

Public ReadOnly Property ColumnInfo(ByVa l Index As Integer) As Column
Get
Return m_aCols(Index)
End Get
End Property

Private Sub AddColumnInfo(B yVal Column As Column)

m_ColCount += 1
ReDim Preserve m_aCols(m_ColCo unt)

m_aCols(m_ColCo unt) = Column

End Sub

#End Region

#Region " DB Operations"

Public Sub ReadData()

'Read it only once
If m_HasRead Then Exit Sub
If Not System.IO.File. Exists(m_DBFile Name) Then Exit Sub

'
Dim sLine As String
Dim sr As System.IO.Strea mReader
Dim iRowPos As Integer

'File Related
Dim oFileInfo As System.IO.FileI nfo
Dim iFileSize As Integer

'Db Related
Dim iRowSize As Integer
Dim iRowCount As Integer
Dim aCols() As Column

'Get file size
oFileInfo = New System.IO.FileI nfo(m_DBFileNam e)
iFileSize = oFileInfo.Lengt h

'Load our schema
aCols = Me.Schema

'Determine row size
For Each i As Column In aCols
iRowSize += i.Length
Next

'Determine row count
iRowCount = Int(iFileSize / iRowSize)

RaiseEvent ReadStarted()

sr = New System.IO.Strea mReader(m_DBFil eName)

iRowPos = 0

sLine = sr.ReadLine
ParseLine(sLine )

Do While Not sLine Is Nothing

iRowPos += 1

RaiseEvent ReadProgress(In t((iRowPos / iRowCount) * 100))

sLine = sr.ReadLine
ParseLine(sLine )

Loop

RaiseEvent ReadComplete()

m_HasRead = True

End Sub

Public Sub SaveDB()

Dim sw As System.IO.Strea mWriter
Dim sLine As System.Text.Str ingBuilder

'Delete our old file
System.IO.File. Delete(m_DBFile Name)

sw = New System.IO.Strea mWriter(m_DBFil eName)

Try
For Each s() As String In m_DataRows
sLine = New System.Text.Str ingBuilder
For i As Integer = 0 To s.Length - 1
sLine.Append(s( i))
Next
sw.WriteLine(sL ine.ToString)
Next
sw.Flush()
Catch ex As Exception
MsgBox(ex.ToStr ing)
End Try

sw.Close()
sw = Nothing

RaiseEvent SaveComplete()

End Sub

Private Sub ParseLine(ByVal sLine As String)

'Don't attempt to read an empty line
If sLine Is Nothing Or sLine = String.Empty Then Exit Sub

Dim Record() As String
Dim aCols() As Column
Dim iPos As Integer

aCols = Me.Schema

ReDim Record(aCols.Le ngth - 1)

For i As Integer = 0 To aCols.Length - 1
Record(i) = sLine.Substring (iPos, aCols(i).Length )
iPos += aCols(i).Length
Next

Add(Record)

End Sub

Private ReadOnly Property Schema() As Column()
Get

If m_aCols Is Nothing OrElse m_aCols.Length < 1 Then

Dim objTempReader As System.IO.Strea mReader
Dim sLine As String

objTempReader = New System.IO.Strea mReader(m_SFile Name)

sLine = objTempReader.R eadLine()
ParseColumnInfo (sLine)

Do While Not sLine Is Nothing
sLine = objTempReader.R eadLine()
ParseColumnInfo (sLine)
Loop

objTempReader.C lose()

objTempReader = Nothing

End If

Return m_aCols

End Get
End Property

Private Sub ParseColumnInfo (ByVal sLine As String)

Dim asEntries() As String

If Not sLine Is Nothing Then

asEntries = sLine.Trim.Spli t(",")

If Not m_FullSchema Then

asEntries = sLine.Trim.Spli t(",")

For i As Integer = 0 To asEntries.Lengt h - 1
AddColumnInfo(N ew Column(i, CType(asEntries (i), Integer)))
Next

Else

AddColumnInfo(N ew Column(m_ColCou nt, CType(asEntries (0), String),
CType(asEntries (1), Integer), CType(asEntries (2), Integer)))

End If

End If

End Sub

#End Region

#Region " Destructors"
Protected Overrides Sub Finalize()
MyBase.Finalize ()

If Not m_DataRows Is Nothing Then m_DataRows.Clea r()
m_DataRows = Nothing

End Sub
#End Region

'Sample:

'Dim f As FlatFile

' f = New FlatFile("C:\Da ta.txt", "C:\Schema.txt" , True, False)
' f.ReadData()

' For i As Integer = 0 To f.RowCount - 1
' console.writeli ne(String.Join( "", f.Row(0)))
'
' f.Row(i)(0) = CType(Int((9999 99 - 1) * Rnd() + 1), String)
' f.Row(i)(1) = Now.ToShortDate String
' f.Row(i)(2) = "Some Customer"
' f.Row(i)(3) = "Some Street"
' f.Row(i)(4) = ""
' f.Row(i)(5) = "Some City"
' f.Row(i)(6) = "MN"
' f.Row(i)(7) = "00000"
'
' console.writeli ne(String.Join( "", f.Row(0)))
' Next

' For i As Integer = 1 To 1000

' Dim astring(7) As String
' astring(0) = CType(Int((9999 99 - 1) * Rnd() + 1), String)
' astring(1) = Now.ToShortDate String
' astring(2) = "Some Customer"
' astring(3) = "Some Street"
' astring(4) = ""
' astring(5) = "Some City"
' astring(6) = "MN"
' astring(7) = "00000"

' f.Add(astring)

' Next

' f.SaveDB()

' f = Nothing

' MsgBox("Done!")

'Template File

'OrderID,2,9
'OrderDate,2,8
'CustomerName,2 ,35
'Address1,2,35
'Address2,2,35
'City,2,35
'State,2,2
'ZipCode,2,5

'Data File
'705547 9/8/2005 Some Customer Some Street
Some City MN00000
'533423 9/8/2005 Some Customer Some Street
Some City MN00000
'579518 9/8/2005 Some Customer Some Street
Some City MN00000
'289562 9/8/2005 Some Customer Some Street
Some City MN00000
'301948 9/8/2005 Some Customer Some Street
Some City MN00000
'774739 9/8/2005 Some Customer Some Street
Some City MN00000
'14018 9/8/2005 Some Customer Some Street
Some City MN00000
'760723 9/8/2005 Some Customer Some Street
Some City MN00000
'814489 9/8/2005 Some Customer Some Street
Some City MN00000
'709037 9/8/2005 Some Customer Some Street
Some City MN00000
'45353 9/8/2005 Some Customer Some Street
Some City MN00000
'414032 9/8/2005 Some Customer Some Street
Some City MN00000
'862618 9/8/2005 Some Customer Some Street
Some City MN00000
'790479 9/8/2005 Some Customer Some Street
Some City MN00000
'373536 9/8/2005 Some Customer Some Street
Some City MN00000
'961952 9/8/2005 Some Customer Some Street
Some City MN00000
'871445 9/8/2005 Some Customer Some Street
Some City MN00000
'56237 9/8/2005 Some Customer Some Street
Some City MN00000
'949555 9/8/2005 Some Customer Some Street
Some City MN00000
'364018 9/8/2005 Some Customer Some Street
Some City MN00000
'524868 9/8/2005 Some Customer Some Street
Some City MN00000
'767111 9/8/2005 Some Customer Some Street
Some City MN00000
'53505 9/8/2005 Some Customer Some Street
Some City MN00000
'592458 9/8/2005 Some Customer Some Street
Some City MN00000
'468700 9/8/2005 Some Customer Some Street
Some City MN00000
'298165 9/8/2005 Some Customer Some Street
Some City MN00000
'622696 9/8/2005 Some Customer Some Street
Some City MN00000
'647820 9/8/2005 Some Customer Some Street
Some City MN00000
'263793 9/8/2005 Some Customer Some Street
Some City MN00000
'279342 9/8/2005 Some Customer Some Street
Some City MN00000
'829800 9/8/2005 Some Customer Some Street
Some City MN00000
'824601 9/8/2005 Some Customer Some Street
Some City MN00000
'589162 9/8/2005 Some Customer Some Street
Some City MN00000
'986092 9/8/2005 Some Customer Some Street
Some City MN00000
'910963 9/8/2005 Some Customer Some Street
Some City MN00000
'226866 9/8/2005 Some Customer Some Street
Some City MN00000
'695115 9/8/2005 Some Customer Some Street
Some City MN00000
'980002 9/8/2005 Some Customer Some Street
Some City MN00000
'243931 9/8/2005 Some Customer Some Street
Some City MN00000
'533873 9/8/2005 Some Customer Some Street
Some City MN00000
'106370 9/8/2005 Some Customer Some Street
Some City MN00000

End Class

"AMDRIT" <am****@hotmail .com> wrote in message
news:%2******** *******@TK2MSFT NGP15.phx.gbl.. .

I would do something like this: (Not in anyway tested.! And could be
seriously optimized and featured.)

Public Class FlatFile

Event ReadProgress(By Val PercentComplete As Integer)
Event ReadComplete()
Event ReadFailed()
Event ReadStarted()

'Reads a flat file based on a template
'Template file looks like
'Just one line with the column size
'15,30,10,1,3,4 ,5,6,9

Private m_DBFileName As String
Private m_SFileName As String
Private m_DataRows As ArrayList
Private m_StreamReader As System.IO.Strea mReader

Private m_HasRead As Boolean

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As
String, ByVal Create As Boolean)

'Validate DB file exits
If System.IO.File. Exists(DBFileNa me) Then
Else
If Not Create Then Throw New ApplicationExce ption("Bad DB File
Name")
End If

'remember the DB file location
m_DBFileName = DBFileName

'Validate Schema file exits
If System.IO.File. Exists(SchemaFi leName) Then
'remember the Schema file location
m_SFileName = SchemaFileName
Else
Throw New ApplicationExce ption("Bad Schema File Name")
End If

End Sub

Public Sub New(ByVal DBFileName As String, ByVal SchemaFileName As
String)

MyClass.New(DBF ileName, SchemaFileName, False)

End Sub

Private ReadOnly Property DBReader() As System.IO.Strea mReader
Get
If m_StreamReader Is Nothing Then
m_StreamReader = New System.IO.Strea mReader(m_DBFil eName)
End If
Return m_StreamReader
End Get
End Property

Private ReadOnly Property Schema() As Integer()
Get

Dim objTempReader As System.IO.Strea mReader
Dim sLine As String
Dim asEntries() As String
Dim aiEntries() As Integer

objTempReader = New System.IO.Strea mReader(m_SFile Name)

sLine = objTempReader.R eadLine()

objTempReader.C lose()

objTempReader = Nothing

If Not sLine Is Nothing Then
asEntries = sLine.Trim.Spli t(",")

ReDim aiEntries(asEnt ries.Length - 1)

For i As Integer = 0 To asEntries.Lengt h - 1
aiEntries(i) = CType(asEntries (i), Integer)
Next

End If

End Get
End Property

Public Sub ReadData()

'Read it only once
If m_HasRead Then Exit Sub

Dim sLine As String

'File Related
Dim oFileInfo As System.IO.FileI nfo
Dim iFileSize As Integer

'Db Related
Dim iRowSize As Integer
Dim iRowCount As Integer
Dim aCols() As Integer

'Get file size
oFileInfo = New System.IO.FileI nfo(m_DBFileNam e)
iFileSize = oFileInfo.Lengt h

'Load our schema
aCols = Me.Schema

'Determine row size
For Each i As Integer In aCols
iRowSize += i
Next

'Determine row count
iRowCount = Int(iFileSize / iRowCount)
RaiseEvent ReadStarted()

sLine = DBReader.ReadLi ne
ParseLine(sLine )

Do While Not sLine Is Nothing
sLine = DBReader.ReadLi ne
ParseLine(sLine )
Loop

RaiseEvent ReadComplete()

m_HasRead = True

End Sub

Private Sub ParseLine(ByVal sLine As String)

Dim Record() As String
Dim aCols() As Integer
Dim iPos As Integer

aCols = Me.Schema

ReDim Record(aCols.Le ngth - 1)

For i As Integer = 0 To aCols.Length - 1
Record(i) = sLine.Substring (iPos, aCols(i))
iPos += aCols(i)
Next

m_DataRows.Add( Record)

End Sub

Public Function Add(ByVal Value() As String) As Integer

ValidateRow(Val ue)

Return m_DataRows.Add( Value)

End Function

Public Property Row(ByVal Index As Integer) As String()
Get
Return m_DataRows(Inde x)
End Get
Set(ByVal Value() As String)

ValidateRow(Val ue)

m_DataRows(Inde x) = Value

End Set
End Property

Private Sub ValidateRow(ByV al Value() As String)
Dim aCols() As Integer

aCols = Me.Schema

'Validate the updated values
If aCols.Length <> Value.Length Then
Throw New ApplicationExce ption(String.Fo rmat("Invalid Row Length"))
End If

For i As Integer = 0 To aCols.Length - 1

If Value(i).Length > aCols(i) Then
Throw New ApplicationExce ption(String.Fo rmat("Invalid Data
Length: Max Data Lenght for column{0} is {1}", i, aCols(i)))
ElseIf Value(i).Length > aCols(i) Then
'Pad the data with spaces
Value(i) = Value(i).PadRig ht(aCols(i), " ")
End If

Next
End Sub

Public Sub SaveDB()
Dim sw As System.IO.Strea mWriter
Dim sLine As System.Text.Str ingBuilder

'Close our read connection
m_StreamReader. Close()
m_StreamReader = Nothing

'Delete our old file
System.IO.File. Delete(m_DBFile Name)

sw = New System.IO.Strea mWriter(m_DBFil eName)

For Each s() As String In m_DataRows
sLine = New System.Text.Str ingBuilder
For i As Integer = 0 To s.Length - 1
sLine.Append(s( i))
Next
sw.WriteLine(sL ine.ToString)
Next

sw.Close()

End Sub

Protected Overrides Sub Finalize()
MyBase.Finalize ()

If Not m_StreamReader Is Nothing Then
m_StreamReader. Close()
End If

m_StreamReader = Nothing

End Sub

End Class
"Ben" <sp******@innis mir.net> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .
So, at my place of employment, we use a national standard to transmit
data between certain applications. This standard consists of a fixed
width, flat file 4500-some-odd chars wide that contain 375-some-odd
columns (Not delimited). This standard is not changing anytime soon, so
I attempting to write a library of some sort to read and write these
files within our SQL databases (which use 160 of those columns, but
completely ignore the rest).

Now, the way I would attempt to understand this is: Flat file gets
sucked into dataset, dataset gets shoved into database (or vicea
versa). So... All of the stuff I see for typed datasets is "XML! XML!
XML!" but I'm just looking at a way of specifying a dataset in which
there is a table that says "Field1 is chars 1-10, Field2 is chars
10-14..." etc etc etc.

So, people infinitely more talented then me, how would you attempt to
do this?

Nov 21 '05 #5

Similar topics

1422

Typed DataSets and web services

by: Bj?rn Mor?n | last post by:

I am consuming a web service that returns arrays of classes which can contain other classes (a hierarchy of data). Is there a simple way to move the data into a typed DataSet without writing too much additional code? Observe that this web service does not return a DataSet explicitly, but I still want to handle the data as a DataSet at the consumer end. I havn't found any source of information on how SoapHttpClientProtocol.Invoke works...

C# / C Sharp

2557

Programmatically Create Oracle Typed DataSets?

by: Tim Smith | last post by:

Hi, Is it possible to create a progam to mimic the following action a) drag oledbadapter to form1.cs b) select my oracle ole connection c) enter 'select * from mytable' d) create adapter for select only (no updates) e) repeat for all my tables f) generate dataset for all the those adapters/tables

C# / C Sharp

2452

databind combobox to xml file with typed dataset

by: Phil | last post by:

I need to created a typed dataset for a xml flat file and bind that dataset to a combobox. I was able to do this in VS.Net 2003 easy enough, but can't get it to work in VS.Net 2005. I've come close, but can't seem to get the right steps down to make this happen. Thanks.

.NET Framework

5950

Handling DBNull from databases

by: Charlie Brown | last post by:

When checking for NULL values from a database, normally I would use If Not row("NetSales") Is DBNull.Value Then NetSales = row("NetSales") End If However, if I use a typed dataset then I can't check for NULL the same way

Visual Basic .NET

1122

Design question and databases

by: Flack | last post by:

Hey guys, I'm in the process of designing a small app that will be used by myself and a few other people. Now, there is some data that this app will need to run. I know it is possible to put this data, for example, in an Access database and include it with the app. However, I was wondering if it would be a better idea to just include the data directly in my app. There isn't a huge amount of data and it will rarely, if ever, change. ...

C# / C Sharp

9923

Typed vs untyped datasets

by: Ronald S. Cook | last post by:

I've always used untyped datasets. In a Microsoft course, it walks through creating typed datasets and harps on the benefits. It has you drag all these things around ..wizard, wizard, wizard... code gen, code gen, code gen. What's at the end looks slick, but then there's a ton of generated code that I'm going to have to maintain now. I.e. I like typing things myself (don't like wizards) so I can know exactly what I've done.

C# / C Sharp

2431

Typed DataSets in VS2005

by: Peter Bradley | last post by:

Hi all, This post is sort of tangentially related to my earlier posts on configuration files for DLLs. Does anyone know how to create typed DataSets using VS2005's new DataSet designer, but with the ability to configure the connection string via a config file? The designer seems to hard-code the connection string into the dataset itself, which just can't be right.

C# / C Sharp

3603

typed datasets vs. business objects

by: BillE | last post by:

I'm trying to decide if it is better to use typed datasets or business objects, so I would appreciate any thoughts from someone with more experience. When I use a business object to populate a gridview, for example, I loop through a datareader, populating an array list with instances of a custom class in the middle tier, and then send the array list up to the presentation layer and bind the gridview to it. If I use a typed dataset, I...

.NET Framework

9377

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10136

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9989

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9925

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8814

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6640

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5405

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3913

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2788

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General