Need some conceptual help

[Bmetz]

Hey everyone

I'm working on an application (VB.Net), and need to move data from 1000+ text files (small amount of data from each file) into an Excel spreadsheet. I'm pretty sure I know how to pull the data from a single text file, but paging through the 1000+ files and moving the data into an Excel sheet are the issues I'm having issues with.

At this point, I'm just trying to decide on a best way to accomplish this. Any and all suggestions would be greatly beneficial!

Thanks guys

Bryan

[JMvVliet]

I think you should take a look at working with Excel automation:

Like this (code is absolutely not correct, but it gives you an idea, my VB install is damaged :-( ):

Dim exc as new Excel.Application
Dim Files as long, fil as long, i as long
 
i = 1
fil = 1
Files = [number_of_files]
 
exc.AddNew"test.xls"
 
for each fil in Files
exc.rows(i).value = fil.data
i = i + 1
next

Hope this will help.

[Ecniv]

Couple of choices:
- one is the Excel automation via another app
- if you only need to do this once and never again, perhaps writing it into VBA in Excel might be an idea
- import it into Access or other database, once all in export the table to excel
- something completely different which I haven't thought of...


Some of it will depend on how the data is formatted in the files and whether all the files follow the same formatting (file formats).

Can you paste of the format or a sample of a file..?
What do you need to do with all the data afterwards..?

Vince

[PilgrimPete]

Depending on the format you want to end up with in Excel, you could try using ADO and treating your workbook as a database. I guess ADO.Net will have similar functionality, but I don't know enough about it!

Using ADO.Net would probably be more efficient than automation, because you won't have to worry about the Interop overhead.

[Alternatively, if you use ADO.Net, you might be able to read all your text file data into a dataset and pump all of it into Excel in one go..?]

Do I sound as if I have any idea what I'm talking about? No? I'll stop here then

[JMvVliet]

PilgrimPete, you make me alert on a project I did in the past. Here I get info out of Excel using ADO. The connectionstring is as follows:

With cnExcel
        .Provider = "Microsoft.JET.OLEDB.4.0"
        .ConnectionString = "Data Source = " & sDataSource & ";" & _
                            "Extended Properties = ""Excel 8.0;HDR=No;IMEX=1"""
        .Open
    End With

where 'sDataSource' is the path to the excel file, 'Excel 8.0' refers to the version of excel (XP = 10, I think), HDR refers to the header columns in Excel, and IMEX is the IMport or EXport mode (1 means only export, i thought 2 was both import and export, but it's a long time ago).

You query it with:

rsExcel.Open "SELECT * FROM [" & wsName & "$]", cnExcel

where the '$' must exist after the worksheet name and wsName is the worksheet name.

I think you can edit the recordset the normal way, with addnew and update and so on.

[Bmetz]

Ecniv,

Ok, here's the situation. I have 102 different folders of historical information on traded futures contracts. Each folder contains between 50 & 100 text files. For each folder, I need to develop an Excel spreadsheet, importing all of the data from the text files in that respective folder. i.e. I need 102 different Excel spreadsheets of data.

Here is a line of data from one of the files (I've also attached a sample file to this thread):

940601, 0.0000, 0.0000, 0.0000, 0.0000, 8501, 651,

All of the files have the same format. (Appear to be tab and comma delimited) There is a naming scheme to the files which is as follows: "XX" + "##" + "Y"

XX = 2 Letter abbreviation for futures contract type
## = 2 digit year
Y = Not sure at this moment (will need to get this information from my client)

Once I have the data into a spreadsheet, I'll be creating various charts and reports using Excel.

Thanks for your help If there's any more information you need just let me know.

[Ecniv]

OK,

CSV files... at least it looks like it along with limited files... but no tabs (not in the one you posted anyway).

If they are all the same format, like the one you posted, you can open them in excel no problem. The problem is getting the data compiled into one sheet.

Assuming you are not going to use access at all there are several ways to go.

- Import each file via code and save as sheets... uses something like the following.

Workbooks.OpenText Filename:="C:\Vince\ab94q.txt", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
    
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

- Import as above but cut n paste into a main spreadsheet (also need to add the source file name to a column before putting into a main sheet so you can tell which one is from where.

- Code an import which parses trough the files (I personally would do this if you have time) - can be slow though and need some way to tell the user whats happening.

- VB Code the above so you can tell the user and keep the files hidden (automation which uses invisble excel...)


I'm a bit busy atm but I'll look at it later if I may - you want it in excel only ?


Vince

[Ecniv]

Check the coding see if it does what you need.

You need to change the folder to look in and I'd suggest running it on two or three files only at first...


Vince