Translation of a piece of data during an import (TransferText).

[WillMiller]

Hi,

I'm currently attempting to write a small Access Database which carries out the following tasks :

1. Imports Submission files (of a text variety) to a table using TransferText
on a daily basis (many files into 1 table)

2. Imports Reject Files files (again of a text variety) to another table using Transfer Text. (again, daily, many files into 1 table)

3. Run a query which reports all submissions that haven't had a matching rejection.

(I've done a small amount of moving of files around and renaming when archiving, but that's not particularly relevant to my question).

Now the tricky part (well for me at least!). The files which I'm importing for step 2 above contain a date field, which is essential to the matching process that I need to carry out in step 3. The date format that is received is a Julian date without the year of the format NNN - eurghh. For example January 15th 2007 would be represented as 015 (as would any January 15th!).

What I need to do is one of the following :

reformat/translate this as part of the DoCmd.TransferT xt as it's imported
(I don't think this is possible!?)

or add another field which represents the date in a more usable format eg DD/MM/YY during the import process. (not sure how I'd do this)

or Add a command at the end of my import process which runs through each of the records and adds the relevant date field whilst copying it to the full repository table.

Thanks for taking the time to look at this!

Full code for step 2 is below this is driven through a button :

Private Sub Command12_Click ()
On Error GoTo bImportFiles_Cl ick_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFolderPath = "C:\TESTDB\Reje ct\"
Set objFS = CreateObject("S cripting.FileSy stemObject")
Set objFolder = objFS.GetFolder (strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If InStr(objF1.Nam e, "msg") > 0 Then
'Variables used to create and modify the file extension
Dim objFileSystem
Dim objFile
Dim strFileCopy As String
Dim strFileCopy2 As String
Dim intExtPosition As Integer

'Create an instance of the FileSystemObjec t to access
'the local file system
Set objFileSystem = CreateObject("S cripting.FileSy stemObject")

'Use the GetFile method to return a File object corresponding to the
'file in a specified path.
Set objFile = objFileSystem.G etFile(objF1)
intExtPosition = InStr(objFile.N ame, ".")
If intExtPosition > 0 Then
strFileCopy2 = Left(objFile.Na me, intExtPosition - 1)
Else
strFileCopy2 = objFile.Name
End If
strFileCopy = strFileCopy2 & ".txt"

'Create a copy of the file with a .txt extension
objFile.Copy strFolderPath & strFileCopy, True

DoCmd.TransferT ext acImportFixed, "XYZ Export Specification", "AccRejects ", strFolderPath & strFileCopy, False

MsgBox strFolderPath & objF1.Name & " loaded"
Name strFolderPath & objF1.Name As "C:\TESTDB\Reje ct\Archive\" & objF1.Name 'Move the files to the archive folder
Kill strFolderPath & strFileCopy

End If

Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Cl ick_Exit:
Exit Sub

bImportFiles_Cl ick_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Cl ick_Exit

End Sub

[MMcCarthy]

Have a look at this tutorial as I think it is more suitable to your needs. Let us know if there is anything you don't understand or need help on.

Import csv (Comma Separated Value) File


Mary

[ADezii]

Hi,

I'm currently attempting to write a small Access Database which carries out the following tasks :

1. Imports Submission files (of a text variety) to a table using TransferText
on a daily basis (many files into 1 table)

2. Imports Reject Files files (again of a text variety) to another table using Transfer Text. (again, daily, many files into 1 table)

3. Run a query which reports all submissions that haven't had a matching rejection.

(I've done a small amount of moving of files around and renaming when archiving, but that's not particularly relevant to my question).

Now the tricky part (well for me at least!). The files which I'm importing for step 2 above contain a date field, which is essential to the matching process that I need to carry out in step 3. The date format that is received is a Julian date without the year of the format NNN - eurghh. For example January 15th 2007 would be represented as 015 (as would any January 15th!).

What I need to do is one of the following :

reformat/translate this as part of the DoCmd.TransferT xt as it's imported
(I don't think this is possible!?)

or add another field which represents the date in a more usable format eg DD/MM/YY during the import process. (not sure how I'd do this)

or Add a command at the end of my import process which runs through each of the records and adds the relevant date field whilst copying it to the full repository table.

Thanks for taking the time to look at this!

Full code for step 2 is below this is driven through a button :

Private Sub Command12_Click ()
On Error GoTo bImportFiles_Cl ick_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFolderPath = "C:\TESTDB\Reje ct\"
Set objFS = CreateObject("S cripting.FileSy stemObject")
Set objFolder = objFS.GetFolder (strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If InStr(objF1.Nam e, "msg") > 0 Then
'Variables used to create and modify the file extension
Dim objFileSystem
Dim objFile
Dim strFileCopy As String
Dim strFileCopy2 As String
Dim intExtPosition As Integer

'Create an instance of the FileSystemObjec t to access
'the local file system
Set objFileSystem = CreateObject("S cripting.FileSy stemObject")

'Use the GetFile method to return a File object corresponding to the
'file in a specified path.
Set objFile = objFileSystem.G etFile(objF1)
intExtPosition = InStr(objFile.N ame, ".")
If intExtPosition > 0 Then
strFileCopy2 = Left(objFile.Na me, intExtPosition - 1)
Else
strFileCopy2 = objFile.Name
End If
strFileCopy = strFileCopy2 & ".txt"

'Create a copy of the file with a .txt extension
objFile.Copy strFolderPath & strFileCopy, True

DoCmd.TransferT ext acImportFixed, "XYZ Export Specification", "AccRejects ", strFolderPath & strFileCopy, False

MsgBox strFolderPath & objF1.Name & " loaded"
Name strFolderPath & objF1.Name As "C:\TESTDB\Reje ct\Archive\" & objF1.Name 'Move the files to the archive folder
Kill strFolderPath & strFileCopy

End If

Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Cl ick_Exit:
Exit Sub

bImportFiles_Cl ick_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Cl ick_Exit

End Sub

This should solve the Julian NNN conversion. I've made a couple assumptions on Table and Field Names. Any questions, please ask.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, T As Integer
Dim dteStartDate As Date
 
dteStartDate = #1/1/2007#
 
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblConvertJulian", dbOpenDynaset)
MyRS.MoveLast: MyRS.MoveFirst
 
Do While Not MyRS.EOF
  MyRS.Edit
    MyRS![Date] = DateAdd("d", Val(MyRS![Julian] - 1), dteStartDate)
  MyRS.Update
    MyRS.MoveNext
Loop
 
MyRS.Close

[WillMiller]

Thanks for the responses, they certainly help.

ADezii, yours looks to be of particular interest. I think I can get away with the date addition as long as it uses 01/01/current year for the date on which it's adding.

Couple if questions though :

T As Integer (what is this being used for is it needed for the While loop?)

MyRS.MoveLast : MyRS.MoveFirst
what does this do - from searchin MoveFirst moves to the first record of the record set but why the first bit and what does the colon represent?

If I wanted update the records into a different table (and delete them from the current table - ie move them) how would I do this?

Many Thanks,

Will

[ADezii]

Thanks for the responses, they certainly help.

ADezii, yours looks to be of particular interest. I think I can get away with the date addition as long as it uses 01/01/current year for the date on which it's adding.

Couple if questions though :

T As Integer (what is this being used for is it needed for the While loop?)

MyRS.MoveLast : MyRS.MoveFirst
what does this do - from searchin MoveFirst moves to the first record of the record set but why the first bit and what does the colon represent?

If I wanted update the records into a different table (and delete them from the current table - ie move them) how would I do this?

Many Thanks,

Will

1. Sorry, the Variable T is not being used in this context, and can be deleted.
2. MyRS.MoveLast:M yRS.MoveFirst - This double move really isn't needed in this scenario, but if you need an accurate RecordCount for certain Recordsets, you can only get an accurate figure if you traverse the Recordset (manually move the Record Pointer to the end and then back.
3. The Colon is just a Command separator and enables you to place several statements on the same line. I apologize since this is really not a recommended practice, but since the statements are short, I used this syntax.
4. If you wish to perform this operation on another Table, just replace the Table Name in the Recordset Line to your own as illustrated in the code below:
   

   Set MyRS = MyDB.OpenRecordset("tblConvertJulian", dbOpenDynaset)
                          'REPLACE WITH
   Set MyRS = MyDB.OpenRecordset("<your table name>", dbOpenDynaset)