Solved

Import CSV File to Access Database using vbscript

Posted on 2012-04-11
10
2,813 Views
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.

Thanks,

Patrick
0
Comment
Question by:pmcd2012
10 Comments
 
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?
0
 

Author Comment

by:pmcd2012
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.
0
 
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.  

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

Open in new window

0
 

Author Comment

by:pmcd2012
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.
0
 
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.
0
Backup Your Microsoft Windows Server®

Backup 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

by:pmcd2012
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.
0
 
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()

Loop

End Sub

Open in new window

0
 
LVL 39

Accepted Solution

by:
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?
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
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"
   appAccess.CloseCurrentDatabase

End Sub

Open in new window

0
 

Author Comment

by:pmcd2012
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now