import a CSV file to MS Access

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If you post your code it might be easier to locate the problem. The syntax of that command is fussy.

Denis
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856

ADVERTISEMENT

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

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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:

Code:
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

Code:
    DoCmd.TransferText acImportDelim, , DEST_TABLE, IMPORT_FILE, True

Denis
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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

New Member
Joined
Jan 7, 2008
Messages
20
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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:

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

Denis
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,208
Messages
5,857,920
Members
431,908
Latest member
Marjie174

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top