none
Importing csv files into Access 2010 RRS feed

  • Question

  • Hi All,

    I have this code that allows the user to select the excel file they want to import, can someone help me change it so that they can select and import a csv instead?

    Sub Import_table()

    Dim dlg As FileDialog ' I get a Compile Error: User-defined type not defined
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    With dlg
    .Title = "Select the Excel file to import"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls", 1
    .Filters.Add "All Files", "*.*", 2
    If .Show = -1 Then
    StrFileName = .SelectedItems(1)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Import_Table", StrFileName, True
    Else
    Exit Sub
    End If
    End With

    End Sub


    Friday, February 1, 2013 11:44 AM

Answers

  • See if it helps.

    First add reference to Microsoft Office Object Library.Then paste the below in standard module and run.

    You can finetune the TransferText arguments after this code runs succesfully.

    Sub Import_table()
    
        Dim strfilename As String
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Title = "Select the CSV file to import"
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "CSV Files", "*.csv", 1
            .Filters.Add "All Files", "*.*", 2
            If .Show = -1 Then
                strfilename = .SelectedItems(1)
                DoCmd.TransferText TransferType:=acImportDelim, _
                    TableName:="Import_Table", FileName:=strfilename
                Else
                Exit Sub
            End If
        End With
    
    End Sub



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.


    • Edited by Asadulla JavedEditor Friday, February 1, 2013 1:16 PM
    • Marked as answer by Spark_True Friday, February 8, 2013 9:07 PM
    • Unmarked as answer by Spark_True Friday, February 8, 2013 9:07 PM
    • Marked as answer by Spark_True Friday, February 8, 2013 9:08 PM
    Friday, February 1, 2013 1:15 PM
    Answerer

All replies

  • See if it helps.

    First add reference to Microsoft Office Object Library.Then paste the below in standard module and run.

    You can finetune the TransferText arguments after this code runs succesfully.

    Sub Import_table()
    
        Dim strfilename As String
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Title = "Select the CSV file to import"
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "CSV Files", "*.csv", 1
            .Filters.Add "All Files", "*.*", 2
            If .Show = -1 Then
                strfilename = .SelectedItems(1)
                DoCmd.TransferText TransferType:=acImportDelim, _
                    TableName:="Import_Table", FileName:=strfilename
                Else
                Exit Sub
            End If
        End With
    
    End Sub



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.


    • Edited by Asadulla JavedEditor Friday, February 1, 2013 1:16 PM
    • Marked as answer by Spark_True Friday, February 8, 2013 9:07 PM
    • Unmarked as answer by Spark_True Friday, February 8, 2013 9:07 PM
    • Marked as answer by Spark_True Friday, February 8, 2013 9:08 PM
    Friday, February 1, 2013 1:15 PM
    Answerer
  • Hi Sorry for the late reply but that worked wonderfully. Thanks a million
    Friday, February 8, 2013 9:08 PM
  • I am actually having a small issue with this. It doesn't recognise the first row as being headings and therefore it is importing data into random fields in access. Does anyone know how to fix this?
    Friday, February 22, 2013 9:55 AM
  • Try adding one extra argument ( HasfieldName)
    Sub Import_table()
    
        Dim strfilename As String
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .Title = "Select the CSV file to import"
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "CSV Files", "*.csv", 1
            .Filters.Add "All Files", "*.*", 2
            If .Show = -1 Then
                strfilename = .SelectedItems(1)
                DoCmd.TransferText TransferType:=acImportDelim, _
                    TableName:="Import_Table", hasfieldnames:=True, FileName:=strfilename
                Else
                Exit Sub
            End If
        End With
    
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, February 22, 2013 10:41 AM
    Answerer
  • Hi Learning and Learning that fixed it perfectly, however the formatting is still an issue.

    I have a field that is 14/15 digits long but when I upload it, it turns it into a E+13 or E1+15. Si there anythign we can you to maintain formatting?

    On the csv it is defined as text, I have defined the field as text in access also so I am not quite sure what I am missing.

    Thanks.

    Friday, March 1, 2013 4:53 PM
  • Import gives you very basic facility without much coding.

    But probably you should opt for DAO or ADO objects for better control over importing data.Both have options for specifying format.You can search internet for these or wait till some expert in these field helps you.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, March 2, 2013 5:52 AM
    Answerer