Import data from textfile!

[Niklas Östergren]

Hi!

I need some hint´s on how to import comma separated data from a textfile
into a table. Any help is apreciated!

I have a textfile with comma separated measurement data of temperature
inside a servomotor and I´d like to get the data into a colomn in Excell.
And I´m a little more familure with VBA in Access then in Excell so if I
only can get it into a table in Access I know how to do the rest myself.

This is what the textfile looks like:
1,3,5,6,7,8,9,10,10,11,11,12,12,13,13,13,14,14,15,15,15,16...etc.

TIA!
// Niklas

 

[Tom Wickerath]

Hi Niklas,

I have worked up an example in Access, which imports the temperature readings from a text file
into Access. It then allows one to chart the data in Excel, using automation. I will try
sending this example, in a .zip file, to you using the e-mail address you have indicated.

Tom
_____________________________________


Hi!

I need some hint´s on how to import comma separated data from a textfile
into a table. Any help is apreciated!

I have a textfile with comma separated measurement data of temperature
inside a servomotor and I´d like to get the data into a colomn in Excell.
And I´m a little more familure with VBA in Access then in Excell so if I
only can get it into a table in Access I know how to do the rest myself.

This is what the textfile looks like:
1,3,5,6,7,8,9,10,10,11,11,12,12,13,13,13,14,14,15,15,15,16...etc.

TIA!
// Niklas

 

[Niklas Östergren]

Hi Tom!

Thank´s a lot for all your help I realy apreciate it and it was way more
than I could ask for!

I have been away for this weekend and just arived back at work but I´ll take
a closer look at your code and if I make any changes to it I´ll get back to
it. I have tested it though and it works almost as an schoolexample of what
I need! ;-)

I have never ploted any chart from Access before (a lots of time in Excell
though, manually :-( ). So I don´t have the knowledge, right now, give you a
solution for how to set any text in the chart via code!



I have xome very nice book´s at home though so I´ll try to look in to it as
soon as possible! Right now I have do get back to work though.

Thanks a lot again Tom!



// Niklas

 

[Brian Kastel]

Set a reference to "Microsoft Scripting Runtime" in your project, then
insert the following code into a module:

== START OF CODE ===========================

Public Sub ImportDelimitedFileToRows(ByVal fname As String, _
Optional ByVal delim As String = ",")

'This code assumes that the file contains one line separated
'by a specific delimeter character. The code could be further
'refined to repeat the logic for multiple lines, but as it
'is written it will only read the first line of the file.

'Handle all errors.
On Error GoTo Err_ImportDelimitedFileToRows

'Declare procedural variables.
Dim fs As FileSystemObject
Dim ts As TextStream
Dim s As String
Dim lCPos As Long
Dim lPPos As Long
Dim sData As String

'Use scripting to access the text file.
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile(fname, ForReading, False, TristateFalse)

'Get the data.
s = ts.ReadLine

'Start at the beginning.
lPPos = 0
'Continue through the end of the data.
Do While lPPos < Len(s)
'Find the next delimiter.
lCPos = InStr(lPPos + 1, s, delim, vbBinaryCompare)
'If no more delimiters, get the remaining data.
If lCPos = 0 Then lCPos = Len(s) + 1
'Parse the data between the previous and current delimiter.
sData = Mid$(s, lPPos + 1, lCPos - lPPos - 1)
'Change this to do what you want with the "row" data.
Debug.Print sData
'Set the previous delimiter position to the one just used.
lPPos = lCPos
Loop

'Destroy the local objects.
ts.Close
Set ts = Nothing
Set fs = Nothing

Exit_ImportDelimitedFileToRows:
Exit Sub

Err_ImportDelimitedFileToRows:
'Better error handling can be implemented here.
MsgBox "An error occurred importing the file.", , "Message"
Resume Exit_ImportDelimitedFileToRows

End Sub

=== END OF CODE ==========================

You would call the routine using the following syntax:

ImportDelimitedFileToRows "file.txt", ","

The routine allows for other delimiters, as well.

The routine could easily be converted to a function that returns the number
of "records" retrieved by implementing a counter variable within the loop.

The particular way in which you save that data to your table will depend on
whether you choose to use DAO or ADO, so I have intentionally left out the
code for that portion. If you would like further help with this, please ask
and I will be happy to help.

My email address is spelled out below.

As an aside, I would like to add that by far the easiest way to accomplish
what I have just coded above is to open your text file in Microsoft Word,
and do a global search and replace to change "," to "^p". This will replace
all commas with paragraph characters, and you can then import your file as
you normally would.

Cheers!

(e-mail address removed)