Re: How can I read CSV file using VBA?

[Stuart McCall]

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

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'.

 

[Mike Painter]

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.

 

[Mike Painter]

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

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.

 

[Mike Painter]

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.

 

[Mike Painter]

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
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

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.

 

[Mike Painter]

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) &
(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

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.