Insert the part of any txt name into access and...

[meloco]

Hi there,

I have a .mdb file with 8 fields in temp table (import 8 fields from text files into temp), now firstly I need to import a part of text files into a field (called: ID) in access.
Examples my text files are: Z08121.A01607001.txt (need getting: 01607001) ; Z1231.A01203500.txt (need getting: 01203500) => this is my code for this problem:

 Dim str As String

        Dim rsl As DialogResult


        With Me.OpenFileDialog1
            .Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*"
            rsl = .ShowDialog()
            If rsl = Windows.Forms.DialogResult.OK Then
                Dim fi = OpenFileDialog1.FileName
                txtNote.Text = OpenFileDialog1.FileName
                Dim fileName2 As String = IO.Path.GetFileName(fi)
                str = fileName2.Substring(fileName2.Length - 12, 8)
                Me.txtNote.Text = str
            End If

        End With

Now i need to insert it into ID field in temp table but i don't know how to do. I inserted 8 field for content of each txt files. That is my code:

strsql1 = "Insert into temp (F1,F2,F3,F4,F5,F6,F7,F8) Select * From [Text; FMT=Delimited;Database=\\server\Data\test;HDR=No].[" & fileName2 & "]"
                Dim TextCommand As New OleDbCommand(strsql1, TextConnection)
                Try
                    TextCommand.ExecuteNonQuery()
                Catch myerror As Exception
                    MessageBox.Show("Error Connecting to Database: " & myerror.Message)
                End Try

Then, in each my text file always contain this format at the first line:
BG$ddmmyyyy$number$number
and the last line:
EN$number$number$number

(This first line and last line in each txt file will be not inserted into access, i only get from the second line of txt file)

But I must get ddmmyyyy in the first line in txt file to insert a field (named: Received_Date) into temp table in access.

Plz help me, I don't know how to solve this problem...very urgent...
Thanks in advance!

 

[Tom]

First I would drop the temp table altogether.

Create a typed dataset with the fields in your text file. Then drop trying to import it directly from the file to the database, read it into the typed dataset, manipulate & format the fields as needed then send the info into there proper tables in the database.

 

[cjard]

Going along with what Tom said:

'assumes you have filename  in a string
Dim filebits() as String = filename.Split("."c)

Dim id as string = filebits(1)

'assumes you have filepath in a string
Dim fileLines() as String = File.ReadAllLines(filepath)

Dim linebits() as String = fileLines(0).Split("$"c)

Dim fileDate = Date.ParseExact(linebits(1), "yyyymmdd", CultureInfo.CurrentCulture)

linebits(0) = Nothing


Dim dt as New MyDataSet.TempTable
dt.IDColumn.DefaultValue = id
dt.TheDateColumn.DefaultValue = fileDate

ForEach line as String in fileLines

  If line = nothing Then Continue

  Dim ro as MyDataSet.TempRow = dt.NewTempRow()

  ro.Data1Whatever = 'whatever bit of the line
  ro.Data2Whatever = 'whatever part of the line

Next


'upload to the DB
Dim myTA as New MyDataSetTableAdapters.TempTableAdapter
myTA.Update(dt)

If I used anything in tehre you didnt understand or never saw, then you need to read the tutorial in my signature under the DW2 link.. Read the section "Creating a Simple Data App"

 

[meloco]

Thanks u all