469,889 Members | 1,367 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,889 developers. It's quick & easy.

importing a text file

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
Jul 21 '05 #1
15 1751
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

Jul 21 '05 #2
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)
Jul 21 '05 #3
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
Jul 21 '05 #4
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
Jul 21 '05 #5
What if it's a fixed-length file, how would I import that into a database
thanks in advance
rodchar
Jul 21 '05 #6
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)
Jul 21 '05 #7
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
Jul 21 '05 #8
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 :(
Jul 21 '05 #9
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
Jul 21 '05 #10
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)
Jul 21 '05 #11
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)
Jul 21 '05 #12
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.

Jul 21 '05 #13
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.
Jul 21 '05 #14
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)
Jul 21 '05 #15
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

Jul 21 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Grim Reaper | last post: by
5 posts views Thread by hharriel | last post: by
2 posts views Thread by Debbiedo | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.