TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech Support Archives > Microsoft > Microsoft Windows Powershell

Using Import-CSV to post entries in Access DB

Microsoft Windows Powershell

 
 
Thread Tools Display Modes
Unread 05-11-2007, 04:06 PM   #1
Brandon Shell [MVP]
Guest
 
Posts: n/a
Using Import-CSV to post entries in Access DB

I try to help out as much as I can on Experts-Exchange, but I don't do much
with Access. Anyone that can help I would appreciate it.

Question:
I'm attempting to import a CSV file into the Access Database that already
exists. In Powershell, the import-csv will quickly read in the data and
display it, but how can I take that read data and put it into Access?
Preferably checking to see if it already exists. The database resides on a
computer without Access.

--
Brandon Shell [MVP]
----------------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject
--------------------------------------

 
Unread 05-11-2007, 04:06 PM   #2
/\/\o\/\/ [MVP]
Guest
 
Posts: n/a
RE: Using Import-CSV to post entries in Access DB

Brandon,

you can use ADO to connect to access :

see for an example :
http://mow001.blogspot.com/2006/02/s...h-answers.html

you can use the out-datatable from here to convert the CSV to a dataset :

http://mow001.blogspot.com/2006/05/p...pdate-and.html

then you can use a dataadapter to write back the datatable :
here is an helperfunction I mostly use for this :

function get-DataAdapter ($SelectCMD){
$da = new-object System.Data.SqlClient.SQLDataAdapter($SelectCMD)
$scb = New-Object System.Data.SqlClient.SqlCommandBuilder($da)
$da.UpdateCommand = $scb.GetUpdateCommand()
$da.InsertCommand = $scb.GetInsertCommand()
$da.DeleteCommand = $scb.GetDeleteCommand()
$da
}

now you can write back by using this command :

$da.update($Table)

h.t.h.

Greetings /\/\o\/\/
http://thePowerShellGuy.com

"Brandon Shell [MVP]" wrote:

> I try to help out as much as I can on Experts-Exchange, but I don't do much
> with Access. Anyone that can help I would appreciate it.
>
> Question:
> I'm attempting to import a CSV file into the Access Database that already
> exists. In Powershell, the import-csv will quickly read in the data and
> display it, but how can I take that read data and put it into Access?
> Preferably checking to see if it already exists. The database resides on a
> computer without Access.
>
> --
> Brandon Shell [MVP]
> ----------------------
> Blog: http://www.bsonposh.com/
> PSH Scripts Project: www.codeplex.com/psobject
> --------------------------------------
>
>

 
Unread 05-11-2007, 04:07 PM   #3
Marco Shaw [MVP]
Guest
 
Posts: n/a
Re: Using Import-CSV to post entries in Access DB

Brandon Shell [MVP] wrote:
> I try to help out as much as I can on Experts-Exchange, but I don't do
> much with Access. Anyone that can help I would appreciate it.
>
> Question:
> I'm attempting to import a CSV file into the Access Database that
> already exists. In Powershell, the import-csv will quickly read in the
> data and display it, but how can I take that read data and put it into
> Access? Preferably checking to see if it already exists. The database
> resides on a computer without Access.
>


The hard part is loading the data from Access:
http://www.microsoft.com/technet/scr...6/hey1002.mspx

Either once loaded or as records are being read, one could then compare
one or all of the values from the original CSV file.

One would likely want to create a 3rd file here to write out the
results, unless one reads in all of the Access data, closes the file,
then reopens it to append or whatever.

(Sorry, short on time right now to provide a full solution.)

Marco


--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com
 
Unread 05-11-2007, 04:07 PM   #4
Brandon Shell [MVP]
Guest
 
Posts: n/a
RE: Using Import-CSV to post entries in Access DB

Thanks SIR!

Brandon Shell
---------------
Blog: http://www.bsonposh.com/
PSH Scripts Project: www.codeplex.com/psobject

o> you can use ADO to connect to access :
o> see for an example :
o> http://mow001.blogspot.com/2006/02/s...-day-msh-answe
o> rs.html
o> you can use the out-datatable from here to convert the CSV to a
o> dataset :
o>
o> http://mow001.blogspot.com/2006/05/p...rid-update-and
o> .html
o>
o> then you can use a dataadapter to write back the datatable : here is
o> an helperfunction I mostly use for this :
o>
o> function get-DataAdapter ($SelectCMD){
o>
o> $da = new-object System.Data.SqlClient.SQLDataAdapter($SelectCMD)
o>
o> $scb = New-Object System.Data.SqlClient.SqlCommandBuilder($da)
o>
o> $da.UpdateCommand = $scb.GetUpdateCommand()
o>
o> $da.InsertCommand = $scb.GetInsertCommand()
o>
o> $da.DeleteCommand = $scb.GetDeleteCommand()
o>
o> $da
o>
o> }
o>
o> now you can write back by using this command :
o> $da.update($Table)


 
Unread 05-11-2007, 04:07 PM   #5
RichS
Guest
 
Posts: n/a
RE: Using Import-CSV to post entries in Access DB

Unless I am missing something the sqlclient classes are only usable with SQL
Server

You need to to use System.Data.OleDb.OleDbCommand

etc

is the access table pre created and does it have a field designated as a
unique key?
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"/\/\o\/\/ [MVP]" wrote:

> Brandon,
>
> you can use ADO to connect to access :
>
> see for an example :
> http://mow001.blogspot.com/2006/02/s...h-answers.html
>
> you can use the out-datatable from here to convert the CSV to a dataset :
>
> http://mow001.blogspot.com/2006/05/p...pdate-and.html
>
> then you can use a dataadapter to write back the datatable :
> here is an helperfunction I mostly use for this :
>
> function get-DataAdapter ($SelectCMD){
> $da = new-object System.Data.SqlClient.SQLDataAdapter($SelectCMD)
> $scb = New-Object System.Data.SqlClient.SqlCommandBuilder($da)
> $da.UpdateCommand = $scb.GetUpdateCommand()
> $da.InsertCommand = $scb.GetInsertCommand()
> $da.DeleteCommand = $scb.GetDeleteCommand()
> $da
> }
>
> now you can write back by using this command :
>
> $da.update($Table)
>
> h.t.h.
>
> Greetings /\/\o\/\/
> http://thePowerShellGuy.com
>
> "Brandon Shell [MVP]" wrote:
>
> > I try to help out as much as I can on Experts-Exchange, but I don't do much
> > with Access. Anyone that can help I would appreciate it.
> >
> > Question:
> > I'm attempting to import a CSV file into the Access Database that already
> > exists. In Powershell, the import-csv will quickly read in the data and
> > display it, but how can I take that read data and put it into Access?
> > Preferably checking to see if it already exists. The database resides on a
> > computer without Access.
> >
> > --
> > Brandon Shell [MVP]
> > ----------------------
> > Blog: http://www.bsonposh.com/
> > PSH Scripts Project: www.codeplex.com/psobject
> > --------------------------------------
> >
> >

 
Unread 05-11-2007, 04:07 PM   #6
/\/\o\/\/ [MVP]
Guest
 
Posts: n/a
Re: Using Import-CSV to post entries in Access DB

LS,

Richard, this is correct , the Access post uses oleDB,
the helperfunction needs a bit adaption from SQL to Access

some combining of posts is needed, but the dataset makes it easy to work
with it.

some more examples using oldDB (but then CSV ) to manipulate the data before
saving it you can find here :

http://mow001.blogspot.com/2006/04/m...-more-csv.html

I have no time to combine them myself at the moment, but when there are
problems let me know I will take a look at it later when I have time/

Greetings /\/\o\/\/

"Marco Shaw [MVP]" wrote:

> Brandon Shell [MVP] wrote:
> > I try to help out as much as I can on Experts-Exchange, but I don't do
> > much with Access. Anyone that can help I would appreciate it.
> >
> > Question:
> > I'm attempting to import a CSV file into the Access Database that
> > already exists. In Powershell, the import-csv will quickly read in the
> > data and display it, but how can I take that read data and put it into
> > Access? Preferably checking to see if it already exists. The database
> > resides on a computer without Access.
> >

>
> The hard part is loading the data from Access:
> http://www.microsoft.com/technet/scr...6/hey1002.mspx
>
> Either once loaded or as records are being read, one could then compare
> one or all of the values from the original CSV file.
>
> One would likely want to create a 3rd file here to write out the
> results, unless one reads in all of the Access data, closes the file,
> then reopens it to append or whatever.
>
> (Sorry, short on time right now to provide a full solution.)
>
> Marco
>
>
> --
> Microsoft MVP - Windows PowerShell
> http://www.microsoft.com/mvp
>
> PowerGadgets MVP
> http://www.powergadgets.com/mvp
>
> Blog:
> http://marcoshaw.blogspot.com
>

 
Unread 05-11-2007, 04:07 PM   #7
RichS
Guest
 
Posts: n/a
RE: Using Import-CSV to post entries in Access DB

lets assume a database test.mdb with a table called Table1 with 3 fields
called col1, col2, col3 and that the csv has same column names

to insert values will be something like this


$conn = New-Object
System.Data.OleDb.OleDbConnection("Provider=Micros oft.Jet.OLEDB.4.0; Data
Source=test.mdb")
$conn.Open()

import-csv file.txt | foreach {

$cmd = $conn.CreateCommand()

$cmd.CommandText ="INSERT Table1 VALUES ($_.col1, $_.col2, $_.col3)"
$cmd.ExecuteNonQuery()

}


$conn.Close()



--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Brandon Shell [MVP]" wrote:

> I try to help out as much as I can on Experts-Exchange, but I don't do much
> with Access. Anyone that can help I would appreciate it.
>
> Question:
> I'm attempting to import a CSV file into the Access Database that already
> exists. In Powershell, the import-csv will quickly read in the data and
> display it, but how can I take that read data and put it into Access?
> Preferably checking to see if it already exists. The database resides on a
> computer without Access.
>
> --
> Brandon Shell [MVP]
> ----------------------
> Blog: http://www.bsonposh.com/
> PSH Scripts Project: www.codeplex.com/psobject
> --------------------------------------
>
>

 
Unread 21-11-2007, 07:02 PM   #8
Flowering Weeds
Guest
 
Posts: n/a
Re: Using Import-CSV to post entries in Access DB


"Brandon Shell [MVP]"

>
> Question:
> I'm attempting to import a CSV file into
> the Access Database that already exists.


Almost all of PowerShell's data parsing
can be done via
Microsoft's data parser,
Log Parser 2.2
(where one writes it once and
calls it by almost any Windows language)!

LogParser.exe "SELECT whatever
INTO TheTable FROM file.csv "
-i:csv -CsvParams -o:sql -DataBaseParams

Just the "log parsing" way!






 
 

Thread Tools
Display Modes



< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 01:02 AM.


vBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO
Copyright © 2005-2013, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional