import a CSV file to MS Access

[tuktuk]

is possible to import a csv to a table in access?

i have code that peorfrom the fcn from MS Excel to MS Access but i can't find info regarding CSV.

thanks
tuk

 

[SydneyGeek]

Is it always the same file name? And is theere onyl 1 file at a time?

The method to use is TransferText -- check the syntax in the Help.

Also check these threads...
http://www.mrexcel.com/board2/viewtopic.php?t=296390&highlight=transfertext
http://www.mrexcel.com/board2/viewtopic.php?t=272198&highlight=transfertext

Denis

 

[tuktuk]

hey there denis,

Yes it is always the same file name.

adn Yes there is only one file at a time.

I am created the module and attempted to step through (after testing the file>get external data>import manual way which worked) BUT I GET and error # 3625 (the text file 'DAILYRMT.csv' does not exist."

i tried the following....creating a folder where it is the only .csv file in the folder but no luck

and

i tested both way to name my file where IMPORT_SPEC = "DAILYRMT.csv" and whereIMPORT_SPEC = "DAILYRMT". that is i was not sure if the definition needed to include the file extension.

might there need to be a REFERNCE setting in the mdb that needs to be checked?

that is my only idea but it's a guess.

any suggestions?

thanks
tuk

one other thing i thought it could be is the security settings? any thoughts on that as well?

 

[SydneyGeek]

If you post your code it might be easier to locate the problem. The syntax of that command is fussy.

Denis

 

[tuktuk]

thanks for the help........here is my code:

Function ImportCSVFiles()
    Dim FilesToProcess As Integer
    Dim i As Integer
    Const TOP_FOLDER = "G:\MSAccessTesting" 'adjust folder name to suit
    Const ARCHIVE_FOLDER = "G:\MSAccessTesting\Imported" 'adjust folder name to suit
    Const DEST_TABLE = "tbl_Daily_RMT" 'change to suit
    Const IMPORT_SPEC = "DAILYRMT.csv" 'change to suit

    
    With Application.FileSearch
        .NewSearch
        .LookIn = TOP_FOLDER
        .SearchSubFolders = False 'we only want to search the top folder
        .Filename = "*.csv"
        .Execute
        FilesToProcess = .foundfiles.Count
        
        For i = 1 To FilesToProcess
          'import each file
          DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, .foundfiles(i), True
        Next i
    End With
End Function

i'd like to add that it errors on the DoCmd line....that is it has the correct .foundfiles.Count when i check it by draggin my cursor over it in step through mode.

T

 

[SydneyGeek]

The import spec is actually a specification stored in Access, not a filename -- I think that's your problem. To create teh spec you need to use the manual import process, and once you have selected delimited and that the file has column headers, click the Advanced button in the bottom left.
At this stage you can tweak the settings a bit; select which fields (if any) to skip, and set the data types. Then save that (you can use the suggested default if you like) and go back to the rest of the import wizard. Use that saved name as your import spec.

Denis

 

[SydneyGeek]

Another thought:

If theere is only ever going to be one file whose name stays constant, you don't need to use Application.FileSearch. This simpler version should do the job:

Function ImportOneCSVFile()
    Dim FilesToProcess As Integer
    Dim i As Integer
    Const IMPORT_SPEC = "The name of your import spec" 'change to suit
    Const DEST_TABLE = "tbl_Daily_RMT" 'change to suit
    Const IMPORT_FILE = "G:\MSAccessTesting\DailyRMT.csv" 'adjust to suit; full path to file
    
    'import the file
    DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, IMPORT_FILE, True
    
End Function

Note: If you don't use an import spec, change the line to

    DoCmd.TransferText acImportDelim, , DEST_TABLE, IMPORT_FILE, True

Denis

 

[tuktuk]

the import is functioning well but i have an addition set of ?'s regarding this.

what exactly is in an IMPORT SPEC?

also, the csv file that i am importing DOES NOT contain headers.

so when i manually imported it, it created field name as Field1, Field2, etc. This is what i used to build many queries on.

when i use the docmd.transfer to a new sheet, the field header assigned are F1, F2, etc.

but the problem is that i want to import to the existing tbl called "DAILY RMT" with the Filed1, Field2, etc fields names and the code errors becasue it cannot find the F1 field in the table "DAILY RMT".

any suggestions?

 

[powderider86]

I don't know if this helps, it may be too simple, but you can just convert CSV files into excel files, and then import them... thats what I do, but that may not achieve the end result your looking for, I don't know.

 

[SydneyGeek]

The Import Spec lets you map imported data to your table. Among other things you can:
1. Define which fields to import and which to skip
2. Define the data type of the imported data (useful if you have any gaps in the first few records, because Access will then make a default call which is unlikely to be correct).

In your case you are best to pull the data into a new import table, then use an Append query to add it to the production table in your DB. So...

1. Import the data manually into a new table. Leave the field names as F1, F2, etc but check in the design that the data types are correct. (Hint: use the Currency data type for money -- it's more accurate than using Number and formatting it as Currency)

2. Use the code to:
a. Delete all from the import table (run a Delete query)
b. Import the new CSV data
c. Append the imported data to the main table (run an Append query, that you use to map the fields correctly).

Something like:

DoCmd.SetWarnings False
DoCmd.Execute "yourDeleteQuery"
'run your import code in here, then...
DoCmd.Execute "yourAppendQuery"
DoCmd.SetWarnings True

Denis