Loading data into an Access Table from CSV or XML

[mosscliffe]

I have lots of old data files, which I am adding to an Access Database.

I read somewhere, that you can select directly on a CSV file or XML by
changing the Connection String - Extended Property - or I could have
been dreaming.

So to save a lot of coding and before I break everything, is it
possible to do something like the following

COPY CSV into an Access Table

INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
fld4 from mycsv.csv

COPY XML into an Access Table

INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
fld4 from myxml.xml

Using OleDbCommand.executeNonQuery

If it is possible, some sample code would be very helpful

Thanks for any help - Richard

 

[tomb]

I have lots of old data files, which I am adding to an Access Database.

I read somewhere, that you can select directly on a CSV file or XML by
changing the Connection String - Extended Property - or I could have
been dreaming.

So to save a lot of coding and before I break everything, is it
possible to do something like the following

COPY CSV into an Access Table

INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
fld4 from mycsv.csv

COPY XML into an Access Table

INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
fld4 from myxml.xml

Using OleDbCommand.executeNonQuery

If it is possible, some sample code would be very helpful

Thanks for any help - Richard

If the goal is simply to get the data into the Access file, you can do
that directly from Access. Much less convoluted.

T

 

[mosscliffe]

Thanks for the reply.

The Access DataBase, will be on a webserver, so it will require some
coding to get the Uploaded file into the Database Table.

Any further thoughts ?

 

[Paul Clement]

¤ I have lots of old data files, which I am adding to an Access Database.
¤
¤ I read somewhere, that you can select directly on a CSV file or XML by
¤ changing the Connection String - Extended Property - or I could have
¤ been dreaming.
¤
¤ So to save a lot of coding and before I break everything, is it
¤ possible to do something like the following
¤
¤ COPY CSV into an Access Table
¤
¤ INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
¤ fld4 from mycsv.csv
¤
¤ COPY XML into an Access Table
¤
¤ INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3,
¤ fld4 from myxml.xml
¤
¤ Using OleDbCommand.executeNonQuery
¤
¤ If it is possible, some sample code would be very helpful
¤

The following might help:

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=C:\Test Files\db1 XP.mdb")

AccessConn.Open()

'New table
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ReportFile] FROM
[Text;DATABASE=C:\Documents and Settings\nfisppc\My Documents\My Database\Text].[ReportFile.txt]",
AccessConn)
'Existing table
'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tbl1] (F1, F2, F3,
F4, F5) SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=d:\My
Documents\TextFiles;].[ReportFile.txt]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

XML is a different story. You typically need to perform an XML transform, but it may depend upon how
complex your XML files are.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Is it possible to bulk insert records into access database from a DataTable
instead of executing "insert into [tbl] ([x], [y], [z]) values (1,2,3)" for
each row in the DataTable?

 

[Paul Clement]

¤ Is it possible to bulk insert records into access database from a DataTable
¤ instead of executing "insert into [tbl] ([x], [y], [z]) values (1,2,3)" for
¤ each row in the DataTable?
¤

There is no Bulk Insert from a DataTable into Microsoft Access. You would have to perform the insert
row by row, which would be incredibly slow.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

So there are no ways to import data from XML to access database other than
row by row insert?

 

[Brendan Reynolds]

Well, recent versions of Access have an 'ImportXML' method, but if you mean
'from a .NET app' then I think the answer is probably 'no, you'd have to do
the row-by-row insert'.

--
Brendan Reynolds
Access MVP

So there are no ways to import data from XML to access database other than
row by row insert?

On Tue, 6 Jun 2006 07:23:02 -0700, Sanjib Biswas

¤ Is it possible to bulk insert records into access database from a
DataTable
¤ instead of executing "insert into [tbl] ([x], [y], [z]) values (1,2,3)"
for
¤ each row in the DataTable?
¤

There is no Bulk Insert from a DataTable into Microsoft Access. You would
have to perform the insert
row by row, which would be incredibly slow.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Paul Clement]

¤ So there are no ways to import data from XML to access database other than
¤ row by row insert?
¤

Not that I am aware of, not from a DataSet. The only programmatic batch method I'm aware of requires
Access automation (ImportXML).


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Cor Ligthert [MVP]]

Sanjib,

It is worse than every other advice you had in this thread, but to show that
it is not impossible.

http://www.vb-tips.com/default.aspx?ID=5bacb522-2c4e-4dad-abf4-851df8324532

I hope this helps,

Cor

So there are no ways to import data from XML to access database other than
row by row insert?

On Tue, 6 Jun 2006 07:23:02 -0700, Sanjib Biswas

¤ Is it possible to bulk insert records into access database from a
DataTable
¤ instead of executing "insert into [tbl] ([x], [y], [z]) values (1,2,3)"
for
¤ each row in the DataTable?
¤

There is no Bulk Insert from a DataTable into Microsoft Access. You would
have to perform the insert
row by row, which would be incredibly slow.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Thank you all for your valuable advice. But the application I am developing
should be capable of transforming an access database to XML and back to
access database without losing any information from the original database.
Moreover the access database I am dealing with has primary keys, indexes etc
and as you know WriteSchema does not write index information other than field
properties. So I have defined my own index schema and write both schema and
the data associated with the each index in the same xml file. In the XML to
database conversion, I first create an empty access database and then loop
through the dataset and create tables, indexes and finally fill-in the data
row-by-row. I think I have to live with it even if it’s slow.

I would appreciate if anyone of you could give me some light on the below
issue:
I have got an OLE Object field in the database and after converting it to
XML, see a huge string data for that OLE Object field. How do I convert that
string data back to System.Byte[] datatype? I was hoping that Convert class
could be used to convert the string to OleObject datatype but there are no
methods available in that class.

Sanjib

Sanjib,

It is worse than every other advice you had in this thread, but to show that
it is not impossible.

http://www.vb-tips.com/default.aspx?ID=5bacb522-2c4e-4dad-abf4-851df8324532

I hope this helps,

Cor

So there are no ways to import data from XML to access database other than
row by row insert?

On Tue, 6 Jun 2006 07:23:02 -0700, Sanjib Biswas

¤ Is it possible to bulk insert records into access database from a
DataTable
¤ instead of executing "insert into [tbl] ([x], [y], [z]) values (1,2,3)"
for
¤ each row in the DataTable?
¤

There is no Bulk Insert from a DataTable into Microsoft Access. You would
have to perform the insert
row by row, which would be incredibly slow.


Paul
~~~~
Microsoft MVP (Visual Basic)