I have to read CSV file using VBA. I don't want to use macros or queries like
transfer spreadsheet etc. because I want to apply certain rules in a  module
after reading the file.


Thank you

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

I am using the following code but one of my field which has both characters
and numbers is not importing at all.. How do I handle this?


Sub import_csv()
DoCmd.TransferText acImportDelim, "", "Test_CSV", "c:\csv_files\12-31-2009
Test.csv", False, ""
End Sub

mls wrote:
>I have to read CSV file using VBA. I don't want to use macros or queries like
>transfer spreadsheet etc. because I want to apply certain rules in a  module
>after reading the file.
>
>Thank you

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

"mls via AccessMonster.com" <u55943@uwe> wrote in message 
news:a19e72191967d@uwe...
>I have to read CSV file using VBA. I don't want to use macros or queries 
>like
> transfer spreadsheet etc. because I want to apply certain rules in a 
> module
> after reading the file.
>
>
> Thank you
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
>

Lets say each row of the csv file consists of a string and two whole 
numbers. First you declare variables to hold the incoming values:

Dim var1 As String, var2 As Long, var3 As Long

Then you need an integer variable to hold the open file's id number:

Dim f As Integer

Then you read in the file in a loop till the end-of-file:

f = FreeFile
Open "c:\temp\myCSVfile.csv" For Input As f
Do Until EOF(f)
    Input #f, var1, var2, var3
    'Do whatever you want with the values here
    '(ie apply your rules)
Loop
Close f

Make sure you get the variable list in the correct order on the Input# line.

Untested 'air code'.

Thank you Stuart.
Can I ask you one more question?

Suppose my var1 has
1)  value "Document Name: 12-12-2009 Test Panel" and I need to read values
after  colon: how can I do that. 
2) Same way I need to read values after colon in my 3rd row "User: image4"

1)In another language I read these 2 line seperately and used functions to
get the values
SUBSTR to read the first row value. 
Input #f, var1
   Run_File_Name = substr(doc_name, 16)
2) opr=SCAN(op, -1);

Then I have to store these 2 values in a table.
Is that possible in ACCESS.

Thanks a lot


Stuart McCall wrote:
>>I have to read CSV file using VBA. I don't want to use macros or queries 
>>like
>[quoted text clipped - 3 lines]
>>
>> Thank you
>
>Lets say each row of the csv file consists of a string and two whole 
>numbers. First you declare variables to hold the incoming values:
>
>Dim var1 As String, var2 As Long, var3 As Long
>
>Then you need an integer variable to hold the open file's id number:
>
>Dim f As Integer
>
>Then you read in the file in a loop till the end-of-file:
>
>f = FreeFile
>Open "c:\temp\myCSVfile.csv" For Input As f
>Do Until EOF(f)
>    Input #f, var1, var2, var3
>    'Do whatever you want with the values here
>    '(ie apply your rules)
>Loop
>Close f
>
>Make sure you get the variable list in the correct order on the Input# line.
>
>Untested 'air code'.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

mls via AccessMonster.com wrote:
> I am using the following code but one of my field which has both
> characters and numbers is not importing at all.. How do I handle this?
>
>
> Sub import_csv()
> DoCmd.TransferText acImportDelim, "", "Test_CSV",
> "c:\csv_files\12-31-2009 Test.csv", False, ""
> End Sub

Without a specification (the "") I suspect Access is guessing at what the 
values are.
Probably that field starts with a number and then contains text.

Run through a manual import first, pick the advanced button and save the 
spec with a good name, then use it.

Unless I have to pharse the file I always import into a table, then use 
queries to modify what I need. 

mls via AccessMonster.com wrote:
> Thank you Stuart.
> Can I ask you one more question?
>
> Suppose my var1 has
> 1)  value "Document Name: 12-12-2009 Test Panel" and I need to read
> values after  colon: how can I do that.
> 2) Same way I need to read values after colon in my 3rd row "User:
> image4"


 Instr will find the colon and Mid will return the value.
YourVar = "Document Name: 12-12-2009 Test Panel"

Mid (YourVar,Instr(YourVar,":")+1 )

The Split function is another way. It has a lot of advantages but can't be 
used without a function built around it.

Mid can be used in queries as it stands. 

 can you help me run this code? i.e how can I check the value of testvar

Sub test()
Dim testvar As String

var1 = "Document Name: 12-12-2009 Test Panel"

testvar = Mid(var1, InStr(var1, ":") + 1)

End Sub

Also if my field one has the value = A10,5770,test1,Undetermined, how can I
put them into different fields..

well=A10
sample=5770
dectect=test1
value=Undetermined

These might look silly but I am learning VBA so..

Mike Painter wrote:
>> Thank you Stuart.
>> Can I ask you one more question?
>[quoted text clipped - 4 lines]
>> 2) Same way I need to read values after colon in my 3rd row "User:
>> image4"
>
> Instr will find the colon and Mid will return the value.
>YourVar = "Document Name: 12-12-2009 Test Panel"
>
>Mid (YourVar,Instr(YourVar,":")+1 )
>
>The Split function is another way. It has a lot of advantages but can't be 
>used without a function built around it.
>
>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

With advance options I could see that my csv file imported exactly the way I
wanted.
I saved the specifications but how can I open later to see the code.


Mike Painter wrote:
>> I am using the following code but one of my field which has both
>> characters and numbers is not importing at all.. How do I handle this?
>[quoted text clipped - 3 lines]
>> "c:\csv_files\12-31-2009 Test.csv", False, ""
>> End Sub
>
>Without a specification (the "") I suspect Access is guessing at what the 
>values are.
>Probably that field starts with a number and then contains text.
>
>Run through a manual import first, pick the advanced button and save the 
>spec with a good name, then use it.
>
>Unless I have to pharse the file I always import into a table, then use 
>queries to modify what I need.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

"mls via AccessMonster.com" <u55943@uwe> wrote in message 
news:a1a87fab6feb3@uwe...
> can you help me run this code? i.e how can I check the value of testvar
>
> Sub test()
> Dim testvar As String
>
> var1 = "Document Name: 12-12-2009 Test Panel"
>
> testvar = Mid(var1, InStr(var1, ":") + 1)

msgbox textvar

or
You can open an immediate window and use debug.print or



>
> End Sub
>
> Also if my field one has the value = A10,5770,test1,Undetermined, how can 
> I
> put them into different fields..
>
> well=A10
> sample=5770
> dectect=test1
> value=Undetermined
>
> 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





> Mike Painter wrote:
>>> Thank you Stuart.
>>> Can I ask you one more question?
>>[quoted text clipped - 4 lines]
>>> 2) Same way I need to read values after colon in my 3rd row "User:
>>> image4"
>>
>> Instr will find the colon and Mid will return the value.
>>YourVar = "Document Name: 12-12-2009 Test Panel"
>>
>>Mid (YourVar,Instr(YourVar,":")+1 )
>>
>>The Split function is another way. It has a lot of advantages but can't be
>>used without a function built around it.
>>
>>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
> 

"mls via AccessMonster.com" <u55943@uwe> wrote in message 
news:a1a8a447e169e@uwe...
> With advance options I could see that my csv file imported exactly the way 
> I
> wanted.
> I saved the specifications but how can I open later to see the code.

Open another import window, go to advanced, and select the name you saved.
>
>
> Mike Painter wrote:
>>> I am using the following code but one of my field which has both
>>> characters and numbers is not importing at all.. How do I handle this?
>>[quoted text clipped - 3 lines]
>>> "c:\csv_files\12-31-2009 Test.csv", False, ""
>>> End Sub
>>
>>Without a specification (the "") I suspect Access is guessing at what the
>>values are.
>>Probably that field starts with a number and then contains text.
>>
>>Run through a manual import first, pick the advanced button and save the
>>spec with a good name, then use it.
>>
>>Unless I have to pharse the file I always import into a table, then use
>>queries to modify what I need.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1
> 

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

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.

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

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. 

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

Mike, 
I did progress in running this code but again got stuck at Var1(2) & (3)..
error 3265 says item not found in this collection.. var1= a10,5245,test1,23.
45
I am expecting var1(2) test1 & var1(3)=23.45. So how can I capture these? 
Thank you
Code below..
If (i > 9) Then
                    
                    Temp = Mid(strline, InStr(strline, ":") + 1)
                    var1 = Split(Temp, ",")
                    mylog![sample] = var1(1)
                    'mylog![detect] = var1(2)
                    'mylog![value] = var1(3)
                    mylog.Update
                 
                End If
                i = i + 1
            Loop
mls wrote:
>I did not get a chance to try this today but will post an update when I am
>done.
>
>Thanks
>
>>If you want to use Line Input it would be something like
>>For I = 1 to 8
>[quoted text clipped - 13 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

If  var1= a10,5245,test1,23. 45
then it is not an array.
Use var2 or give it some meaningful name and dimension it.
Note this is zero based so the first value is YourTestArray(0)
Did you dimension var1 as shown in the sample I gave you?

mls via AccessMonster.com wrote:
> Mike,
> I did progress in running this code but again got stuck at Var1(2) &
> (3).. error 3265 says item not found in this collection.. var1=
> a10,5245,test1,23. 45
> I am expecting var1(2) test1 & var1(3)=23.45. So how can I capture
> these? Thank you
> Code below..
> If (i > 9) Then
>
>                    Temp = Mid(strline, InStr(strline, ":") + 1)
>                    var1 = Split(Temp, ",")
>                    mylog![sample] = var1(1)
>                    'mylog![detect] = var1(2)
>                    'mylog![value] = var1(3)
>                    mylog.Update
>
>                End If
>                i = i + 1
>            Loop
> mls wrote:
>> I did not get a chance to try this today but will post an update
>> when I am done.
>>
>> Thanks
>>
>>> If you want to use Line Input it would be something like
>>> For I = 1 to 8
>> [quoted text clipped - 13 lines]
>>>>> That way the code "looks" like what you are importing and errors
>>>>> cn be easy to spot. 

This is working fine. I just forgot to add the test and value fields to the
table so I got the 3265 ERROR Message
My code is working fine to read specific cells from CSV file. Now I need to
add code to process the data.
Thanks a lot Mike.

Mike Painter wrote:
>If  var1= a10,5245,test1,23. 45
>then it is not an array.
>Use var2 or give it some meaningful name and dimension it.
>Note this is zero based so the first value is YourTestArray(0)
>Did you dimension var1 as shown in the sample I gave you?
>
>> Mike,
>> I did progress in running this code but again got stuck at Var1(2) &
>[quoted text clipped - 25 lines]
>>>>>> That way the code "looks" like what you are importing and errors
>>>>>> cn be easy to spot.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1