Importing .csv file into an Access 2010 table using VBA

jeffkwells2003
jeffkwells2003 used Ask the Experts™
on
I am having problems inputting data from .csv file into an Access 2010 temporary table.  The data is output from a web application called Clicktools. Please refer to the enclosed database file:Testinput.accdb and the function doimport() in Module1.

The enclosed file Testfile1.csv contains a single comma delimited record (Andrew Everyman), where the last field is a mixture of alpha and numeric characters. The field in the Access table tmpDDPaymentsData is defined as Text(255).

If you run the doimport() function  all required fields will be imported correctly. However if you change the file name in the doimport() code to import Testfile2 (which contains the same record mixed in with other records where the last fields are all numeric characters) the import routine will not load the last field of the Andrew Everyman record. All other records load correctly.

Thanks for your help

TestInput.accdbTestFile1.csvTestFile2.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
The problem is:

The import needs the data types involved. It either determins these by using a import specification or by looking into the first rows.

You don't use a specification, thus it looks into the first values. The majority is of the values in column 20 are integer, thus it reads them as integer. The second row is alphanumeric, thus it is not read.

The solution is to use a import specification. Run the import for your second file to via the External Data tab. Chose import into a new table.  Use the Advanced... button to get into the specification dialog. You need here to set the data types of all columns:

 Capture.PNG
Save this specification. Then you can use it later in your code as

Option Explicit
Option Compare Database

Public Sub Test()

  If ImportCsv(Application.CurrentProject.Path & "\TestFile2.csv") Then
    MsgBox "Imported file.", vbInformation + vbOKOnly
  Else
    MsgBox "Errors while importng file.", vbExclamation + vbOKOnly
  End If

End Sub

Public Function ImportCsv(ByVal CFileName As String) As Boolean

  On Local Error GoTo LocalError
  
  ImportCsv = False
  DoCmd.SetWarnings False
  DoCmd.RunSQL "DELETE * FROM tmpDDPaymentsData;"
  DoCmd.SetWarnings True
  DoCmd.TransferText acImportDelim, "TestFile2", "tmpDDPaymentsData", CFileName, False
  ImportCsv = True
  Exit Function
  
LocalError:
  DoCmd.SetWarnings True
  MsgBox "The following error occurred: " & Err.Description
  
End Function

Open in new window

Use always Option Explicit. Make it the default for new code modules in the VBA IDE under Tools\Options\Variable declaration required and add it in all existing modules.
When using functions returing their state as a boolean, then it is good style to set the default result in the first line of executed code, so that you don't need to touch it in the error handler.
And declare always the type of variables explicitly.

Author

Commented:
Thanks for this, I'll give it a go and get back to you.

Author

Commented:
Thanks a lot. that did the trick!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial