Text converting

[Ticotion]

Hi

I'm trying to import a textfile where the delimiter is Tab. I only get some
of the file imported in access because some rows contain values that looks
like this eg. 3-. This is the syntax from the program where data is extracted
from and means -3. Would it be possible to make a program that before
importing checks and corrects this for all rows?

Thanks for all your help

Ticotion

 

[Paolo]

Hi Ticotion,
this simple code wuold do the trick

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile("c:\test.txt") 'put the path and the name of your file
Set a = f.OpenasTextstream(1, -2)
Set b = f.OpenasTextstream(2, -2)
Do While a.AtEndOfStream <> True
my_str = a.readLine
my_str = Replace(my_str, "3-", "-3")
b.writeline (my_str)
cr = cr + 1
Loop
a.Close
b.Close
Set f = Nothing
Set fs = Nothing

HTH Paolo

 

[Ticotion]

Hi Paolo

Thank you for the quick reply.

I can see that the replace looks for 3-. This was only an example. It could
be 4- or 9- etc. How could this be implemented in your code

Thank you for your help.

Ticotion

 

[Paolo]

You can replace this line
my_str = Replace(my_str, "3-", "-3")
with this one
my_str = Replace(my_str, stringtosearch, stringtoreplace)
and put the value you want to search your file for in the variable
stringtosearch and the value that you wanna write in the file instead of
stringtosearch in stringtoreplace.
So you can set the two variables with whatever you want

Cheers Paolo

 

[Ticotion]

Hi Paolo

I see your point but I'm not that experience with programming so how would I
tell the code that I'm searching for valuese g. 3-, 9-etc. delete the - and
put the - in front of the number so it says -3, -9 etc?
How do i handle this in the in the Replace(my_str, stringtosearch,
stringtoreplace) part of the code?

Once again thank you for your help

Ticotion


Replace(my_str, stringtosearch, stringtoreplace)

 

[Paolo]

Well, if you wanna search and replace in your file, for example, all the
numbers from 1 to 10 you can do in this way

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile("c:\test.txt")
Set a = f.OpenasTextstream(1, -2)
Set b = f.OpenasTextstream(2, -2)
Do While a.AtEndOfStream <> True
my_str = a.readLine
For i = 1 To 10
stringtosearch = i & "-"
stringtoreplace = "-" & i
my_str = Replace(my_str, stringtosearch, stringtoreplace)
Next i
b.writeline (my_str)
cr = cr + 1
Loop
a.Close
b.Close
Set f = Nothing
Set fs = Nothing

Is the same code I posted before, I just modified tha part handling the
string to search for and the string to replace.

Regards Paolo

 

[Ticotion]

Hi Paolo

This works fine for numbers 1 to 10, but if I replace the 10 with 999999,
Access locks up. I have numbers that where it is 100000- that needs to be
changed. Any suggestions as to how the code can be modified to be able to
handle this?

Br
Ticotion

 

[Paolo]

I don't think access hangs, it takes just more time to execute the loop.
Keep in mind that my code makes the scan for every line in your file from
the number 1 to the upper end of the for cycle, in your case 999999.
So that, if you have for example 1000 rows in your file my program do
999999000 loop to scan all the file so it takes more time then to scan from 1
to 10.
In your case to speed up, if your nymbers can be up to 100000, put as upper
limit for the for cycle 100000 and you will avoid 900000 useless loop.

Paolo

 

[Marshall Barton]

I'm trying to import a textfile where the delimiter is Tab. I only get some
of the file imported in access because some rows contain values that looks
like this eg. 3-. This is the syntax from the program where data is extracted
from and means -3. Would it be possible to make a program that before
importing checks and corrects this for all rows?

A more common approach is to import the data into a scratch
table with text fields. Then fix up the funky values as you
inset the records to the resl destination table.

That way, you can just use a simple expression like
CLng([the problrm field])
because some functions understand the 123- syntax in a text
field.

If your original data file can be linked as an external
table, then you can skip the sctatch table and simply use a
query to convert and import at the same time.

 

[Ticotion]

Hi Paolo

Your right "moving" the upper limit will result in a longer process time.
One more question. If I set the upper limit to 10000 the all numbers below
1000 are correctly handled. But all above are not eg 1624- becomes 1-624? Do
you know why?

Thank you for your help

Ticotion

 

[Paolo]

Sorry about that, it's a little particular I didn't considerate. To solve it
just reverse the loop from the highst value to the lowest in this way

For i = 100000 To 1 Step -1
stringtosearch = i & "-"
stringtoreplace = "-" & i
my_str = Replace(my_str, stringtosearch, stringtoreplace)
Next i

Cheers Paolo

 

[John W. Vinson]

Hi

I'm trying to import a textfile where the delimiter is Tab. I only get some
of the file imported in access because some rows contain values that looks
like this eg. 3-. This is the syntax from the program where data is extracted
from and means -3. Would it be possible to make a program that before
importing checks and corrects this for all rows?

Thanks for all your help

Ticotion

Paolo's brute-force looping solution will work, but as you see it's
inefficient. What you can do is - once you have the data in a Text field - is
run an Update query:

UPDATE table
SET textfield = "-" & Left([textfield], Len([textfield]) - 1)
WHERE textfield LIKE "*-"

The Where condition will find all records where the textfield ends in a -
sign, and the SET clause will move the - to the front. You can use the Val()
function to convert the text string to a Number if you wish.