Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-09-04 : 08:15:56
|
| Mo writes "I have a 20 MB text file with over a million records. I created some scripts using VBScript in ASP to parse the file, but it always times out.What I would like to do is import the entire text file into a single cell of a table, or as an individual text variable of some kind, then create a function or some other type of script in SQL Server to parse the file.The rows that I need have 4 fields that I'll need to place into a database. The other rows have data in all kinds of different formats, so the final script will end up excluding all of that stuff.What I would like to know is 1. how do I use SQL Server to access the 20 MB text file?2. how do I build a function that will parse the text and place the data?Specifically for question 2, what I really need to know is what tools are available in SQL Server 2000 to accomplish this task.-Maurice" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-04 : 08:21:17
|
| Create a staging table with a single varchar(8000) column:CREATE TABLE staging(linedata varchar(8000) null)Use bcp or BULK INSERT to load the text file:BULK INSERT staging FROM 'C:\myfile.txt' WITH (FIELDTERMINATOR='')You can then delete all of the rows that you don't want by putting the proper WHERE clause condition (i.e. all rows you want to keep are 100 characters long):DELETE staging WHERE Len(linedata)<>100The same applies to the lines you want to keep and parse. Parsing lines can be accomplished using SubString() and CharIndex():SELECT SubString(linedata, 1, 10) AS Account, SubString(linedata, 11, CharIndex(', ', linedata, 11)-11) AS LastName,SubString(linedata, CharIndex(', ', linedata, 11)+2, 20) AS FirstNameFROM stagingIf you post some more detail on the structure of the file we can give you a better solution. |
 |
|
|
|
|
|
|
|
Importing poorly formatted text files