Perl Script CSV to Access Database

Posted on 2010-11-19
Medium Priority
Last Modified: 2012-05-10
I am looking for a perl script to read a CSV file into an Access Database.

Anyone have anything that would work?

Question by:kyle972

Expert Comment

ID: 34178013
Does it have to be perl?  How about this for VB script (windows)

Dim objConn

Dim Cells(30,1000)

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\MyAccessDatabase.mdb;­ "

For i = 1 to UBound(Cells)
    If Cells(1, i) <> "" Then
        'The cell isnt emtpy so lets do some work on this row...
        objConn.Execute("INSERT INTO table (column_1, column_2, column_n) VALUES ('" & cells(1,i) & "', '" & Cells(2,i) & "', '" & Cells(3,i)"');")
    End IF

Set objConn = Nothing

Sub ReadCSV(sFilename)
    Dim fso, f, line, Column, CellStart, CellEnd, Row
    Set fso = CreateObject("Scripting.FileSystem­ Object")
    Set f = fso.OpenTextFile(sFilename, 1)
    Row = 0
    While Not f.AtEndOfStream
        line = """" & Replace(f.ReadLine, ",", """,""") & """"
        Row = Row + 1
        Column = 0
        While line <> ""
            Column = Column + 1
            CellStart = 2
            CellEnd = InStr(2, line, """", vbTextCompare)
            Cells(Row, Column) = Mid(line, CellStart, CellEnd - CellStart)
            line = Mid(line, CellEnd + 2)
        RowCount = Row
    Set f = Nothing
    Set fso = Nothing
End Sub

Open in new window

LVL 16

Accepted Solution

Justin Mathews earned 2000 total points
ID: 34188392
Assuming you are in Windows environment. You need Win32::ODBC perl module installed. See an example below.
#! c:\perl\bin\perl

use Win32::ODBC;

$DriverType = "Microsoft Access Driver (*.mdb)";
$DSN = "Win32 ODBC";
$Dir = "V:\\Doc\\KB\\Tech\\ExpExch\\acc";
$DBase = "Database11.mdb";

Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $DriverType,("DSN=$DSN", "Description=Win32 ODBC Test DSN for Perl", "DBQ=$Dir\\$DBase", "DEFAULTDIR=$Dir", "UID=", "PWD=")) or die "ConfigDSN(): Could not add temporary DSN" . Win32::ODBC::Error();

$db=new Win32::ODBC($DSN) or die "couldn't ODBC $DSN because ", Win32::ODBC::Error(), "\n";

$query = "insert into table1 values (4, 'Hello', 'World')";
!$db->Sql($query) or die "couldn't do $query because ", $db->Error(), "\n";

 my %Data = $db->DataHash();
 foreach my $key(keys(%Data)){
   print $key," -> ",$Data{$key};

Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $DriverType, "DSN=$DSN") or die "ConfigDSN(): Could not remove temporary DSN because ", Win32::ODBC::Error();

Open in new window


Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Six Sigma Control Plans
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question