How can I read CSV file using VBA?

[mls via AccessMonster.com]

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]

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

[Stuart McCall]

"mls via AccessMonster.com" <u55943@uwe> wrote in message
news:a19e72191967d@uwe...

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

[mls via AccessMonster.com]

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

[Mike Painter]

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.

[Mike Painter]

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.

[mls via AccessMonster.com]

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.

--

[mls via AccessMonster.com]

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.

--

[Mike Painter]

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

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

[mls via AccessMonster.com]

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]

[Mike Painter]

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.

[mls via AccessMonster.com]

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]

--
Message posted via http://www.accessmonster.com

[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

[mls via AccessMonster.com]

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.

--

[mls via 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]

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

[mls via AccessMonster.com]

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.

--