importing a text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hey all

what's the best way to take comma-delimited text file and insert it into a database via oledb provider

thanks in advance
rodchar
 
Check out the "Text" provider.. there's a specific connection string for CSV
and OleDb http://www.connectionstrings.com

You can use it directly as a datasource and use dataAdapter.Fill(dataSet,
"datatableName");

Now, you'll need another adapter to take this data and get it into your DB
but that's pretty easy to do and just like configuring any other adapter.

The only thing you need to do is set the .AcceptChangesDuringFill property
to false so that the rowstate of each of the rows will be Added instead of
unchanged.. http://www.knowdotnet.com/articles/datasetmerge.html

Then just call DataAdapter2.Update(dataSet, "datatableName");

So you have one adapter to fill it, one to submit the updates, and make sure
you set the AcceptChangesDuringFill property to false on the adapter you use
to Fill the dataset with.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
¤ What if it's tab/space delimited, how would my string look?

For tab and space delimited files you need to create a schema.ini file:

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

Below are a couple of examples.

Space delimited:

[Order.txt]
ColNameHeader=False
Format=Delimited( )
CharacterSet=ANSI

Tab delimited:

[TabDelimitedFile.txt]
ColNameHeader=False
Format=TabDelimited
CharacterSet=ANSI


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Let's say it's a comma delimited file, what about the data adapter definition. What would the SQL string look like? For instance "Select * from authors", what would the value after the from clause be for a text file

rodchar
 
Hi Rodchar,

This is pure a dataset with rowstate set in the standard way "unchanged"

Follow for your solution the text from Bill.

(The CSV file should be conform your localized standards)

I hope this helps?

Cor

Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim file As String = "Test1.txt"
Dim path As String = "C:\"
Dim ds As New DataSet
Try
Dim f As System.IO.File
If f.Exists(path & file) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
file, conn)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
DataGrid1.DataSource = ds.Tables(0)
End Sub
 
What if it's a fixed-length file, how would I import that into a database
thanks in advance
rodchar
 
¤ What if it's a fixed-length file, how would I import that into a database?
¤ thanks in advance,
¤ rodchar

You need a schema.ini file, as was previously mentioned, for any file that does not use a comma as
its delimiter.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi

I can't find any examples of how to define a field that has decimal positions? Anybody know of a good reference to this or how the syntax looks in the .ini file

thanks in advance
rodchar
 
The reason I asked is one of the links above it points to the description of the schema.ini at Microsoft. and under optional parameters there's the NumberDigits option and I don't know how to include for a field. sorry for so many questions :(
 
¤ The reason I asked is one of the links above it points to the description of the schema.ini at Microsoft. and under optional parameters there's the NumberDigits option and I don't know how to include for a field. sorry for so many questions :(

Could you post an example (five lines or so) from your text file?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Hi Paul,

You are right, I thought I had followed this thread however it seems not
good enough,

Thanks for your extra information about ODBC and OleDb

Cor
 
For instance

First Name Amount Pai
John 250
Max 250
Sally 250
Josh 255

The amount paid column should have a decimal place of 2
So the amount would actually be 25.00 dollars, etc

Here's my schema file

[Fees.txt
ColNameHeader=Fals
Format=FixedLengt
MaxScanRows=
CharacterSet=OE
Col1="FNAME" Char Width 1
Col2="FeeAmount" Decimal Width 5,

Col2 doesn't work if it looks like the above, but I don't know where to place the NumberDigits option to make the schema work.
 
¤ For instance,
¤
¤ First Name Amount Paid
¤ John 2500
¤ Max 2500
¤ Sally 2500
¤ Josh 2550
¤
¤ The amount paid column should have a decimal place of 2.
¤ So the amount would actually be 25.00 dollars, etc.
¤
¤ Here's my schema file:
¤
¤ [Fees.txt]
¤ ColNameHeader=False
¤ Format=FixedLength
¤ MaxScanRows=0
¤ CharacterSet=OEM
¤ Col1="FNAME" Char Width 10
¤ Col2="FeeAmount" Decimal Width 5,2
¤
¤ Col2 doesn't work if it looks like the above, but I don't know where to place the NumberDigits option to make the schema work.

Since the file does not contain the formatting character (decimal point) for your currency column I
don't believe the driver will be able to determine the appropriate format.

You will have to use a formatting function (such as Format when importing to Access) in your SQL
statement.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Forgive me, I'm very new to .Net and Microsoft programming. It would seem more intuitive to me to use the Insert command because I'm actually adding new records to a file. I realize there is more than one way to skin a cat, but just out of curiosity could that have been used

I do appreciate all the input everyone has given and I did get it working thanks to all your (everyone) advice. Thank you

rodcha
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing Text Files Into Access 1
Is there an escape sequence for Space char? for space delimted tx 12
Reading a text file 2
saving on screen data to comma delimetted text file 0
Architecture question on parsing a large text file 21
delimited dropdownlist selectedvalue 2
writing to a text file 2
arrays of integers 5

Back
Top