By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,695 Members | 1,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,695 IT Pros & Developers. It's quick & easy.

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

P: 3
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.TransferTxt 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_Click_Err

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

strFolderPath = "C:\TESTDB\Reject\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If InStr(objF1.Name, "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 FileSystemObject to access
'the local file system
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

'Use the GetFile method to return a File object corresponding to the
'file in a specified path.
Set objFile = objFileSystem.GetFile(objF1)
intExtPosition = InStr(objFile.Name, ".")
If intExtPosition > 0 Then
strFileCopy2 = Left(objFile.Name, 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.TransferText acImportFixed, "XYZ Export Specification", "AccRejects", strFolderPath & strFileCopy, False

MsgBox strFolderPath & objF1.Name & " loaded"
Name strFolderPath & objF1.Name As "C:\TESTDB\Reject\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_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit

End Sub
May 22 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
May 25 '07 #2

ADezii
Expert 5K+
P: 8,692
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.TransferTxt 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_Click_Err

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

strFolderPath = "C:\TESTDB\Reject\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If InStr(objF1.Name, "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 FileSystemObject to access
'the local file system
Set objFileSystem = CreateObject("Scripting.FileSystemObject")

'Use the GetFile method to return a File object corresponding to the
'file in a specified path.
Set objFile = objFileSystem.GetFile(objF1)
intExtPosition = InStr(objFile.Name, ".")
If intExtPosition > 0 Then
strFileCopy2 = Left(objFile.Name, 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.TransferText acImportFixed, "XYZ Export Specification", "AccRejects", strFolderPath & strFileCopy, False

MsgBox strFolderPath & objF1.Name & " loaded"
Name strFolderPath & objF1.Name As "C:\TESTDB\Reject\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_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, T As Integer
  2. Dim dteStartDate As Date
  3.  
  4. dteStartDate = #1/1/2007#
  5.  
  6. Set MyDB = CurrentDb
  7. Set MyRS = MyDB.OpenRecordset("tblConvertJulian", dbOpenDynaset)
  8. MyRS.MoveLast: MyRS.MoveFirst
  9.  
  10. Do While Not MyRS.EOF
  11.   MyRS.Edit
  12.     MyRS![Date] = DateAdd("d", Val(MyRS![Julian] - 1), dteStartDate)
  13.   MyRS.Update
  14.     MyRS.MoveNext
  15. Loop
  16.  
  17. MyRS.Close
May 25 '07 #3

P: 3
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
May 29 '07 #4

ADezii
Expert 5K+
P: 8,692
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:MyRS.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:
    Expand|Select|Wrap|Line Numbers
    1. Set MyRS = MyDB.OpenRecordset("tblConvertJulian", dbOpenDynaset)
    2.                        'REPLACE WITH
    3. Set MyRS = MyDB.OpenRecordset("<your table name>", dbOpenDynaset)
May 29 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.