adding file name to each record on import

[ApplePirate]

I have a module that i use to import a number of files from a set location.
Is there a way to tag the filename onto each record as it is imported so i know where the record came from.
this is the code im using
-------------------------------------------------------
Function importXLS_files()
Dim FileName As String

FileName = Dir("//File Loading Area/" & "/*.xls&quot
If FileName <> &quot;&quot; Then
Do Until FileName = &quot;&quot;
DoCmd.TransferSpreadsheet acImport, &quot;8&quot;, Table1, _
&quot;//File Loading Area/&quot; & FileName, False, &quot;&quot;
FileName = Dir()
Loop

End If
MsgBox (&quot;Import Complete!&quot


End Function
-------------------------------------------------------

Never ever, bloody anything, ever

 

[dpimental]

Of course you can.

I would have to see more of the code to determine the least &quot;bloody&quot; means of doing it.

But, you are obviously passing a variable with a file name to a sub routine that imports the files?

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[ApplePirate]

This is the code as is...

'------------------------------------------------------------
' import_GTC_files
'
'------------------------------------------------------------
Function importXLS_files()
Dim FileName As String
'Original command line
'DoCmd.TransferSpreadsheet acImport, &quot;8&quot;, &quot;Forms![File Importer]![Location]&quot;, _
&quot;//Supdox02/data/shared/Developer Partnerships/File Loading Area/Spare4GTC/&quot; & FileName, False, &quot;&quot;

FileName = Dir(&quot;//Supdox02/data/shared/Developer Partnerships/File Loading Area/&quot; _
& Forms![File Importer]![Location] & &quot;/*.xls&quot
If FileName <> &quot;&quot; Then
Do Until FileName = &quot;&quot;
DoCmd.TransferSpreadsheet acImport, &quot;8&quot;, Forms![File Importer]![Location], _
&quot;//Supdox02/data/shared/Developer Partnerships/File Loading Area/&quot; _
& Forms![File Importer]![Location] & &quot;/&quot; & FileName, False, &quot;&quot;
FileName = Dir()
Loop

End If
MsgBox (&quot;Import Complete!&quot


End Function
-----------------------------------------------------------
-----------------------------------------------------------

i have a form with a drop down list to choose from. The list is of five folders which all contain excel files from different sources, the files are in different formats and are imported into seperate tables to be cleaned and then appended to one single table which is then worked by a number of users.
It hasnt been a problem until now, but ive noticed a few duplicates from seperate sources and need to query the data.

 

[ApplePirate]

any help on this is much appreciated

Never ever, bloody anything, ever

 

[MichaelRed]

hmmmmmmmmmm,

some VERY general observations - and NO specific commentary re the code, so if you aren't interested in the generic observations just skip this.


I try to practice &quot;safe db&quot;. In part, this means not co-mingling un validated data with what is known to be good. This, in turn, means that 'un-trusted' sources are imported via some temp structure, some bit of V&V is performed and ONLY THEN is the information included in (e.g. moved to) a valid data set. In this process, I would do the transferspreadsheet action to a TEMPORARY table, hopefully one with the fields alread &quot;nammed&quot; and the spreadsheet having column names (a header row). The Temp table would then already include a field for &quot;srcName&quot; and ONE of htre V&V steps would be to update the field to the source documnet name (or other appropiate identifier). Of course, the alternative is to just do the import, then programatically add and populate the SrcName field. In either case, continue with the V&V, identifying and isolating records with unacceptable info. On completion of V&V, append the &quot;good&quot; records to the &quot;real&quot; data set(s), return the &quot;Bad&quot; items tro the source / sender for review, repair and re-submittal, and proceede tothe next import.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places