Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

How can I import text to Access

by Broggy69 (Novice)
on May 28, 2015 at 17:12 UTC ( #1128165=perlquestion: print w/replies, xml ) Need Help??
Broggy69 has asked for the wisdom of the Perl Monks concerning the following question:

I have programmed in VB.Net and trying to learn PERL. I have read vari +ous methods searching with google, but none work. If it helps below i +s the code I would like to convert to PERL if possible. Dim sCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" Dim conp As String = "SELECT * INTO [Input] FROM [Text;DATABAS +E=" Dim WF As String = "W:\Glenn-123456\Test\" Dim Conn_1 As New OleDbConnection(sCon & WF & "\VDD\Data\DevCo +mm1.mdb") Dim Comm_1 As New OleDbCommand(conp & WF & "\VDD\Data\;HDR=No] +.[DevComm1.txt]", Conn_1) Dim dSysDate As String = "" Conn_1.Open() Comm_1.ExecuteNonQuery() Conn_1.Close()

Replies are listed 'Best First'.
Re: How can I import text to Access
by stevieb (Abbot) on May 28, 2015 at 17:26 UTC

    Welcome to PerlMonks!

    Please start here: How do I post a question effectively.

    Note that this is not a code-writing service. We like to see you've made at least some attempt yourself before we blindly translate your code for you.


Re: How can I import text to Access
by Broggy69 (Novice) on May 28, 2015 at 17:49 UTC
    Here is what I am trying in PERL #!/usr/bin/perl use strict;use warnings; use Win32::OLE; my $DBFile = qw( W:\Glenn-123456\Test\DevComm1.mdb ); # #Choose appropriate version of Jet for your system my $Jet = Win32::OLE->CreateObject('DAO.DBEngine.36') or die "Can't +create Jet database engine."; my $DB = $Jet->OpenDatabase( $DBFile ); my $SQLquery = "SELECT * INTO [Input] FROM [Text;DATABASE=W:\Glenn-123 +456\Test\VDD\Data\;HDR=No].[DevComm1.txt]"; $DB->Execute($SQLquery, 128); #128=DBFailOnError I get 4 error messages that says unrecognized escape /g /t /v /d at li +ne 11 and one can't create jet database engine line 8 Trying again.
      I also tried this one just now, and I get an error saying my DevComm1. +mdb file is not found. It does exist in the folder location I specifi +ed. #!perl use strict; use warnings; use DBI; print 'bits: ' . (8 * (length pack 'P', -1)) . "\n\n"; my $DBFile = q(W:\Glenn-123456\Test\DevComm1.mdb); my $dbh = DBI->connect("dbi:ODBC:Driver={Microsoft Access Driver (*.md +b)};DBQ=$DBFile",'','') or die("cannot connect to DB"); my $SQLquery = "SELECT * INTO [Input] FROM [Text;DATABASE=W:\Glenn-123 +456\Test\\VDD\Data\;HDR=No].[DevComm1.txt]"; my $sth = $dbh->prepare($SQLquery); my $rc = $sth->execute; #Trying again

        Hi, Broggy69. Did you miss or simply decide to ignore the advice given to you by stevieb?

        Looking at your nodes on screen, don't you realise that they are an unreadable mess?

        Please read How do I post a question effectively?, and then go back and edit all your posts.

        Update: The OP seems to have taken our advice and formatted their posts, albeit not very well... but at least they're readable now!

        It may be Perl is having trouble with your Windows-style path. Try:

        my $DBFile = 'W:/Glenn-123456/Test/DevComm1.mdb';
        Dum Spiro Spero
Re: How can I import text to Access
by soonix (Monsignor) on Jun 01, 2015 at 11:18 UTC

      While this is an old post I did manage to get this figured out. Here is my code.

      my $filename = 'C:/GBowl/strawberry32/Scripts/ccname.txt'; my $db_file = 'C:/GBowl/strawberry32/Scripts/Hello.accdb'; my $dbh = DBI->connect( 'dbi:ADO:Provider=Microsoft.ACE.OLEDB.12.0;Data Source='.$db_file, ) or die $DBI::errstr; print $filelog "Connected to ms-access database\n"; #prepare and execute SQL statement $Export = $dbh->prepare( 'SELECT Company_Code, Name INTO [Text;HDR=No;DATABASE=C:\GBowl\str +awberry32\Scripts].[Plants.txt] FROM Plants ORDER BY Company_Code'); $Export->execute || die "Could not execute SQL statement ... maybe invalid?";

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1128165]
Approved by GotToBTru
and dust plays in a shaft of sunlight...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (2)
As of 2018-04-22 01:39 GMT
Find Nodes?
    Voting Booth?