Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data?
Best way to read in tab delimited data? Best way to read in tab delimited data?
Best way to read in tab delimited data?
Go Back  Xtreme Visual Basic Talk > > > Best way to read in tab delimited data?


Reply
 
Thread Tools Display Modes
  #1  
Old 08-21-2005, 04:20 PM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default Best way to read in tab delimited data?


I need to be able to read in data from a txt file that has 68 columns of tab delimited data and 10s of thousands of lines. I need to read about 10,000 lines at a time and put that data on a sheet, then read the next set and put it on a new sheet. The easiest way to do this would be xlsheet.cells(x,y).value = [however I can read tab delimited data].

Pseudocode is as follows:

Open File
Read 68 different numbers and simultaneously put them on a sheet in Excel
Continue reading until some string "Cycle" is encountered.
Move to the next sheet in Excel and then extract data again.
Until EOF

I can do all of this except for reading in the data. In C is as simple as this:

fscanf(input, "%d %d %d", &noise[count], &a[count], &b[count]);

because fscanf ignores white space. How can I do this with VB.NET?

Thanks.
Reply With Quote
  #2  
Old 08-21-2005, 08:12 PM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

There must be a faster way than this at just more than 1 row per second!

Code:
Dim f As Integer Dim s As String Dim AutoCycleData() As String f = FreeFile() FileOpen(f, ofdAutomatedCycle.FileName, OpenMode.Input) 'While Not EOF(f) For countRows = 2 To 10000 s = LineInput(f) AutoCycleData = Split(s, Chr(9)) If AutoCycleData(0) = "Cycle" Then xltsheet = xltemplate.Worksheets(AutoCycleData(1) + 1) s = LineInput(f) AutoCycleData = Split(s, Chr(9)) End If For countColumns = 0 To 67 xltsheet.Cells.Item(countRows, countColumns + 1).Value = Val(AutoCycleData(countColumns)) Next countColumns 'End While Next countRows FileClose(f)
Reply With Quote
  #3  
Old 08-21-2005, 09:18 PM
Wraith Daquell Wraith Daquell is offline
Freshman
 
Join Date: Aug 2005
Posts: 36
Default

The code I use is from Excel 2003 edition. I'm not sure that it will work on previous versions, though they all have similar functions. The following code is not a complete solution; it's just there to get you started. It just takes a file and reads in the tab-delimited data to the active sheet.
My apologies for any lack of elegance and/or style; it's been a while since I've endeavored to try VBA.

Code:
Dim FilePath As String FilePath = "Some\Path\To\The\File.txt" 'Change this to the path to the file Dim DestinationRange As String DestinationRange = "CellName (like A1)" 'Change this to the starting cell's name (A1, B1, C3, etc...) Dim QueryName As String QueryName = "TabDelim" 'This is the eventual name of the query (for future reference only) With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & FilePath, Destination:=Range(DestinationRange)) .Name = QueryName 'Sets the name of this query for future reference .TextFileStartRow = 1 'Change this to change the starting row .TextFileParseType = xlDelimited 'The file will be delimited by something .TextFileTabDelimiter = True 'Specifies tab delimiting .Refresh BackgroundQuery:=False 'As does this End With
__________________
...there is only one Daquell.
However, there is more than one way to solve a problem.
Reply With Quote
  #4  
Old 08-22-2005, 07:03 AM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

The file is too long to open with Excel. I have to break it up into each individual cycles (approx. 10000 rows) and place them each on their own sheet.

I guess I could put some millisecond timers in to see what process takes the most time. How do I get the system clock in ms?
Reply With Quote
  #5  
Old 08-22-2005, 08:03 AM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default I need to get the system clock value in milliseconds

I need to get the system clock value in milliseconds for timing/performance purposes. How can I do this?

Thanks.
Reply With Quote
  #6  
Old 08-22-2005, 08:11 AM
wayneph's Avatar
waynephBest way to read in tab delimited data? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

This example uses a TimeSpan to find the amount of time between two datetime objects. It has a milliseconds option.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #7  
Old 08-22-2005, 08:46 AM
Sonreir's Avatar
Sonreir Sonreir is offline
Contributor
 
Join Date: Jul 2004
Location: Hampshire, England
Posts: 540
Default

Now.Millisecond is a good choice as well.
Reply With Quote
  #8  
Old 08-22-2005, 08:53 AM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

Seems like that would be easier.

Quote:
Originally Posted by Sonreir
Now.Millisecond is a good choice as well.
Reply With Quote
  #9  
Old 08-22-2005, 09:06 AM
Sonreir's Avatar
Sonreir Sonreir is offline
Contributor
 
Join Date: Jul 2004
Location: Hampshire, England
Posts: 540
Default

Depends on what you're using it for. Now.Millisecond forces you to do a lot of the work yourself. I just listed it as an alternative. TimeSpan is probably the better choice for a timer.
Reply With Quote
  #10  
Old 08-22-2005, 09:24 AM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

Refer to Best way to read in tab delimited data? for additional background.

I am trying to pull sets of ~10,000 lines out of a text file and place each set on its own sheet in an Excel workbook. The process is slow, only handling roughly 250 lines per minute. Each line contains 68 numbers. Please refer to that link to see the code I used. I am trying to optimize this process, and the thread isn't getting enough hits in the File I/O forum to get me any responses.
Reply With Quote
  #11  
Old 08-22-2005, 01:09 PM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

Anyone have any idea or any experience with this? The code works, but it has been processing for over 5 hours now at ~250 lines per minute max!

At this rate, it's faster to do it manually. How is it that Excel can open text files so fast, but when I am exporting data to excel from memory it is freaking slow?
Reply With Quote
  #12  
Old 08-22-2005, 11:31 PM
brandnew's Avatar
brandnew brandnew is offline
Centurion
 
Join Date: Jun 2004
Location: Phnom Penh, Cambodia.
Posts: 181
Default

Tick (0.1 microsecond or 0.0001 millisecond) is also a good bet. Try Now.Ticks
__________________
Very useful and free stuffs for VB programmers.
API-Guide - Wonderful API viewer with examples and full explanation.
APIViewer - More APIs; replacement for default api viewer in VB.
Reply With Quote
  #13  
Old 08-23-2005, 07:15 AM
Machaira's Avatar
MachairaBest way to read in tab delimited data? Machaira is offline
Jedi Coder

* Expert *
 
Join Date: Aug 2002
Location: Abingdon, MD
Posts: 3,438
Default

Any you post a sample of the data file that you're reading? I have something I'd like to test for you but don't feel like writing a random data generator.
Reply With Quote
  #14  
Old 08-23-2005, 08:19 AM
Zumwalt Zumwalt is offline
Contributor
 
Join Date: Sep 2003
Location: Tampa, FL
Posts: 474
Default

Interesting, well, since a 'sheet' in excel can hold 65,000~ rows, and you only want 10,000~ rows per sheet, to speed this up, you might want to split the process up a tiny bit.

I'll go off the assumption you want the end result to be in the same 'workbook' just multiple sheets in the workbook.

First task, split the current text file up so that the line count matches your row count per sheet, meaning take BIG.Txt, and make it ChunkX.txt (replace X with a number)

I'd toy with this with some code I have written to import txt tab files to excel but I don't have data as large as you have.

250 rows a second seems awefuly slow to me.

Do you have a txt file with dummy data as large as your real data that I can play with?
Reply With Quote
  #15  
Old 08-23-2005, 08:31 AM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

I'll write that random number generator because the client would probably get me fired for posting their data here.

I'll make up some data and get it her shortly. What size attachments can I upload? The files I'm opening are 40 MB.
Reply With Quote
  #16  
Old 08-23-2005, 09:10 AM
Zumwalt Zumwalt is offline
Contributor
 
Join Date: Sep 2003
Location: Tampa, FL
Posts: 474
Default

I didn't know how active you were watching this thread.
So I took the time to create a small application to blow in 200,000 random number in 68 columns tab delimted.

Attached is that project, the resulting outfile.txt file is 50 megs in size, compressed is 20 megs.

Anyhow, this project is for those of us trying to help you with a solution.

Its created in VB.NET 2003

Now I am looking into the splitting thing I was talking about for you.
Updates soon I hope.
Attached Files
File Type: zip TabDump68.zip (7.7 KB, 6 views)
Reply With Quote
  #17  
Old 08-23-2005, 10:01 AM
Zumwalt Zumwalt is offline
Contributor
 
Join Date: Sep 2003
Location: Tampa, FL
Posts: 474
Default

As as update, I am running my lab now on this. I might add an elapsed time to it or something, anyway, the 50 megs is chugging along, this lab test machine I am doing this test on is a pentium 2, 350 mghz machine.

Time to create the 200,000 records was 2 minutes, time to convert the text file to an ado recordset was 2 minutes, don't know time on the conversion yet, manually timing it.
Reply With Quote
  #18  
Old 08-23-2005, 11:53 AM
Zumwalt Zumwalt is offline
Contributor
 
Join Date: Sep 2003
Location: Tampa, FL
Posts: 474
Default

One of the components I am using in this test, I didn't write, unfortunately I do not know who the original creator was.

This class file is an amazing little tool, going to go ahead and post it here, I had some syntax issues in the last 2 batch runs, so I am re-running them, hopfully I got the bugs worked out, once I am satisfied with the resulting information, I'll post the project.

In the mean time, here is the class object file.
Attached Files
File Type: zip pvWorksheet.zip (3.0 KB, 7 views)
Reply With Quote
  #19  
Old 08-23-2005, 12:17 PM
Zumwalt Zumwalt is offline
Contributor
 
Join Date: Sep 2003
Location: Tampa, FL
Posts: 474
Default

Current run time is 23 minutes against 200,000 records.
Doing some final testing before I post the project.
Reply With Quote
  #20  
Old 08-23-2005, 02:42 PM
TexasAggie TexasAggie is offline
Junior Contributor
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 286
Default

Thanks.

Quote:
Originally Posted by Zumwalt
Current run time is 23 minutes against 200,000 records.
Doing some final testing before I post the project.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data? Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data? Best way to read in tab delimited data?
Best way to read in tab delimited data?
Best way to read in tab delimited data?
 
Best way to read in tab delimited data?
Best way to read in tab delimited data?
 
-->