473,320 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
4 1814
MMcCarthy
14,534 Expert Mod 8TB
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
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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

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

Similar topics

1
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have...
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
2
by: N. Graves | last post by:
I'm planning to develop a process to import data from an external flat file to a new table automatically. As usual I'm having a hard time getting started on the solution because of the Access's...
2
by: ms | last post by:
In Access 2000, I am using... 'Import Raw text to stageBMIDLog table. DoCmd.TransferText acImportFixed, "BMIDLog Link Specification", "stageBMIDLog", file ....to import raw records into a...
4
by: khutch | last post by:
Not that up on MS Access. I understand that the TransferText command can be used to import csv files into a database. Question: Does the text file have to mirror the alignment of the table columns...
0
by: DataFreakFromUtah | last post by:
Hello! No question here, just a procedure for the archive. Search critera: count records imported count data imported count number of rows imported count number of records imported record import...
0
by: Typehigh | last post by:
HELP! I don't know what happened. I have an MS_Access 2000 application that imports 20 data files; some Excel, some fixed-length txt and some tab delimited. I use "DoCmd.TransferSpreadsheet . ....
3
by: Bill | last post by:
I'm trying to use a command button to the same thing as going to the file menu and doing a Get External Data command to make the import process easier for some end users. I'm using Access 2k, I...
0
by: Sean Howard | last post by:
I have a strange problem linking tab delimited text files in Access 2000 (I am running Windows XP), please try this and let me know if I am going mad. Step 1. Create the tab-delimited text...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.