How can I read CSV file using VBA?
in [Forms]
|
From: mls via AccessMonster.com on 6 Jan 2010 09:40 My code is running but the values are not inserted into the test_table I created.. Initially when I tried to import with fixed length it imported every thing into 1 field but with Advanced option I noticed that the system is reading with comma delimiter and creating 6 fields, all text. my CSV file is as follows.. I want to read 1,3,5,8-13 rows and store these values into an access table.. 1Document Name: 12-12-2009 Test Panel 2 3User: image4 4 5Run Date: Tuesday December 5 2009 12:45:11 6 7 8Well Sample Detector Ct 9A1 NTC Test1 Undetermined 10A2 5245 Test1 34.0956 11A7 5670 Test1 Undetermined 12A8 5861 Test1 31.5816 13A9 5743 Test1 33.0868 Is there a simple way to read this csv file with VBA? Mike Painter wrote: >> can you help me run this code? i.e how can I check the value of testvar >> >[quoted text clipped - 4 lines] >> >> testvar = Mid(var1, InStr(var1, ":") + 1) > >msgbox textvar > >or >You can open an immediate window and use debug.print or > >> End Sub >> >[quoted text clipped - 8 lines] >> >> These might look silly but I am learning VBA so.. > >You can use Mid for all of these but I would use >Split > >Dim WellInfo() as string >WellInfo = Split(YourWellField, ",") > >at this point >wellInfo(0)= "A10" >WellInfo(1)="5770" >WellInfo(2)="test1" >WellInfo(3)="Undetermined" > >so > >With SomeTable > .well = wellInfo(0) > .sample =WellInfo(1) > .detect = wellinfo(2) > .YourValue = WellInfo(3) >end with > >>>> Thank you Stuart. >>>> Can I ask you one more question? >[quoted text clipped - 11 lines] >>> >>>Mid can be used in queries as it stands. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
From: Mike Painter on 6 Jan 2010 23:01 mls via AccessMonster.com wrote: > My code is running but the values are not inserted into the > test_table I created.. Initially when I tried to import with fixed > length it imported every thing into 1 field but with Advanced option > I noticed that the system is reading with comma delimiter and > creating 6 fields, all text. > > my CSV file is as follows.. I want to read 1,3,5,8-13 rows and store > these values into an access table.. > 1Document Name: 12-12-2009 Test Panel > 2 > 3User: image4 > 4 > 5Run Date: Tuesday December 5 2009 12:45:11 > 6 > 7 > 8Well Sample Detector Ct > 9A1 NTC Test1 Undetermined > 10A2 5245 Test1 34.0956 > 11A7 5670 Test1 Undetermined > 12A8 5861 Test1 31.5816 > 13A9 5743 Test1 33.0868 > > Is there a simple way to read this csv file with VBA? You decide if the import is fixed or delimiterd in some way. I see no comma's in what you posted so am guessing you got six fields from line one by picking a space as a delimiter. I'd stick with that and work form that table. If not you will need to use OPEN amd Line Input Here is some sample code, Dim Textline <HERE YOU OPEN THE TABLE(S) YOU WANT TO WRITE THE DATA TO> Open "C:\TESTFILE.txt" For Input As #1 ' Open file. Do While Not EOF(1) ' Loop until end of file. Line Input #1, TextLine ' Read line into variable. <MORE CODE GOES HERE> Debug.Print TextLine ' Print to the Immediate window. Loop Close #1 ' Close file. Where MORE CODE appears you will have to, add a new record to your table with .addNew For I = 1 to 9 parse each line with mid or split set the values from your parsing rountine to the field names (covered in a previous post Use .Update to write the new record. Next I You could do all 13 rows but it appears that 9 through 13 should be related records in another table. So for I = 1 to 5 go through the same process writing the results to a second table using teh key from the other table. Failure to relate these records (if they are related) *WILL* continue to cause problems, especially when it comes to reporting. You would have to write code to answer a simple question. How many tests were marked "Undetermined" As for it being a simple way, I think so, just a lot of busy work, but I've been writing this type of import routine since dBase II on an Osborne I and I did something similar on an IBM 1620. I would have no problem asigning such a task to a student who wanted to learn about looping through files and tables and using some of the string handling functions of VB One more comment. I rarely use loops in such events. I would use 13 Line Input commands, parse the info then write it to a record. That way the code "looks" like what you are importing and errors cn be easy to spot.
From: mls via AccessMonster.com on 7 Jan 2010 15:16 Thanks Mike for the detailed message. You are giving me hope to continue this program but I could not pick up. Simple debug takes me hours together as I am new to VBA. Can you send me specific code where I can read only row 9 to row 100 for field1, field2, field3 & field4 ( all text fields) from a .CSV file ( i.e comma delimited) and insert into table called "test_csv" Thanks a lot Mike Painter wrote: >> My code is running but the values are not inserted into the >> test_table I created.. Initially when I tried to import with fixed >[quoted text clipped - 19 lines] >> >> Is there a simple way to read this csv file with VBA? > >You decide if the import is fixed or delimiterd in some way. >I see no comma's in what you posted so am guessing you got six fields from >line one by picking a space as a delimiter. >I'd stick with that and work form that table. >If not you will need to use OPEN amd Line Input >Here is some sample code, >Dim Textline ><HERE YOU OPEN THE TABLE(S) YOU WANT TO WRITE THE DATA TO> >Open "C:\TESTFILE.txt" For Input As #1 ' Open file. >Do While Not EOF(1) ' Loop until end of file. > Line Input #1, TextLine ' Read line into variable. > <MORE CODE GOES HERE> > Debug.Print TextLine ' Print to the Immediate window. >Loop >Close #1 ' Close file. > >Where MORE CODE appears you will have to, > add a new record to your table with .addNew >For I = 1 to 9 > parse each line with mid or split > set the values from your parsing rountine to the field names (covered >in a previous post > Use .Update to write the new record. >Next I >You could do all 13 rows but it appears that 9 through 13 should be related >records in another table. >So for I = 1 to 5 go through the same process writing the results to a >second table using teh key from the other table. > >Failure to relate these records (if they are related) *WILL* continue to >cause problems, especially when it comes to reporting. You would have to >write code to answer a simple question. >How many tests were marked "Undetermined" > >As for it being a simple way, I think so, just a lot of busy work, but I've >been writing this type of import routine since dBase II on an Osborne I and >I did something similar on an IBM 1620. > >I would have no problem asigning such a task to a student who wanted to >learn about looping through files and tables and using some of the string >handling functions of VB > >One more comment. I rarely use loops in such events. >I would use 13 Line Input commands, parse the info then write it to a >record. >That way the code "looks" like what you are importing and errors cn be easy >to spot. -- Message posted via http://www.accessmonster.com
From: Mike Painter on 8 Jan 2010 13:26 If you want to use Line Input it would be something like For I = 1 to 8 Line Input #1, TextLine ' Read line into variable. 'just throw it away. Next I For I = 9 to 100 Line Input #1, TextLine ' Read line into variable. <MORE CODE GOES HERE> Next I Wher more code appears you would parse the text as described below and in previous posts using Split or Mid and Instr mls via AccessMonster.com wrote: > Thanks Mike for the detailed message. You are giving me hope to > continue this program but I could not pick up. Simple debug takes me > hours together as I am new to VBA. Can you send me specific code > where I can read only row 9 to row 100 for field1, field2, field3 & > field4 ( all text fields) from a .CSV file ( i.e comma delimited) and > insert into table called "test_csv" > > Thanks a lot > Mike Painter wrote: >>> My code is running but the values are not inserted into the >>> test_table I created.. Initially when I tried to import with fixed >> [quoted text clipped - 19 lines] >>> >>> Is there a simple way to read this csv file with VBA? >> >> You decide if the import is fixed or delimiterd in some way. >> I see no comma's in what you posted so am guessing you got six >> fields from line one by picking a space as a delimiter. >> I'd stick with that and work form that table. >> If not you will need to use OPEN amd Line Input >> Here is some sample code, >> Dim Textline >> <HERE YOU OPEN THE TABLE(S) YOU WANT TO WRITE THE DATA TO> >> Open "C:\TESTFILE.txt" For Input As #1 ' Open file. >> Do While Not EOF(1) ' Loop until end of file. >> Line Input #1, TextLine ' Read line into variable. >> <MORE CODE GOES HERE> >> Debug.Print TextLine ' Print to the Immediate window. >> Loop >> Close #1 ' Close file. >> >> Where MORE CODE appears you will have to, >> add a new record to your table with .addNew >> For I = 1 to 9 >> parse each line with mid or split >> set the values from your parsing rountine to the field names >> (covered in a previous post >> Use .Update to write the new record. >> Next I >> You could do all 13 rows but it appears that 9 through 13 should be >> related records in another table. >> So for I = 1 to 5 go through the same process writing the results to >> a second table using teh key from the other table. >> >> Failure to relate these records (if they are related) *WILL* >> continue to cause problems, especially when it comes to reporting. >> You would have to write code to answer a simple question. >> How many tests were marked "Undetermined" >> >> As for it being a simple way, I think so, just a lot of busy work, >> but I've been writing this type of import routine since dBase II on >> an Osborne I and I did something similar on an IBM 1620. >> >> I would have no problem asigning such a task to a student who wanted >> to learn about looping through files and tables and using some of >> the string handling functions of VB >> >> One more comment. I rarely use loops in such events. >> I would use 13 Line Input commands, parse the info then write it to a >> record. >> That way the code "looks" like what you are importing and errors cn >> be easy to spot.
From: mls via AccessMonster.com on 8 Jan 2010 14:51 I did not get a chance to try this today but will post an update when I am done. Thanks Mike Painter wrote: >If you want to use Line Input it would be something like >For I = 1 to 8 > Line Input #1, TextLine ' Read line into variable. >'just throw it away. >Next I >For I = 9 to 100 > Line Input #1, TextLine ' Read line into variable. ><MORE CODE GOES HERE> >Next I >Wher more code appears you would parse the text as described below and in >previous posts using Split or Mid and Instr > >> Thanks Mike for the detailed message. You are giving me hope to >> continue this program but I could not pick up. Simple debug takes me >[quoted text clipped - 57 lines] >>> That way the code "looks" like what you are importing and errors cn >>> be easy to spot. -- Message posted via http://www.accessmonster.com
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Referencing subform from another form Next: Toggle Allow Additions |