Update database from text file

[victor64]

Hello,

I have a text file with data for certain fileds that are in certain position, for example data for field1 is in yhe firts 5 spaces in each line, data for field2 is
from the 7th space up to the 10th space, field3 from the 11th space to the 19 space etc.....

I only know how to open and read the entire text into a form control, how do you read line by line and extrat the data using the "inset" command to populate the ACCESS table. I can use either vb/vb.net

Thanks in advance

Victor

 

[MattP]

Read up on schema.ini.

Schema.ini File (Text File Driver)

My test file C:\Temp2\Fixed.txt

abcde12345fghij67890
abcde11111fghij66666
abcde22222fghij77777
abcde33333fghij88888
abcde44444fghij99999
abcde55555fghij11111
abcde66666fghij22222

My schema.ini file (needs to be in the same directory as the txt file.)

[Fixed.txt]
ColNameHeader=False
Format=FixedLength
Col1=AlphaOne Text Width 5
Col2=NumericOne Single Width 5
Col3=AlphaTwo Text Width 5
Col4=NumericTwo Single Width 5

Dataset with columns 2, 4, and 1

		Dim cn As New OleDbConnection( _
		 String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp2\;Extended Properties={0}Text; FMT=FixedLength{0}", _
		 ControlChars.Quote))

		Dim cmd As OleDbCommand = New OleDbCommand("SELECT NumericOne, NumericTwo, AlphaOne FROM C:\Temp2\Fixed.txt", cn)

		Dim da As New OleDbDataAdapter()
		Dim ds As New DataSet()

		cn.Open()
		da.SelectCommand = cmd
		da.AcceptChangesDuringFill = False
		da.Fill(ds, "File")

		cn.Close()

Gotcha: make sure you set AcceptChangesDuringFill to False otherwise you won't have any changed records when you do your update to Access.