Import CSV File to Access Database using vbscript

Posted on 2012-04-11
Last Modified: 2012-04-28
I have a need to import a csv file into an accdb database table.  The csv file has the following data:

Row 1: ABC/CHAN A/2
Row 2: Name
Row 3: BusAccnt
Row 4: Date/Time, Value
Row 5: 4/1/2011 0:15, 1000

The data can go on for any number of rows from 1 to 40,000 beginning at row 5.  Row 5 is where I need to begin the import into access.  On top of doing the import, I would like to be able to convert the date/time field into two separate columns: Date, Time.  Also, based on the number of records, I would need to add another column: Time Span.  The name of the csv file will house the Main account number that would also need to be inserted.  As a result, here are the fields I need to insert:

Main Account, Date, Time, Time Span, Value

I already have the following code to connect to the database:

Option Explicit 

Dim dbe 
Dim db  

Set dbe = CreateObject("DAO.DBEngine.120") 
Set db = dbe.OpenDatabase("C:\Data Warehouse.accdb")  

Open in new window

I already have a process that utilizes Microsoft Excel to import the CSV to Excel and calculate the time span and separate the date/time field and then upload to Access but if I can find a way to get out of Excel and run just a vbscript, it would be much better and possibly faster.


Question by:pmcd2012
LVL 21
ID: 37835922
Do you have to use vbscript (a .vbs file)?

 You could write the data to the .accdb database from Excel?

You can to do everything you need with VBA inside Access directly from the CVS file without having to use Excel at all.

Which method do you want o use?

Author Comment

ID: 37835928
I would prefer to use an option that doesn't tie up a computer.  I am not a member of the IT staff so I would have to utilize my own computer to run the Excel file which would tie it up every hour which I can't do.  I was thinking that utilizing vbscript would allow it to run in the background without tying up my computer.  Since I have to run calculations on the date field, which in the csv looks like this "03/01/2011 00:15" and I need to conver it to two columns, 03/01/2011 and 00:15, the only way I knew how to do that was using excel formulas.  If you have a better suggestion, I am open to it.
LVL 21
ID: 37835969
Ah ... so you really need to do this as a scheduled task every hour.

You can schedule an Access database to be run every how that would read the csv filr and import the data.

can you post an example of the actual csv file?

TIP: It is actually better design  (normalization) to store the date and time together than as separate fields. VBA has great date and time  functions that make working getting out just he date or time very easy.  

? DateValue("03/01/2011 00:15")
? TimeValue("03/01/2011 00:15")
12:15:00 AM 

Open in new window

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 37835982
Here is the problem with the date/time field.  When I need to pull this information later, I have to have the data sorted with dates in rows and time as column headers (which having them as separate columns allows me to do a crosstab query.  Additionally, I would potentially have multiple, sometimes hundreds, of different csv files I would have to process into Access.
LVL 21
ID: 37836042
<<Here is the problem with the date/time field.  When I need to pull this information later, I have to have the data sorted with dates in rows and time as column headers (which having them as separate columns allows me to do a crosstab query. >>
There really is no issue with storing date/time together for what you described. I regularly do crosstabs like that with the date and time stored in a single field.   It is simple to split the date and time into separate fields in the query with calculated fields.

<< Additionally, I would potentially have multiple, sometimes hundreds, of different csv files I would have to process into Access.>>
Not a problem. I have several client that do the same thing. All the csv files are dropped into a folder. When the apps (usually VB/ but some Access apps) fires off it looks in the folder and processing all the files it finds.  I move each file to a "processed folder" after it is processed.   It is all scheduled task so no humans are involved.

If speed is a real issue the you really should consider using a language that can compile into an .exe   like VB, C/C++, .Net etc.

Author Comment

ID: 37836047
It sounds like the access app is going to be the best route for now.  What I am basically trying to do is prove to my IT team, that currently has no faith it can be done, is that it can be done.  Until they can code it in their own language, I need to help my team out and keep them from doing all of this manually.

How would I go about doing what you say with loading the files?  Here are some additionaly requirements:

I could have multiple files for the same main account number and the only way to get them into the file system is to use a naming convention like 39493949-1.csv, 39493949-2.csv, etc.

I still need to be able to count the number of records in the file and insert a calculated field into the database table (if count > 20,000, 15, if >10,000, 30, else 60).

I also need to be able to capture the file name, prior to the -, if it exists, and insert it into the table.
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 167 total points
ID: 37836106
Everything you want to do can be done with Access VBA.

<<I still need to be able to count the number of records in the file and insert a calculated field into the database table (if count > 20,000, 15, if >10,000, 30, else 60).>>
Again you are storing calculated data. This could easily be calculated as needed. No need to store it.

I see why you would do this in Excel. But in a well design relational database you do not need to store calculated values.

Here is a quick example to read a folder and get the file names:

Public Sub ImportFiles()

Dim strFolder As String
Dim strFilename As String

Dim strAcctNum As String

strFolder = "C:\myfiles"

' get first file name
strFilename = Dir(strFolder & "\*.csv")

Do Until strFilename = ""
   ' get the account number to the left of the - in the file name
   strAcctNum = Left(strFilename, InStr(strFilename, "-") - 1)
   Debug.Print strAcctNum
   ' process file
   ' get next file name
   strFilename = Dir()


End Sub

Open in new window

LVL 39

Accepted Solution

als315 earned 167 total points
ID: 37836115
With Access VBA you can import CSV file, but you can also read it line by line, check structure and store in table without additional conversions. For Bank transactions it is often used.
Can you upload some sample files with dummy data?
LVL 31

Assisted Solution

Helen_Feddema earned 166 total points
ID: 37838772
I would do this in Access (at some time when you can use the database).  Get everything worked out -- first import the CSV into a raw table, then delete the first 4 records, then run a select query to parse out date and time into separate text fields, etc.  This would be done in a function, which can then be run from your VBScript like this:

Public Sub TestRun()

   Dim strDatabaseNameAndPath As String
   Dim appAccess As New Access.Application
   strDatabaseNameAndPath = "G:\Documents\NewData.mdb"
   appAccess.OpenCurrentDatabase (strDatabaseNameAndPath)
   appAccess.Run "YourFunction"

End Sub

Open in new window


Author Comment

ID: 37905679
Thanks for your responses everyone.  I am still working on a solution to not use Access this way since it isn't how I want this to work but for now, it is working with your solution.  Thanks.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Combobox row source 2 20
backup programme - VBA 3 24
VBA taking too long 5 18
Access summarise unique values in tables 2 15
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question