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

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!

Welcome to the forums. :wave:

Importing the entire file into an excel spreadsheet wouldn't be that difficult.

However, doing it in pieces would require you to be able to programmatically define "chunks". Specifically, what do you need from the file, and how it is set off from the rest of the file that would enable to you to code for it?

Thanks for the Welcome!

the data is seperated into columns and seperated by spaces.

Does this mean i can use the 'split' function to distinguish between seperate pieces of data? and do i have to specify a 'space' as the character that 'splits' the data?

Also the data is in several columns and i only need 2 of them to be moved to Excel and i need to be able to call up sections of data, e.g of data im manipulating:

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:01 Elec Sect +ES61 0.7810 0.0000 0.000000 0.000000 0.000000
08:00:02 Elec Sect -ES13 0.7768 0.0000 0.000000 0.000000 0.000000
08:00:02 Elec Sect -ES52 0.7760 0.0000 0.000000 0.000000 0.000000
08:00:02 Elec Sect -ES56 0.7785 0.0694 0.054019 0.000000 0.054019

so ideally i want to move all the data from the Time and Volt columns to Excel at the time 08:00:02.

Also to actually get the data from the .txt file to i have to make a new connection eg:

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

sorry, there's alot there, any help is much appreciated!

I haven't tried it, but I understand that you can use ADO.NET to read the whole file into a datatable, which would be considerably easier than manipulating it line by line. You would be right about the Split function, but if you can read the whole file into a datatable you won't need to worry about that. With a datatable, you can either sort/query on the table, or make a dataview based on the table and use the RowFilter method to restrict the table to just the desired rows.

Thanks for that,

As i have a large amount of data (about 4 times Excel capacity) would ADO.NET be able to support that amount of data?

Can i use it in version 6.3 of VB?

its been a while since i've used VB at all so no idea how to use the ADO.NET application.

Cheers