Solved

Perl Script CSV to Access Database

Posted on 2010-11-19
2
678 Views
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?

0
Comment
Question by:kyle972
2 Comments
 
LVL 2

Expert Comment

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

Dim objConn

Dim Cells(30,1000)
ReadCsv("c:\file.csv")

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
Next

objConn.Close
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)
        Wend
        RowCount = Row
    Wend
    f.Close
    Set f = Nothing
    Set fso = Nothing
End Sub

Open in new window

0
 
LVL 16

Accepted Solution

by:
jmatix earned 500 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";

while($db->FetchRow())
{
 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

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

860 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