Solved

Import CSV File to Access Database using vbscript

Posted on 2012-04-11
10
2,770 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

706 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

18 Experts available now in Live!

Get 1:1 Help Now