Solved

Script to append txt file daily to a ODBC Database

Posted on 2011-03-20
5
373 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Many admins will agree: WSUS is is a nice invention but using it on the client side when updating a newly installed computer is still time consuming as you have to do several reboots and furthermore, the procedure of installing updates, rebooting an…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

786 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