importing a text file

[rodchar]

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

[William Ryan eMVP]

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/
"rodchar" <an*******@discussions.microsoft.com> wrote in message
news:BC**********************************@microsof t.com...

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

[Paul Clement]

On Fri, 28 May 2004 09:21:06 -0700, "rodchar" <an*******@discussions.microsoft.com> wrote:

¤ 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/de...a_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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[rodchar]

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

[Cor Ligthert]

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

[rodchar]

What if it's a fixed-length file, how would I import that into a database
thanks in advance
rodchar

[Paul Clement]

On Fri, 28 May 2004 14:16:05 -0700, "rodchar" <an*******@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[rodchar]

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

[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 :(

[Cor Ligthert]

Hi Rodchar,

I never tried this, however maybe you can (there is written ODBC, I nowhere
found for OleDB so I would try this when I was you).

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

I hope this helps?

Cor

[Paul Clement]

On Tue, 1 Jun 2004 16:11:06 -0700, "rodchar" <an*******@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Wed, 2 Jun 2004 09:51:13 +0200, "Cor Ligthert" <no**********@planet.nl> wrote:

¤ Hi Rodchar,
¤
¤ I never tried this, however maybe you can (there is written ODBC, I nowhere
¤ found for OleDB so I would try this when I was you).
¤
¤ http://msdn.microsoft.com/library/de...a_ini_file.asp
¤
¤ I hope this helps?

This was already posted (on 5/28) and it's what he's currently looking at. The mechanism is the same
whether you use ODBC or OLEDB.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Cor Ligthert]

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

This was already posted (on 5/28) and it's what he's currently looking at. The mechanism is the same whether you use ODBC or OLEDB.

[rodchar]

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.

[Paul Clement]

On Wed, 2 Jun 2004 15:26:06 -0700, "rodchar" <an*******@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[rodchar]

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