Prev: Referencing subform from another form
Next: Toggle Allow Additions
From: mls via AccessMonster.com on 4 Jan 2010 13:37
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

From: mls via AccessMonster.com on 4 Jan 2010 14:00
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

From: Stuart McCall on 4 Jan 2010 14:30
"mls via AccessMonster.com" <u55943(a)uwe> wrote in message
news:a19e72191967d(a)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'.


From: mls via AccessMonster.com on 4 Jan 2010 15:25
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

From: Mike Painter on 4 Jan 2010 15:49
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.


 |  Next  |  Last
Pages: 1 2 3 4
Prev: Referencing subform from another form
Next: Toggle Allow Additions