Import Data into Access using VBA

0
votes

I need to frequently generate output in CSV format. This file has 25 columns. The column order needs to stay the same as the file will be used as input for an ETL process. Please note it is not possible to configure the ETL to look for the column headers.

The data provided to me is also in CSV format. The number of columns may change. It could vary between 15 to 50 columns. The column order may also change. One file may have Col A, Col B, Col C then another one may have Col B, Col A, Col D.

The input data is as below:

Headers:

"Employee","Date","Start","End","Brk","Ord","WEND","TRV","PH200","PH250","T1x5","T1x5P","T2","T2x5","SB2","AL","LL175","COM","LWOP","PERS","PHNW","WCOMP","MKUP","AS","NS","CI","NPAY","COC","KMS","LAFHA","LAFHI","MEAL","MPA","OMA","SMA","Allowances","Approval Status","Branch","Branch Cost Code","Branch Ref. No","Contract","Contract Hours Per Cycle","Detail","Detail Cost Code","Detail Ref. No","Employee Ref. No","Employment Type","Location","Location Cost Code","Location Ref. No","Max Hours Per Period","Pay Group","Pay Level","Role","Role Cost Code","Roster","Shift","Timesheet Comments","Total Bill","Total Cost","Total Hours","Work Type"

Data:

"Smith, John","04/11/2017","12:00","05:00","",10.00,"","","",,"",,"",,"",,"",,"",,"",,3.00,7.00,"",,"",,"",1.00,"",,"",,"MEAL","Approved","Melbourne","","","Admin Officer","70.00","JX526","","1469","948","AT","Melbourne","633","","70.00","","Base","Admin Officer Level 1","7847000","1900-0500","DS","","0.00","351.95","10.00",""

As table:

| Employee      | Date          | Start     | End       | Brk   | Ord       | WEND  | TRV   | PH200     | PH250     | T1x5  | T1x5P     | T2    | T2x5  | SB2   | AL    | LL175     | COM   | LWOP  | PERS  | PHNW  | WCOMP     | MKUP  | AS    | NS    | CI    | NPAY  | COC   | KMS   | LAFHA     | LAFHI     | MEAL  | MPA   | OMA   | SMA   | Allowances    | Approval Status   | Branch        | Branch Cost Code  | Branch Ref. No    | Contract          | Contract Hours Per Cycle  | Detail    | Detail Cost Code  | Detail Ref. No    | Employee Ref. No  | Employment Type   | Location      | Location Cost Code    | Location Ref. No  | Max Hours Per Period  | Pay Group     | Pay Level     | Role                      | Role Cost Code    | Roster        | Shift     | Timesheet Comments    | Total Bill    | Total Cost    | Total Hours   | Work Type     |
|-------------  |------------   |-------    |-------    |-----  |-------    |------ |-----  |-------    |-------    |------ |-------    |----   |------ |-----  |----   |-------    |-----  |------ |------ |------ |-------    |------ |------ |------ |----   |------ |-----  |-----  |-------    |-------    |------ |-----  |-----  |-----  |------------   |-----------------  |-----------    |------------------ |----------------   |---------------    |-------------------------- |--------   |------------------ |----------------   |------------------ |-----------------  |-----------    |--------------------   |------------------ |---------------------- |-----------    |-----------    |-----------------------    |----------------   |-----------    |-------    |--------------------   |------------   |------------   |-------------  |-----------    |
| Smith, John   | 04/11/2017    | 12:00     | 05:00     |       | 10.00     |       |       |           |           |       |           |       |       |       |       |           |       |       |       |       |           |       | 3.00  | 7.00  |       |       |       |       |           | 1.00      |       |       |       |       | MEAL          | Approved          | Melbourne     |                   |                   | Admin Officer     | 70.00                     | JX526     |                   | 1469              | 948               | AT                | Melbourne     | 633                   |                   | 70.00                 |               | Base          | Admin Officer Level 1     | 7847000           | 1900-0500     | DS        |                       | 0.00          | 351.95        | 10.00         |               |

Since I am unable to find any off the shelves solution, I am trying to create a little tool using Access VBA to do just that.

At the import end, I have tried the 2 standard methods:

1. DoCmd.TransferText 
2. CurrentDb.Execute "INSERT INTO " & TableName & " SELECT * FROM " _
     & "[TEXT;FMT=Delimited;HDR=YES;database=" & FolderOnly & "].[" & FileOnly & "]")

Both did not work very well. Numbers are rounded to the nearest decimal. I found no way to force import data as text. So now I am generating the SQL so I have complete control over the data type. Using File System Object, I can read the file. The first step is to loop through and generate the CREATE TABLE script. The VBA Split function works very well with comma as the delimiter:

While Not objTextStream.AtEndOfStream
    strLine = objTextStream.ReadLine
    'regex.pattern =
    If objTextStream.line = 2 And Len(strLine) > 0 Then
        strSQL = "CREATE TABLE " & TableName & " ("
        header = Split(strLine, ",")

        For i = LBound(header) To UBound(header)
            strSQL = strSQL & "[" & Replace(Replace(header(i), Chr(34), ""), ".", "") & "] TEXT(255)"
            headerLine = headerLine & "[" & Replace(Replace(header(i), Chr(34), ""), ".", "") & "]"

            If i <> UBound(header) Then
                strSQL = strSQL & ","
                headerLine = headerLine & ","
            End If
        Next i

        strSQL = strSQL & ")"
        'Debug.Print strSql
        DBEngine(0)(0).Execute strSQL
    End If
Wend

The second step is to generate the INSERT statement. Something like the below:

While Not objTextStream.AtEndOfStream
    If objTextStream.line > 2 And Len(strLine) > 0 Then
        strSQL = "INSERT INTO " & TableName & " (" & headerLine & ") VALUES ("
        line = Split(strLine, """,""") 'Regex??

        For i = LBound(line) To UBound(line)
            If Nz(line(i)) <> "" Then
                strSQL = strSQL & "'" & Replace(Replace(line(i), Chr(34), ""), "'", "''") & "'"
            Else
                strSQL = strSQL & "''"
            End If
            If i <> UBound(line) Then strSQL = strSQL & ","
        Next i
        strSQL = strSQL & ")"
        'Debug.Print strSQL
        CurrentDb.Execute strSQL
    End If
Wend

I got stuck here as I cant use the Split function with comma as the delimiter. Some fields such as Employee contains a comma as the name is output in Family_Name, First_Name format. I thought about Regex but not sure how to use that in VBA. Anyone can suggest a solution please?

2
regexvbacsvms-accessimport
stackoverflow.com/questions/41092379/… - Tim Williams
The import file must not have varying columns. - Wolfgang Kais
If your code was able to use a dynamic import scheme to import the data into a table with a varying set of columns, how do you expect to create a CSV file with a fix column set out of that? - Wolfgang Kais
@WolfgangKais importing varying columns is the whole idea here. Once the data is in Access with the correct format, it is actually straightforward to export. There are several ways, one of them is to use DoCmd.TransferText acExportDelim on a SELECT query that display the data in the right order. The SELECT query can also add dummy data for any missing columns - Huy Nguyen
So there is a "complete set of columns"? Why can't your supplier stick to that specification? If I were you, I would rather quit his contract instead of tweaking the tweaks and creating a solution that must dynamically handle his dynamic output by dynamically changing objects that are designed to be based on static specifications. - Wolfgang Kais

2 Answers

0
votes

I highly recommend you fix up one of the standard calls to suit your needs instead of doing everything manual.

Using the ISAM text driver is the most promising one imo.

You should increase MaxScanRows to a number large enough to scan a large enough section that it would encounter a number with a high amount of decimals, or to 0 to make Access scan the entire file. Rounding occurs when the scanned rows contain a lower amount of decimals than the value being imported.

For initializing the text driver, see this MSDN page. Note that older sources might refer to the schema.ini file, but this file no longer exists, settings are managed in the registry for current versions of Access.

0
votes

This is how I did it:

Generate the Schema.ini file manually:

Dim objFS, objTextStream
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTextStream = objFS.CreateTextFile(GetFolderFromPath(FileName) & "Schema.ini", True)

objTextStream.WriteLine "[" & GetFileNameFromPath(FileName) & "]"
objTextStream.WriteLine "ColNameHeader = True"
objTextStream.WriteLine "Format = CSVDelimited"
objTextStream.WriteLine "MaxScanRows = 0"
objTextStream.WriteLine "CharacterSet = OEM"
objTextStream.Close

Set objTextStream = Nothing
Set objFS = Nothing

Then add the text file as a linked table:

If IsTableExisted(TableName) Then DoCmd.DeleteObject acTable, TableName
Dim tdfNew As TableDef
Set tdfNew = CurrentDb.CreateTableDef(TableName)
tdfNew.Connect = "Text;DATABASE=" & GetFolderFromPath(FileName) & _
    ";TABLE=" & GetFileNameFromPath(FileName) & ";"
tdfNew.SourceTableName = GetFileNameFromPath(FileName)
CurrentDb.TableDefs.Append tdfNew