By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

remove space at end of text file

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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)
JT <je******@sppinc.net> wrote in message
news:O$*************@tk2msftngp13.phx.gbl...
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

Jul 19 '05 #2

P: n/a
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
SQL Server MVP
http://www.aspfaq.com/


"JT" <je******@sppinc.net> wrote in message
news:O$*************@tk2msftngp13.phx.gbl...
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

Jul 19 '05 #3

P: n/a
> If Not line = "" Then

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

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #4

P: n/a
JT
i am using sql server - what's this about bulk insert???
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:Ow*************@TK2MSFTNGP12.phx.gbl...
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
SQL Server MVP
http://www.aspfaq.com/


"JT" <je******@sppinc.net> wrote in message
news:O$*************@tk2msftngp13.phx.gbl...
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


Jul 19 '05 #5

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

Ray at work

"JT" <je******@sppinc.net> wrote in message
news:eK**************@tk2msftngp13.phx.gbl...
i am using sql server - what's this about bulk insert???
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:Ow*************@TK2MSFTNGP12.phx.gbl...
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
SQL Server MVP
http://www.aspfaq.com/


"JT" <je******@sppinc.net> wrote in message
news:O$*************@tk2msftngp13.phx.gbl...
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



Jul 19 '05 #6

P: n/a
"JT" <je******@sppinc.net> wrote in message
news:eK**************@tk2msftngp13.phx.gbl...
i am using sql server - what's this about bulk insert???


http://msdn.microsoft.com/library/en...t_bcp_2e5s.asp

This information is also available in BOL (Books Online).
Jul 19 '05 #7

P: n/a
> 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

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Jul 19 '05 #8

P: n/a
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] <aa***@TRASHaspfaq.com> wrote in message
news:uK**************@TK2MSFTNGP11.phx.gbl...
If Not line = "" Then


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

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Jul 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.