import CSV files into new tables

[ashwinghanta]

​	I am trying to import CSV files located in a folder into Access as new tables and I have been trying with a code

<tbody>
</tbody>

​

I am trying to import CSV files located in a folder into Access as new tables and I have been trying with a code Public Const rootdir = "C:\Users\deb670s\Desktop\importcsv" Sub import() Dim nr As Integer Dim file As AcBrowseToObjectType file = Dir$(rootdir & "*.csv") nr = 1 Do While file <> "" DoCmd.TransferText acImportDelim, "ImportSpec", "NewTableName-" & nr, rootdir & file, True, msoEncodingCentralEuropean file = Dir$ nr = nr + 1 Loop End Sub But I get an error saying variable not defined at msoEncodingCentralEuropean Can someone tell me where am I going wrong?

<tbody>
</tbody>

 

[Joe4]

Where did you get that "msoEncodingCentralEuropean" argument from?
What happens if you remove it from the command all together?

 

[ashwinghanta]

I got the "msoEncodingCentralEuropean" arguement from another code. I have tried removing it and now I get the error Type mismatch on the line

 [/COLOR][/COLOR]file = Dir$(rootdir & "*.csv")[COLOR=#333333][COLOR=#333333]

 

[Joe4]

Is "importcsv" a folder name?
If so, try adding another slash after it, i.e.

Public Const rootdir = "C:\Users\deb670s\Desktop\importcsv\"

 

[ashwinghanta]

The importcsv is a folder yes. I have added the "\" at the end but I still get the same error.

 

[Joe4]

Can you confirm that there are csv files in that folder?

 

[james_lankford]

what in the world is a
AcBrowseToObjectType


Dir
returns a string
https://msdn.microsoft.com/en-us/library/office/gg278779.aspx


VBA Dir function - How to traverse directories? - The Analyst Cave | Excel, VBA, programming and more


VBA: Dir function - VBA Reference

 

[ashwinghanta]

Yes, I can confirm that the CSV files are in importcsv folder on the Desktop. I have tried with a modified code

Public Const rootdir = "C:\Users\deb670s\Desktop\importcsv\"


    Sub import()
    Dim nr As Integer
    Dim file As String
    file = Dir$(rootdir & "*.csv")
    nr = 1
    Do While file <> ""
    DoCmd.TransferText acImportDelim, , "NewTableName-" & nr, rootdir & file, True, 1250
    file = Dir$
    nr = nr + 1
    Loop
    End Sub

However it gives me another error Run-time error '2521': You have specified a Transfer Type that doesn't support the HTML Table Name arguement.

 

[Joe4]

Where are you getting these arguments from that you keep adding?
The 6th argument for TransferText is HTMLTableName and is optional. You have a value of 1250 for it. Why? Try dropping it altogether, i.e.

DoCmd.TransferText acImportDelim, , "NewTableName-" & nr, rootdir & file, True

 

[ashwinghanta]

I have finally figured out the workaround. Here is the code

Option Compare Database
Option Explicit


Function DoImport()


 Dim strPathFile As String
 Dim strFile As String
 Dim strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean


 blnHasFieldNames = True


 strPath = "C:\importcsv\"


 strFile = Dir(strPath & "*.csv")


 Do While Len(strFile) > 0
       strTable = Left(strFile, Len(strFile) - 4)
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, "importspec", strTable, strPathFile, blnHasFieldNames




       strFile = Dir()


 Loop


End Function

I have changed the location of the folder. I think it was causing a problem with Users as I'm using a German windows system. I'd like to thank you for the help