Solved

Perl Script CSV to Access Database

Posted on 2010-11-19
2
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

696 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