Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 16th, 2006, 06:53 AM
Authorized User
 
Join Date: Oct 2005
Location: Summerville, SC, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default EZ Import

I have a dat file that is the export of a torque wrench. Here is the format:

#12001056.00000000210690@#12002057.10000000341158@ #12003056.40000000662262@#12004054.90000000200678@ #12005055.00000000441526@#12006057.30000000160528@ #12007055.30000000401356@#12008056.00000000060197@ #12009059.80000000180606@#12010057.50000000110365@ #12011058.30000000351195@#12012056.80000000060187@

What would be the best way to import this information into a table?

 
Old May 16th, 2006, 07:05 AM
Authorized User
 
Join Date: Oct 2005
Location: Summerville, SC, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

More Information. All this data is only on the first line of the file.


 
Old May 16th, 2006, 08:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What parts of the data do you need? What does each part mean?

For example, is this one reading from the device, or is it a collection of readings? If a collection, where is the break between each field and/or record?

It looks like to me that the records or readings break at the "@#" characters. Is that correct?

To give you an idea, you would use a Scripting.FileSytemObject, open the file, read a line at a time, and break the line into its components using an array. Then open a connection and recordset in your database and add new records using the array values. Then move on to the next line in the file.

HTH

mmcdonal
 
Old May 16th, 2006, 08:50 AM
Authorized User
 
Join Date: Oct 2005
Location: Summerville, SC, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

# = Start of data entry and @ = end of entry.

So #12001056.0000000002100690@ is the first entry.

I can open the file in VBA but how could I break up each entry in code and have it be dynamic??

 
Old May 16th, 2006, 08:54 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to open the file and read one line at a time. Do you know how to do that?

Then split the line into segments with

sLine = 'read line here

MyArray = Split(sLine, "#")

Your first array would contain:

MyArray(0) = "12001056.00000000210690@"
MyArray(1) = "12002057.10000000341158@"
etc

Then find the upper bound of the array, and that will give you the number of elements in the array.

Does each line need to be broken down further? Or will you store these values in the database?


mmcdonal
 
Old May 16th, 2006, 09:00 AM
Authorized User
 
Join Date: Oct 2005
Location: Summerville, SC, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well one line would be the whole thing. That long string I posted is the entry file.

So I open file Then

sLine would = what? I lost somewhere.

 
Old May 16th, 2006, 09:05 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

So that big long line is the entry, or the individual components that I broke out would be the entry?

What code do you have so far? You said you could open the file. How are you doing that?



mmcdonal
 
Old May 16th, 2006, 09:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is one way to approach this:

Set fso = CreateObject("Scripting.FileSystemObject")

'Check for existence of file first
'If the file exists, open a connection to the back end, and a recordset

If fso.FileExists("C:\data.txt") Then
    Set objStream = fso.OpenTextFile("C:\data.txt", 1, False, 0)
    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=YourDSN;" 'or connection to local database
    objRS.Open "SELECT * FROM tblYourTableName", objConn, 3, 3

'Start reading the file, break it into lines, and the lines into fields

    Do While Not objStream.AtEndOfStream
    sLine = objStream.ReadLine
    MyArray = Split(sLine, "#")

     objRS.AddNew
     objRS("Field0") = MyArray(0)
     objRS("Field1") = MyArray(1)
      ...
     objRS.Update
    Loop
End If


mmcdonal
 
Old May 16th, 2006, 09:16 AM
Authorized User
 
Join Date: Oct 2005
Location: Summerville, SC, USA.
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Everything makes sense expect:

objRS.Open "SELECT * FROM tblYourTableName", objConn, 3, 3

Why and I cycling through the records?

 
Old May 16th, 2006, 09:21 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to open a recordset on the table you want to put the data from the dat file into. Then update the recordset.

The line here is to open the recordset.
The SELECT statement is what recordset you want, like
   "SELECT * FROM tblTorqueWrench"
The objConn is the active connection.
The 3 and 3 are the cursor and lock.
   adUseClient and adLockOptimistic, I think.

Once you have it oepn, you can add lines to the recordset and then update the table - add the dat file data to the table.

Remember to close the connection and recordset at the end of the sub.

mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
Database import CVCgrad SQL Server 2005 4 September 19th, 2007 01:33 PM
Text Import- How to import the first 5 lines. nickzhang.zn Excel VBA 1 August 4th, 2007 05:06 PM
Import Error. lafilip Flash (all versions) 1 January 5th, 2007 01:12 AM
how to import from the same file kgoldvas XSLT 1 February 14th, 2006 06:45 AM
import to access midtowncreek Access 5 June 30th, 2004 01:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.