Solved

Script to append txt file daily to a ODBC Database

Posted on 2011-03-20
5
354 Views
Last Modified: 2012-08-13
I have a txt file that I want to export/append to a ODBC Database on a daily basis, Would I need software that needs to do this or can a script be run to do so ?

Below is an example of what the file format looks like every day parc2.txt
0
Comment
Question by:John-S Pretorius
  • 3
5 Comments
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 60 total points
ID: 35179055
Use VBScript to connect to your ODBC database, read txt file, create and execute insert statements...

creating an odbc connection using vbscript
http://www.visualbasicscript.com/creating-an-odbc-connection-using-vbscript-m54220.aspx
0
 
LVL 8

Accepted Solution

by:
GundogTrainer earned 65 total points
ID: 35179235
Can you state the backend database type - I know you have stated it is ODBC compliant but if its an MSSQL instance you can use the BCP utility to "Bulk Copy" a text file into a database.
(There are other tools for other databases vendors)

Microsoft details for the utility are here: http://msdn.microsoft.com/en-us/library/aa174646(v=sql.80).aspx

So for example to import park2.txt into a table called CarparkTemp in SQL Server SQL-SVR-1 would be
bcp dbo.CarparkTemp in park2.txt /SSQL-SVR-1 /UsqlimportUser -Ppassword

You may need to create a format file (fmt) to define your data types but it may be easier than developing your own vbscript to perform the import for you.
0
 

Author Comment

by:John-S Pretorius
ID: 35179339
Thank you, I believe that this puts me on the right track. I have sql express installed, will this work? as I'm trying to accumulate raw data for later access by a crystal report. I can also use access or Excel to accumulate/append data.
0
 

Author Comment

by:John-S Pretorius
ID: 35179341
Thank you, I believe that this puts me on the right track. I have sql express installed, will this work? as I'm trying to accumulate raw data for later access by a crystal report. I can also use access or Excel to accumulate/append data.
0
 

Author Closing Comment

by:John-S Pretorius
ID: 35232516
Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

747 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

11 Experts available now in Live!

Get 1:1 Help Now