Txt File Importing Question...

[marubal21]

Hi,
I use a madule to import multiple txt files... Is there a way to import a file and at the same time extract part of the file name to populate a column?

All the files i'm working with have the following format:
FileNameMM-DD-YY.txt
ex.
XYZ07-19-10.txt
XYZ07-20-10.txt
XYZ07-21-10.txt

When importing them is there a way to extract the date portion and have it populate a date column within the table? Unfortunately the file lacks the date in the data itself.

 

[vbaInet]

It shouldn't be much of a problem just use the Right() function:

Right([fieldName], 8)

Link:

http://www.techonthenet.com/access/functions/string/right.php

 

[marubal21]

Thanks,
question tho, how do i incorporate that into my current code i'm using for the importing of multiple files. Below i copied the code i'm using for the importing which works fine just need help on extracting the date from the file name.


Public Sub subImport()
On Error GoTo Err_subImport

Dim stDocName As String
Dim fs As FileSearch
Dim ifn As String
Dim sql As String
Dim today As String
Dim fso As Scripting.FileSystemObject
Dim oktogo As Boolean
Dim specname As String
Dim repdate As String
Dim myfile As Scripting.TextStream
Dim i As Long
Dim y As Integer
Dim ShortFn As String

specname = "Import Specs"
'DoCmd.SetWarnings False
'sql = "INSERT INTO tbl_temp_Import"

'DoCmd.RunSQL sql 'Empty Temp Table



DoCmd.SetWarnings False
oktogo = False
ifn = CurrentProject.Path & "\Imports\"
Set fs = Application.FileSearch
With fs
.LookIn = ifn

.FileName = "*.txt"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then

For i = 1 To .FoundFiles.Count

ShortFn = Right(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
DoCmd.TransferText acImportFixed, specname, "tbl_temp_Import", .FoundFiles(i), True
y = y + 1

Next i
Else
MsgBox "Please ensure that the source file is present and try again" & vbCr _
& "Required file location: " & vbCr & ifn, vbExclamation + vbOKOnly, "Input File Missing"

Exit Sub
End If
End With

MsgBox "Import complete. " & y & " files Imported", vbOKOnly + vbInformation, "Import Complete"

Exit_subImport:
' Turn warning messages back on
DoCmd.SetWarnings True

Exit Sub

Err_subImport:
MsgBox Err.Description
Resume Exit_subImport

End Sub

 

[vbaInet]

I thought you mentioned in your other thread that FileSearch wasn't working in your version (because it's deprecated in 2007)?

 

[marubal21]

it doesn't work on 2007, but i found a pc that wasn't updated and figured i use it till i fix the code to work on 2007, but i realized that regardless i need a way to extract the date from the file name..

 

[vbaInet]

Do you realise that doing that will appear a 1000 times if you have 1000 records? And I'm talking for each file imported.

And even if it was possible the process of your import would need to change.

 

[marubal21]

i know that the date would copy over to each record found in the file, but it's something i need. I would expect there to be some changes to my import code, i'm just asking for some help in actually being able to copy the date portion.

 

[vbaInet]

Had a thought that you may not actually need to change the import routine much, but you need to ADD a new field to your tbl_temp_Import table.

Here are the steps:

1. Create the new field in tbl_temp_Import table, datatype - Number
2. Set the Default Value of this field to 1.

3. Include this in your variable declarations:

dim db as DAO.Database

4. Replace the For section with this:

set db = Currentdb
For i = 1 To .FoundFiles.Count

    ShortFn = Right(.FoundFiles(i), Len(.FoundFiles(i)) -  InStrRev(.FoundFiles(i), "\"))
    DoCmd.TransferText acImportFixed, specname, "tbl_temp_Import",  .FoundFiles(i), True
    y = y + 1

    ' Update the field
    db.Execute "UPDATE tbl_temp_Import SET [[COLOR=Blue][B]FieldName[/B][/COLOR]]  = [[COLOR=Blue][B]Fieldname[/B][/COLOR]] & '" &  right(replace(FoundFiles(i), ".txt", ""), 8) & "' WHERE [[COLOR=Red][B]NewField[/B][/COLOR]]=1;"
    
    ' Reset the value of the new field to Null
    db.execute "UPDATE tbl_temp_Import SET [[COLOR=Red][B]NewField[/B][/COLOR]]=Null  WHERE [[COLOR=Red][B]NewField[/B][/COLOR]]=1"
    
    DoEvents

Next i
db.close
set db = nothing

Else

Fill in the field names highlighted in the code. FieldName is the name of the field you want to add the date string to.

You may need a reference to DAO if it's not already there.

 

[marubal21]

thanks for the help, i'll try it out when i get to work, and let you know the results.

 

[vbaInet]

Alrighty. See how it goes.

 

[marubal21]

Thank you for your help, it works like a charm, it actually even imports faster too...

 

[vbaInet]

Thank you for your help, it works like a charm, it actually even imports faster too...

Great! Glad to know it worked for you

 

[marubal21]

Let me bug you one last time, i was testing the imports and i noticed that when it imports its missing the 1st record from every txt file? It looks like it was doing this before you help, any idea what could be causing this?

 

[vbaInet]

In the Docmd.TransferText line, there's a True there. If it's True it means it will use the first line as the Field Names in the table. So play around with the idea of having the field names in your text file instead.

In the code window, click the function key F1 to open help, type Docmd.transfertext to learn more.

 

[marubal21]

okay, thanks once again!

 

[vbaInet]

You're welcome