Printable View
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
Do all the 1000 files have the same format? I'm assuming they're delimited text files? I do stuff like that (not with a thousand, but with a non-fixed number that usually ends up being around 8-10).
You want all the files' data to end up in one spreadsheet? I really don't think that would be that hard a problem based on what I've done with reading in delimited text at runtime and sticking it into a DataSet with a number of tables. How about some more detail on your problem?
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:
940601, 0.0000, 0.0000, 0.0000, 0.0000, 8501, 651,
All of the files have the same format. (Look to be tab and comma delimited)
Thanks for your help ;) If there's any more information you need just let me know.
Assuming you already have the dataset described somehow...
For getting the files into the dataset:
This gets you a list of files, and filters by extension, and passes each filename to a function which will start reading it:Code:Private Sub frmLoadem_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim diDirectory As New DirectoryInfo(CurDir())
Dim afiFiles() As FileInfo
Try
' Call the convenient GetFiles method to get an array of all files
' in the directory.
afiFiles = diDirectory.GetFiles()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
End Try
Dim fi As FileInfo
For Each fi In afiFiles
If fi.Extension.ToLower = ".gen" Then
ReadFile(fi)
End If
Next
End Sub
And for the getting it out to Excel part, attached is a sample from the 101 VB.Net samples pack from Microsoft. Hope this gets you started.Code:Private Function ReadFile(ByVal strDataFileName As String)
Dim sr As StreamReader
' Open the file to read.
Try
sr = File.OpenText(strDataFileName)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error opening file" + strDataFileName + "!")
sr.Close()
Exit Function
End Try
' Read each line in the file.
' When the end of the file is reached, return the value "-1".
While sr.Peek <> -1
'Get a line and trim leading/trailing spaces
strLine = Trim(sr.ReadLine())
If strLine.EndsWith("\") = True Then
'This is not a complete line, read next line and concat it to this one
'Your files may not actually be like this but here's an
'example anyhow, mine were
strLine = strLine.Remove(strLine.IndexOf("\"), 1)
'Just in case some sneaky user stuck a \ on the last line
If sr.Peek <> -1 Then
strLine = strLine + sr.ReadLine()
End If
End If
'Open a new row in the data table
Dim myDataRow As DataRow = dsData.Tables("Table").NewRow()
'freshen up the text a bit if it ends with a comma
If strLine.EndsWith(",") = True Then
strLine = Trim(strLine.Remove(strLine.IndexOf(","), 1))
End If
'split the line into an array of strings
Dim arrFields As String = Split(strLine, ",")
'stick the array elements into fields
'whilst trimming out excess spaces
'actually you could write a loop for this but that
'makes it a little less comprehensible
myDataRow("Field1") = Trim(arrFields(0))
myDataRow("Field2") = Trim(arrFields(1))
myDataRow("Field3") = Trim(arrFields(2))
myDataRow("Field4") = Trim(arrFields(3))
myDataRow("Field5") = Trim(arrFields(4))
dsData.Table("Table").Rows.Add(myDataRow)
End While
sr.Close()
dsData.AcceptChanges()
End Function
Slow_Learner,
Thanks so much for the code...it has definitely helped me to get started on this project ;)
This is my first project in VB.Net, and I'm finding it rather difficult getting acclimated to the new version. Having a little bit of trouble with this bit of code here:
<vbcode>
Dim fi As FileInfo
For Each fi In afiFiles
If fi.Extension.ToLower = ".txt" Then
ReadFile(fi)
End If
Next
</vbcode>
It's giving me a "Value of type 'System.IO.FileInfo' cannot be converted to 'String'" error on the ReadFile(fi) line.
I'm also having a hard time finding information on declaring a dataset ;/ Any help in this area would be greatly appreciated.
I modified that section a little bit when I cut and pasted it from my project I used that loop in and I may have goofed, let me take another look. With respect to declaring datasets, you can either A) use the DataSet tool on the Data toolbar, which allows you to edit table/column information with the Collection Editor, which has the benefit of being fairly easy to get a grip on but the drawback of only being able to declare datasets on a form (it won't insert them in a module so working with multiform projects is harder); or B) declare them from code from a module (what Edneeis suggested to me and what I have been doing lately).
With either option you can also load in an XML schema if you want to (99% sure you can with the module, 100% if you use the toolbar) but you can also build a dataset completely from code, which I found to be simplest since I am a little retarded when it comes to XML. Here's some information on declaring datasets from code:
ms-help://MS.VSCC/MS.MSDNVS/vbcon/html/vbconDataSets.htm
ms-help://MS.VSCC/MS.MSDNVS/vbcon/html/vbtskaddinguntypeddatasetstoformorcomponent.htm
A walkthrough on XML schemas if you're interested (probably overkill for what you're doing right now):
ms-help://MS.VSCC/MS.MSDNVS/vbcon/html/vbwlkwalkthroughcreatingxmlschemawithone-to-manyrelationship.htm
A fairly clean example of how to declare a dataset completely from code (has a bit more than you need since it's got two related tables but it's pretty clear compared to most of the other examples):
ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfSystemDataDataTableClassTopic.htm
Yup I goofed. The function call ReadFile(fi) passes a fileinfo object, and the function itself expects a string (duh) as a parameter. I should have said ReadFile(fi.FullName.ToString), which would pass the filename+pathname in string form.
Bonehead alert! This piece:
should be:Code:'freshen up the text a bit if it ends with a comma
If strLine.EndsWith(",") = True Then
strLine = Trim(strLine.Remove(strLine.IndexOf(","), 1))
End If
Code:'freshen up the text a bit if it ends with a comma
If strLine.EndsWith(",") = True Then
strLine = Trim(strLine.Remove(strLine.Length - 1, 1))
End If
did smething similar a long while in vb6. I used ADO to read data from CSV files (many files to a folder, many folders) i processed this info and dumped it into an excel direct from an ADO recordset. Pretty clean code. Bottom line is - if your files are in any kind of delimeted or standard format then ths might be the route for you i.e. treating each file as a lind of database. Any more help needed just shout out. Cheers