Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Import CSV File to Access Database using vbscript

Posted on 2012-04-11
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


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/VB.net 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 668 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 40

Accepted Solution

als315 earned 668 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

by:Helen Feddema
Helen Feddema earned 664 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

618 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