Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Mon Sep 16, 2019 11:14 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Assigning empty string on txt file import.
PostPosted: Sat Jan 19, 2013 5:49 am 
Offline

Joined: Sat Feb 04, 2012 1:26 pm
Posts: 86
Location: Italy
Hi,
I am importing a csv file into an Ms Access table.
each of the row contains several fields that have to be converted to various datatypes: integers, strings and dates.
Most of the rows in the text file have all the fields containing a value.
item;code;name;cat;weight;rapporto;data_in

Some rows may contain only the first three fields.
item;code;name;;;;

So, when I try to import into the database table, while the tull value rows are imported, the row containing only the first three field full are skipped and, so, the db stores fewer rows that the txt it originated from.

this is the portion of my code:

If f <> Nil then

t=TextInputStream.Open(f)
t.Encoding=Encodings.MacRoman

while not t.EOF
leggelinea = t.ReadLine
RS.Column("Item") = mid(leggelinea,1,4)
RS.Column("Code") = mid(leggelinea,5,12)
RS.column("Name") = mid(leggelinea,17,18)
RS.column("cat") = mid(leggelinea,35,2)
RS.column("weight") = mid(leggelinea,37,3)
RS.column("Rapporto") = mid(leggelinea,40,8)
data = mid(leggelinea,48,2) +"/"+ mid(leggelinea,50,2)+"/"+ mid(leggelinea,52,4)
RS.column("Data_in") = data

DB.InsertRecord("Orders 2012", RS)
wend
DB.Commit
DB.Close
t.Close

End if


I tryed to modify the code like this:


If f <> Nil then

t=TextInputStream.Open(f)
t.Encoding=Encodings.MacRoman

while not t.EOF
leggelinea = t.ReadLine
if IsNumeric(mid(leggelinea,35,2))= true then

RS.Column("Item") = mid(leggelinea,1,4)
RS.Column("Code") = mid(leggelinea,5,12)
RS.column("Name") = mid(leggelinea,17,18)
RS.column("cat") = mid(leggelinea,35,2)
RS.column("weight") = mid(leggelinea,37,3)
RS.column("Rapporto") = mid(leggelinea,40,8)
data = mid(leggelinea,48,2) +"/"+ mid(leggelinea,50,2)+"/"+ mid(leggelinea,52,4)
RS.column("Data_in") = data

else

RS.Column("Item") = mid(leggelinea,1,4)
RS.Column("Code") = mid(leggelinea,5,12)
RS.column("Name") = mid(leggelinea,17,18)

end if


DB.InsertRecord("Orders 2012", RS)
wend
DB.Commit
DB.Close
t.Close

End if


in this case, all the rows are imported, but the fields that should be empty, contain a value, instead.

so I tryed something like this but without success:


If f <> Nil then

t=TextInputStream.Open(f)
t.Encoding=Encodings.MacRoman

while not t.EOF
leggelinea = t.ReadLine
if IsNumeric(mid(leggelinea,35,2))= true then

RS.Column("Item") = mid(leggelinea,1,4)
RS.Column("Code") = mid(leggelinea,5,12)
RS.column("Name") = mid(leggelinea,17,18)
RS.column("cat") = mid(leggelinea,35,2)
RS.column("weight") = mid(leggelinea,37,3)
RS.column("Rapporto") = mid(leggelinea,40,8)
data = mid(leggelinea,48,2) +"/"+ mid(leggelinea,50,2)+"/"+ mid(leggelinea,52,4)
RS.column("Data_in") = data

else

RS.Column("Item") = mid(leggelinea,1,4)
RS.Column("Code") = mid(leggelinea,5,12)
RS.column("Name") = mid(leggelinea,17,18)
RS.column("cat") = null
RS.column("weight") = null
RS.column("Rapporto") = null
RS.column("Data_in") = null
end if


DB.InsertRecord("Orders 2012", RS)
wend
DB.Commit
DB.Close
t.Close

End if


Top
 Profile  
Reply with quote  
 Post subject: Re: Assigning empty string on txt file import.
PostPosted: Sat Jan 19, 2013 8:24 am 
Offline
User avatar

Joined: Mon Feb 05, 2007 5:21 pm
Posts: 600
Location: New York, NY
The problem is, you keep reusing the same DatabaseRecord so the columns you don't access are left with the previous value. Try modifying your code like this:
If f <> Nil then

t=TextInputStream.Open(f)
t.Encoding=Encodings.MacRoman

while not t.EOF
RS = new DatabaseRecord // Add this

leggelinea = t.ReadLine

RS.Column("Item") = mid(leggelinea,1,4)
RS.Column("Code") = mid(leggelinea,5,12)
RS.column("Name") = mid(leggelinea,17,18)
if leggelinea.Len > 34 then // Faster than IsNumeric and Mid
RS.column("cat") = mid(leggelinea,35,2)
RS.column("weight") = mid(leggelinea,37,3)
RS.column("Rapporto") = mid(leggelinea,40,8)
data = mid(leggelinea,48,2) +"/"+ mid(leggelinea,50,2)+"/"+ mid(leggelinea,52,4)
RS.column("Data_in") = data
end if

DB.InsertRecord("Orders 2012", RS)
wend
DB.Commit
DB.Close
t.Close

End if

Also, since you are using a single-byte encoding, using MidB would be faster than using Mid, but using a Structure would be better still. In this case, you would define your structure with each field you need:
Structure OrderLine
Item As String * 4
Code As String * 12
Name As String * 18
Cat As String * 2
Weight As String * 3
Rapporto As String * 8
Day As String * 2
Month As String * 2
Year As String * 4

Then you could modify your code like this:
dim enc as TextEncoding = Encodings.MacRoman
while not t.EOF
RS = new DatabaseRecord // Add this

leggelinea = t.ReadLine
dim ol as OrderLine
ol.StringValue( false ) = leggelinea

RS.Column("Item") = ol.Item.DefineEncoding( enc )
RS.Column("Code") = ol.Code.DefineEncoding( enc )
RS.column("Name") = ol.Name.DefineEncoding( enc )
if leggelinea.LenB > 34 then // Faster than IsNumeric and Mid, and faster than Len
RS.column("cat") = ol.Cat.DefineEncoding( enc )
RS.column("weight") = ol.Weight.DefineEncoding( enc )
RS.column("Rapporto") = ol.Rapparto.DefineEncoding( enc )
data = ol.Day + "/" + ol.Month + "/" + ol.Year
RS.column("Data_in") = data.DefineEncoding( enc )
end if

DB.InsertRecord("Orders 2012", RS)
wend

_________________
Kem Tekinay
MacTechnologies Consulting
http://www.mactechnologies.com/

Need to develop, test, and refine regular expressions? Try RegExRX.


Top
 Profile  
Reply with quote  
 Post subject: Re: Assigning empty string on txt file import.
PostPosted: Sat Jan 19, 2013 4:08 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
As far as terminology goes, you appear to be importing a Fixed Length Record, and not a CSV. But Kem is spot on about creating a new DatabaseRecord for each record in the file.


Top
 Profile  
Reply with quote  
 Post subject: Re: Assigning empty string on txt file import.
PostPosted: Sat Jan 19, 2013 5:48 pm 
Offline

Joined: Sat Feb 04, 2012 1:26 pm
Posts: 86
Location: Italy
Kem,
Thanks a lot. Now it works as expected.
Before you gave me your suggestion, I tryed anyway to workaround the problem, assigning value auch as 0 for integers and 1900/01/01 for dates.

"rs = new Databaserecord" is the same thing as "dim rs as new databaserecord". isn'it?
Also applyed the structure and it is faster.

Tim,
yes I meant to say Fixed Lenght Record. I have, erroneusly believed, so far, tha csv, was any text file whose line could be divided into several fields. now I understand... csv stand for comma separated values.

thanks for helping,


Top
 Profile  
Reply with quote  
 Post subject: Re: Assigning empty string on txt file import.
PostPosted: Sat Jan 19, 2013 6:58 pm 
Offline
User avatar

Joined: Mon Feb 05, 2007 5:21 pm
Posts: 600
Location: New York, NY
Antonio wrote:
"rs = new Databaserecord" is the same thing as "dim rs as new databaserecord". isn'it?

If the "dim" is within a loop, yes.

_________________
Kem Tekinay
MacTechnologies Consulting
http://www.mactechnologies.com/

Need to develop, test, and refine regular expressions? Try RegExRX.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group