StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The easiest way to import CSV into Access is to use <a href="http://msdn.microsoft.com/en-us/library/office/bb214141%28v=office.12%29.aspx" rel="nofollow">DoCmd.TransferText</a> from within an Access application session. </p> <p>But you want to use Excel VBA. In that case, if your MS Office installation includes Access (MSACCESS.EXE), you can use Excel VBA to automate Access and still make use of <code>DoCmd.TransferText</code> for easy CSV import.</p> <p>I tested this module in Excel 2007. It creates the <code>GSPC</code> table and stores the data from <code>table.csv</code> in that table. If the table already exists, <code>TransferText</code> will simply append the CSV data. You can execute <code>DELETE FROM GSPC</code> before running <code>TransferText</code> so the table will contain only the latest CSV data.</p> <p>This may look a bit intimidating considering you said you're a noob. However much of the following is comments I added to guide you. The actual <em>"guts"</em> of that procedure is fairly short and simple.</p> <pre class="lang-vb prettyprint-override"><code>Option Explicit Public Sub ImportCsvToAccess() Const cstrCsvFile As String = "table.csv" Const cstrDbFile As String = "tblImport.accdb" Const cstrTable As String = "GSPC" Dim strFolder As String '* early binding *' ' requires reference to Microsoft Access <version> Object Library 'Dim objAccess As Access.Application 'Set objAccess = New Access.Application '* late binding *' ' no reference required Dim objAccess As Object Set objAccess = CreateObject("Access.Application") ' this is useful during development ' in production, you may prefer to hide it (Visible = False) objAccess.Visible = True strFolder = ActiveWorkbook.Path & Chr(92) 'Debug.Print strFolder objAccess.OpenCurrentDatabase strFolder & cstrDbFile, _ Exclusive:=True '* early binding *' 'objAccess.DoCmd.TransferText _ ' TransferType:=acImportDelim, _ ' TableName:=cstrTable, _ ' Filename:=strFolder & cstrCsvFile, _ ' HasFieldNames:=True '* late binding *' ' acImportDelim = 0 objAccess.DoCmd.TransferText _ TransferType:=0, _ TableName:=cstrTable, _ Filename:=strFolder & cstrCsvFile, _ HasFieldNames:=True objAccess.Quit Set objAccess = Nothing End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload