Hows it going everyone. Im still pretty new to vb .net, and am having a little trouble. I am loading a tab delimited txt file into an access database, and parsing the text to extract some of the needed information from each field, and while this works, it is really slow right now. Does anyone have any suggestions on how I could speed this up? The file that Im trying to load atm has about 100k rows. -
Dim fs As New IO.FileStream(openFileDialog1.FileName, IO.FileMode.Open)
-
Dim sr As New IO.StreamReader(fs)
-
Dim x() As String = sr.ReadToEnd().Split(CType(Chr(10), Char))
-
Dim docno() As String
-
Dim doc As String
-
Dim file As String
-
Dim fileloc As Integer
-
While J < x.Length - 1
-
docno = x(J).Split(vbTab)
-
'MsgBox(docno(4))
-
' If myRegex.IsMatch(x(J)) = True Then
-
If docno(2).Contains("\") = True Then
-
fileloc = docno(2).LastIndexOf(CType("\", Char))
-
Else
-
fileloc = 0
-
End If
-
-
file = docno(2).Substring(fileloc)
-
-
While file.Contains(Chr(34)) = True
-
file = file.Remove(file.IndexOf(Chr(34)), 1)
-
End While
-
-
While file.Contains("\") = True
-
file = file.Remove(file.IndexOf("\"), 1)
-
End While
-
' x(J) = myRegex.Match(x(J)).Value
-
doc = docno(3)
-
While doc.Contains(Chr(34)) = True
-
doc = doc.Remove(doc.IndexOf(Chr(34)), 1)
-
End While
-
Table1TableAdapter.Insert(doc, file, "Member of Txt", "0")
-
Table1TableAdapter.Update(TestdbDataSet.Table1)
-
-
' End If
-
J = J + 1
-
End While
-
10 2657
I know indexes and not having the tablelock set to on (in SQL server) affect performance. I wrote something using the SqlBulkCopy class which using table lock, reading 64 MB tab delimited file (200,000+ rows), create DataTable and all rows, and finally copy to server, executed in 13 seconds (running in Debug mode on my laptop against an old SQL server). How long is yours taking?
I used the MS JET oledb provider to read in the CSV file (56k records takes about 3-5seconds to read into a DataTable)
Then you can do with as you please the DataTable from it.
EDIT: I would be interested in learning this tablelock and bulk copy concept though
Thanks for the replies guys. Well right now its actually reading the file into an array using a streamreader, splitting on each new line, and Im guessing thats whats probably taking it so long? After reading it into an array its splitting each array string into what it needs to insert into access, and inserting them into access through the tableadapter.
And also forgot to add, its basically taking longer than Id be able to wait, lol, the longest i waited was about 15 min
anyone have any suggestions?
Well Im actually parsing the data before I submit it to access, because the access table is bound to a datagrid. I think that using the oledb to read in the csv would just read the whole csv into access right?
It would read it into a DataTable, or DataReader object. You can parse the contents as you wish
Basically what Plater is telling you is to treat the flat file as a database, and use OLE to connect to it. OLE has nothing to do with Access per se...the JET engine does, but it has more uses than just Access. It is also used to access Excel and flatfiles.
I don't see what you are using Access for anyway, unless you are storing the data there. If you are just trying to get some values out, you can just keep it in memory and filter your DataTable object.
Ok, I understand. I tried it a few other ways, including using this csv reader class: http://www.codeproject.com/KB/database/CsvReader.aspx?msg=2629921#xx2629921xx
There are actually paths and filenames in each row, and im removing any the path and any other characters other than the filename out of each row. Do you think performing those operations on every row would slow it down ?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: irond_will |
last post by:
Does anyone know how I might directly access PIL's bicubic
interpolator? I have an arbitrary array of points for which I need to
find interpolated color values. Unfortunately, I have no clue how...
|
by: Terry Bell |
last post by:
We've had a very large A97 app running fine for the last seven years.
I've just converted to SQL Server backend, which is being tested, but
meanwhile the JET based version, running under terminal...
|
by: Bob Alston |
last post by:
Anyone know a way to make all access to a linked table, in another
Access MDB, read only?
I really don't want all the hassle of implementing full access security.
I can't do this at the server...
|
by: TC |
last post by:
I have an Access database application with a lot of custom row
functions written in VBA. In other words, a lot of queries contain
calculated fields which use functions defined in the modules.
I...
|
by: G.Esmeijer |
last post by:
Friends,
I would like to read a text file (fixed length formaated) really fast and
store the data into an Access database (2003).
Using the streamreader and reading line by line, separating the...
|
by: Glen Conway |
last post by:
Hi,
I am trying to get the contents of a file in a hidden share on a remote
server, something like '\\server.domain.com\c$\program
files\application\document.xml'. When I try any of the...
|
by: troy |
last post by:
Could someone please point me in the right direction on how to read in
a large query with .net.
I am trying to emulate a legacy database system so I don't know the
upper bounds of the sql query....
|
by: Michael |
last post by:
I work with a highly programmed Access
database (some 15,000 lines of VBA code, much of it automating data
entry on forms -- and believe me, it's very tight code). In Access
97,
2000, 2002, and...
|
by: ARC |
last post by:
Hello all, I had this e-mail today from a user, and would like to get some
ideas to pass along. Many thanks, all! Andy
" my program is very very slow when more than two people are using the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |