What function do I use to link data from a .txt file to Exce

[dimshen]

I have a large amount of data to manipulate in .txt format.

i want to be able to specify sections of data from the text file and import chunks of it into excel for further manipulation.

What VB function can i use to do this??

Many thanks for nay help!

[mbshinde78]

Hi,

I assume you have a specific format to your .txt file. If it has then you need to

1. read the file line by line using Line Input function

2. split each line to columns, store it temp into variables or array (functions needed : instr, mid etc.)

3. Use microsoft excel object library to create workbook and sheets

4. add rows and columns to excel sheet.

You can find lot of code to handle excel sheets through vb on http://www.planetsourcecode.com.

Cheers

[dimshen]

thanks alot for that,

another quick query, am i right in thinking i can use this to add a new connection to a .txt file?:

.Add(Connection:="TEXT;C:\My Documents\19980331.txt", _
Destination:=shFirstQtr.Cells(1, 1))

and once i have done this i can use the Line Input fucntion to read the data in the .txt file?

how can i then specify the parts of the .txt file i need to manipulate in excel?

as you can tell im not very experienced with VB so any help is much appreciated!

[mbshinde78]

Can you show me sample of your text file?

[dimshen]

yeah sure:

TIME COMP TYPE COMP ID VOLT(kV) CURNT(kA) PWR(MW) MVAR MVA
08:00:00 Elec Sect -ES60 0.7789 0.0000 0.000000 0.000000 0.000000
08:00:00 Elec Sect +ES61 0.7810 0.0000 0.000000 0.000000 0.000000
08:00:00 Elec Sect -ES13 0.7768 0.0000 0.000000 0.000000 0.000000
08:00:00 Elec Sect -ES52 0.7760 0.0000 0.000000 0.000000 0.000000
08:00:00 Elec Sect -ES56 0.7785 0.0694 0.054019 0.000000 0.054019

its about 40KB of data like this. Although the only data i need to transfer to Excel are the Volt(Kv) column and the Time column. Can i set some parameters in VB to do this??

[mbshinde78]

I can see that space is the only thing seperating them so use Split function with delimiter as space to split the liine into an array

arrayvar = split(stringline," ")

this will give you a row of data and you can then read a column like

x = arrayvar(0) and so on

[dimshen]

superb, so i can go across the columns and choose which one si want like so:

x = arrayvar(1,3)

so ultimately i need to be able to specify which rows i need to transfer as well, eg, i need to transfer all the data that ocurred at time 08:00:00.

do i have to write two statements to seperate the data in the vertical (columns) and horizontal (rows)??

[mbshinde78]

no,

pseudo code would be like

while eof(1)
strline = line input(#1)

strarray = split(strline," ")

print strarray(0)
end loop

the array will be a row at a time and not matrix.

[dimshen]

Many thnaks, i think i'm on the right track now.

[mbshinde78]

Most welcome

All the forum members will always help you.