Parse Large Text File

[liming]

Hi all,

I have to parse two text files on a weekly basis. Each range from 300kb to
1mb in total. Each text file has 5 columns (name,id, dollar,
startdate,enddate), everytime,
a) I need to parse each row, extract each column
2) check if the data already exisinst in the db between startdate and end
date
3) if not, then insert them into the the database, else, modify the record
with the new data.

As you can imagine, with size like 1mb, doing this row by row is not fast
nor efficient as it hits the db so many times (around 8000 -10,000 rows)

I'm wondering what would be a faster and efficent way to do this? I'm
thinking of a solution, but would love to get some input. Read into a
dataTable first and then modify the rowState? SqlBulkInsert? Is there a way
to parse large txt file into memory FAST?

thanks. Any suggestion is grealty appreciated.

 

[Remy]

I would use the SQL Bulk load tool (assuming you use SQL server).
This is fast and does the parsing for you. Then you have everything in
the db and just need to run a query on top of it.

Remy

 

[liming]

Hello Remy,

I'm not familiar with the sql bulk load tool. How do I invoke it within my
asp.net C# source?

I know ADO .NET 2.0 offers sql bulk insert, but i guess that's different.

 

[liming]

just did a quick search on sql bulk load, i have sql server 2000, but i
don't have access to its exe files. the sql server is hosted somwhere else.
(i'm using hosting solution)

 

[Remy]

Yeah, then you are most likely out of luck in terms of the SQL bulk
load tool.
What helps a little bit is if you combine a bunch of INSERT statements
into one call.
I think you can combine by separating the statements with a ;

 

[Walter Wang [MSFT]]

Hi liming,

I agree with Remy on this.

Since you have limited access to the database server, bulk insert is not
possible here.

Regarding Remy's suggestion on submitting multiple sql statements in one
call, can you test and verify the speed improvement? Thanks.


Regards,

Walter Wang
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[richard98007]

BULK INSERT or OPENROWSET

You could do the following:

Use BULK INSERT or OPENROWSET into a temporary table

Post to the live tables from the temporary table

Examples;

BULK INSERT testBulkInsert
FROM 'd:\work\test.txt'
WITH (
FORMATFILE='d:\work\testImport-f-n.Fmt',
TABLOCK
)

insert into testOpenRowset(c1, c2, c3, c4)
SELECT t1.c1, t1.c2, t1.c3, t1.c4
FROM OPENROWSET
(
BULK 'd:\work\test.txt',
FORMATFILE = 'd:\work\testImport-f-n.Fmt'
) AS t1(c1, c2, c3, c4);