CSV import macro - Very Confused

[bposaner]

Very confused access newb and need help!!

I'm trying to write a macro to import csv files into a mdb.
I've searched the forum and found some examples, but im truly lost.

A sample of the csv file is below.
So currently I manually import the data with the following steps
1. 1st row contains field names.
2. skip first column (as its all blanks)
3. import all other cols, but with last column I import with a MDY format.
4. import into existing DB

All help would be great
Many Thanks

,ÿþComputerName,ObjectName,PerformanceCounterName,PerformanceInstanceName,SampledValue,TimeSampled
,LIV-CER-WA-P01,LogicalDisk,% Disk Time,_Total,2.24748899462693,07-01-2007 00:05
,LIV-CER-WA-P01,LogicalDisk,% Disk Time,_Total,4.15209282684479,07-01-2007 00:09
,LIV-CER-WA-P01,LogicalDisk,% Disk Time,_Total,4.08122189087369,07-01-2007 00:15
,LIV-CER-WA-P01,LogicalDisk,% Disk Time,_Total,2.59458327199961,07-01-2007 00:20
,LIV-CER-WA-P01,LogicalDisk,% Disk Time,_Total,1.66944401777505,07-01-2007 00:24
,LIV-CER-WA-P01,LogicalDisk,% Disk Time,_Total,2.04877977885725,07-01-2007 00:29

There is about 200,000 rows in each file!

 

[wongm003]

Hi bposaner,

First off you should create a import file specification...
While you are in the Import Text Wizard, importing the CSV file make sure the the delimited button is checked and press next.
Check the "First Row Contains Field Names" and hit the Advanced button (you might get a warning that the first row contains invalid names hit OK).
Check Skip on Field1 and make sure the Data Types are correct.
Click the "Save As" button and make sure you remember this.
You can cancel from here as long as you have saved the import spec.

Then there are a couple ways to do it.
1) Macro - use the action > TransferText using the Specification Name
*** you will need to specify the File Name***

2) Macro with a custom function...
create a module with the following funciton

Public Function importCSVFiles() As Boolean
'make sure reference "Microsoft Office xx Object Library" in Tools > References
On Error GoTo hndl_err
    Dim fd As FileDialog

    Const strTblName As String = "Table Name"         'Change as needed
    Const strImportSpec As String = "Specification Name"    'Change as needed

    'Create a FileDialog object as a File Picker dialog.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)


    With fd
        .Filters.Add "CSV Files", "*.csv"
        .AllowMultiSelect = True
        
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'If the user presses the action button...
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
           
                DoCmd.TransferText acImportDelim, strImportSpec, strTblName, vrtSelectedItem, True

            Next vrtSelectedItem
        'If the user presses Cancel...
        Else
        End If
    End With

    'assume all went well if got here
    importCSVFiles = True

normal_exit:
    'Set the object variable to Nothing.
    Set fd = Nothing
    Exit Function
    
hndl_err:
    importCSVFiles = False
    Resume normal_exit
   
End Function

Then from the module use the Action > RunCode with the function Name > importCSVFiles

The function above will allow the user to select the csv files and import them into the table...