New topic: Assigning empty string on txt file import.
<http://forums.realsoftware.com/viewtopic.php?t=46616> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message Antonio Post subject: Assigning empty string on txt file import.Posted: Sat Jan 19, 2013 5:49 am Joined: Sat Feb 04, 2012 1:26 pm Posts: 76 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 ktekinay Post subject: Re: Assigning empty string on txt file import.Posted: Sat Jan 19, 2013 8:24 am Joined: Mon Feb 05, 2007 5:21 pm Posts: 374 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 timhare Post subject: Re: Assigning empty string on txt file import.Posted: Sat Jan 19, 2013 4:08 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 11996 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 Antonio Post subject: Re: Assigning empty string on txt file import.Posted: Sat Jan 19, 2013 5:48 pm Joined: Sat Feb 04, 2012 1:26 pm Posts: 76 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 ktekinay Post subject: Re: Assigning empty string on txt file import.Posted: Sat Jan 19, 2013 6:58 pm Joined: Mon Feb 05, 2007 5:21 pm Posts: 374 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 Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
