Importing .dat file

  • Thread starter Thread starter Rnt6872
  • Start date Start date
R

Rnt6872

Hello All,
I have a file that I retrieve like this:


7102702007010822222220656017000840314RDSSAL00000011351770610010059900
7102702007010822222220656995000840314RDSSAL00000060077510610010010000
7102702007010822222220657588000840314RDSSAL00000060014810610010079900
7102702007010822222220658315000840314RDSSAL00000026967970610010057500
7102702007010822222220659950000840314RDSSAL00000083792400610010089900

I know the number of spaces that denotes each field. I need some help
with a way to import the data into a database table.

Thank you.
 
You could use the brute force method. There may be a more elegant way
to do this, but this will work.

As for writing to the database, what database are you using?
If you're using SQLServer, are you going to use stored procedures
to insert the records, or just do them yourself. Here's an example
just writing them yourself. This is crude, but effective.
The "select * from myTable" is imprudent, especially if your
table has a lot of rows.

Dim SQLString as String = "SELECT * FROM myTable"
Dim cn as SqlConnection = New SqlConnection(myConnectionString)
cn.Open()
Dim da as New SqlDataAdapter(SQLString, cn)
Dim dt as DataTable = New DataTable()
da.Fill(dt)

'Read the file all at once, split the lines by CrLf
Dim crlfs() as String = {ControlChars.CrLf}
Dim lines() as String = File.ReadAllText("c:\data.txt").Split(crlfs, _
StringSplitOptions.None)
Dim numOfLines = lines.Length 'count of lines
'read through the lines and pick out the data
For i As Integer = 0 to lines.Length - 1
Dim rw as DataRow = dt.NewRow()
rw("FirstField") = lines(i).Substring(0,5)
rw("SecondField") = lines(i).Substring(5,1)
rw("ThirdField") = lines(i).Substring(6,3)
dt.Rows.Add(rw)
Next i
da.Update(dt)
cn.Close()

Robin S.
 
Rnt,

That depends how you want it in the database table, in my idea we cannot see
that.

It can be that you want in this case 5 strings and ask us to devide on
string to 5.

That you can do with Substring in a loop.

Cor
 
jesus that is hilarious

no wonder Robin likes VB she is a ****ing NooB that doesn't know jack
shit about SQL Server

-Aaron
 
¤ Hello All,
¤ I have a file that I retrieve like this:
¤
¤
¤ 7102702007010822222220656017000840314RDSSAL00000011351770610010059900
¤ 7102702007010822222220656995000840314RDSSAL00000060077510610010010000
¤ 7102702007010822222220657588000840314RDSSAL00000060014810610010079900
¤ 7102702007010822222220658315000840314RDSSAL00000026967970610010057500
¤ 7102702007010822222220659950000840314RDSSAL00000083792400610010089900
¤
¤ I know the number of spaces that denotes each field. I need some help
¤ with a way to import the data into a database table.

If this is a fixed length file you can create a schema.ini and read it in. There's no mention what
sort of database table you are importing into:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Dim TextConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My
Documents\TextFiles" & ";" & _
"Extended
Properties=""Text;HDR=NO;""")

TextConnection.Open()

Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [SQLTable] IN ''
[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] FROM
[TextFile#txt]", TextConnection)

TextCommand.ExecuteNonQuery()
TextConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
The REAL answer is to ignore messages posted by trolls and use a method that
is provided by someone who KNOWS what they want to communicate to the
requester that will assist them in resolving their issues.

Bruce
 
jesus that is hilarious

no wonder Robin likes VB she is a ****ing NooB that doesn't know jack
shit about SQL Server

-Aaron
Click to expand...

Maybe he does, maybe he doesn't... but you have shown less knowledge in this
area than he has, so I'd bet on Robin's answer being correct before I'd
trust ANYTHING you posted.

Bruce
 
Bruce W. Darby said:
Maybe he does, maybe he doesn't... but you have shown less knowledge
in this area than he has, so I'd bet on Robin's answer being correct
before I'd trust ANYTHING you posted.

Bruce
Click to expand...

Hmmm. That's not much of a comparison. It's obvious that I know
more than Aaron does, because I answered the question.
Besides, the OP isn't using SQLServer, he's using Firebird.

And just to pi** Aaron off: VB2005 *is* VB.Net. Access is VBA.

Robin S.
 
Back
Top