Import CSV file and adding the filename as a field

[Mark Carlyle via AccessMonster.com]

Ok, here is what I need to do... I have a CSV file with this info

Date Description Type Ref
No. Debit(-) Credit(+)
7/1/2005 NEW ACCOUNT DEPOSIT Credit
$0.01

The file is named 101012123.CSV

I need to import this file into a table and assign a fieldname
[TrustAcctNumber] = 101012123

The filename is the TrustAcctNumber but I need to truncate the .CSV

The resulting data would be something like

Date Description Type Ref No. Debit(-)
Credit(+) TrustAcctNumber
7/1/2005 NEW ACCOUNT DEPOSIT Credit $0.01 101012123

 

[Albert D.Kallal]

You don't mention right now how you import the data.

You could certainly import the data into a temp table that has the extra
field, and then simply do a update to that extra column. You then append the
data to the main table. This approach is somewhat safer anyway, since you
don't import the data into your main production table right away.

I suppose if the trans number in the main table is NEVER blank, then only
the records you JUST imported would be blank, and then you could just go
ahead and import into the main table..and then simply do a update of all
blank trans numbers to the file name.

If you don't want to type in the trans number, and want the whole thing to
be a one click affair then you would

pop up the file browse dialog and this would allow your code to know/grab
the file name the user selected. You can find the file browse pop dialog
code here
http://www.mvps.org/access/api/api0001.htm

You then import the data into a temp table. using the transfertext command
(look this up in the help). It is also assumed that you have made a import
spec.

You then append the data from the temp table to the main table, and add the
extra column during the append (or, as mentioned, simply import the data
directly into he main table..and execute a update for all empty trans no to
the file name selected.

 

[Mark Carlyle via AccessMonster.com]

I am importing the info with the transfertext command. I have it setup with
the DIR() and a while loop to get all the files in the directory.

I have assigned the filename to a variable already. I have tried to assign
the variable to the fieldname; however it tells me it cannot find the control.


Here is my code

Private Sub Command15_Click()
Dim path As String
Dim myfile As String

path = "C:\path\"
myfile = Dir(path & "*.csv", vbHidden) 'gets first txt file in path specified

Do While myfile <> "" 'will cause to loop through all txt files in path

DoCmd.TransferText acImportDelim, , "transactions", path + myfile, -1
DoCmd.OpenForm "transactions subform1"
[TrustAcctNumber] = myfile
myfile = Dir 'grabs next txt file
Loop
End Sub

it crashes when I try to assign myfile to [trustacctnumber] the error I get
is cannot find control | in reference...

 

[Mark Carlyle via AccessMonster.com]

I figured out my problem in the code mentioned above...

thanks for all the help.