How can I read CSV file using VBA?

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/Fo...g/201001/1

"mls via AccessMonster.com" wrote in message
news:

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/Fo...g/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/Fo...g/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.