remove space at end of text file

[JT]

i have written some asp that reads a fixed length text file, line by line,
inserting each line into my database. my problem is that the text file
format seems to have extra space at the end of the file so my code thinks
there is actually one more line when there is not. is there a way to remove
these spaces before i loop through the file?

here is my sample code:

Do While df.AtEndOfStream <> True
line = df.ReadLine

account_no = trim(mid(line,3,9))
account_no = cleansedata(account_no, 1)

customer_name = trim(mid(line,12,28))
customer_name = cleansedata(customer_name, 1)

etc., etc.

Loop

 

[Steven Burn]

Do While df.AtEndOfStream <> True
line = df.ReadLine

If Not line = "" Then
account_no = trim(mid(line,3,9))
account_no = cleansedata(account_no, 1)
customer_name = trim(mid(line,12,28))
customer_name = cleansedata(customer_name, 1)
etc., etc.
End If
Loop

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Disclaimer:
I know I'm probably wrong, I just like taking part ;o)

 

[Aaron Bertrand [MVP]]

Instead of looping through the stream and keeping a handle on the file
throughout, how about:

set fso = CreateObject("Scripting.FileSystemObject")
set fs = fso.openTextFile("path:\filename.ext", 1)
blob = fs.ReadAll()
fs.close: set fs = nothing: set fso = nothing

lines = split(blob, vbCrLf)
for i = 0 to ubound(lines)
thisline = trim(lines(i))
if thisline <> "" then
' execute SQL statement to insert the line
else
' this was en empty line
end if
next


An article on other things FSO-related:
http://www.aspfaq.com/2039

You didn't mention what database you were using, but if it's SQL Server, you
can use BULK INSERT (which allows you to skip n lines, ignore a user-defined
number of "problem" rows, etc.) or BCP, as a better alternative to having
ASP do this work...

 

[Aaron Bertrand [MVP]]

If Not line = "" Then

Careful, if there's an empty space there, this will fail...

 

[JT]

i am using sql server - what's this about bulk insert???

 

[Ray at]

Here's one sample.
http://www.sqlteam.com/item.asp?ItemID=3207

Ray at work

 

[Steven Burn]

I would normally have used Len instead of "" but wasn't sure if ASP
supported it or not.

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

Disclaimer:
I know I'm probably wrong, I just like taking part ;o)

 

[Aaron Bertrand [MVP]]

i am using sql server - what's this about bulk insert???

Well, the command is called BULK INSERT, and it's a *very* fast way to
insert big log files or other plain text formats into a table. The format
is basically this:

BULK INSERT <tablename>
FROM '<path to file>'
WITH
(
ROWTERMINATOR = '\n',
FIELDTERMINATOR = '\t', -- for TSV, or ',' for CSV
MAXERRORS = 10,
FIRSTROW = 3,
ORDER (<usually_clust_index_col_name(s)>)
)

You should play with it, it's very powerful. See http://www.aspfaq.com/2438
for a couple of examples, and an explanation of some of the parameters.
You can read much more about BULK INSERT, and also BCP (which stands for
bulk copy) in Books Online. If you're not familiar with Books Online, see
http://www.aspfaq.com/2229

 

[Chris Hohmann]

i am using sql server - what's this about bulk insert???

http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_2e5s.asp

This information is also available in BOL (Books Online).