automatically importing data

[IANGRAND]

Each day i receive a standard .txt which has the same headings and data format. The title of the file is the current day in the following format ie 04_DECEMBER_2003. Ideally i would like to automate the importing of the table into a table.

Does anyone know a way of automatically importing .txt files?

Cheers

Ian

 

[nicsin]

Hi Ian,

What is your development environment? Is it access/vba or vb6?

 

[SQLBI]

Ian,

You can use the TransferText method of the DoCmd object.

DoCmd.TransferText...

Here's an extract form the Access 2000 VBA help files.

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[IANGRAND]

Leighmore, nicsin

Looks good, but the problem is that i would have to manually alter the table name and the name of the file that the code was looking up.

If i set a textbox on a form to indicate the current day, is there anyway i can link this date to the specificationname, and set the date as the table name?

 

[nicsin]

We have a similar situation. every night a file is being generated in a network folder having the present date. my program looks at that folder and tries to import all the files. I've also created another file which holds tha dates that are inserted succesfully in the db along with their size. So as it tries to import the files again, it first checks this file and if the date is already inserted it disregards it and moves on. subsequently only the new file(s) are inserted. It may incur some overhead but (in our case) there might be a change in a previous date's file so I want to import it again.

 

[IANGRAND]

can you show me the code for the automated import?

 

[nicsin]

sure. The only difference from your situation is that we are importing .csv files and they don't have any headers. Here it goes:


File = Dir(FileName)
Do While File <> &quot;&quot;
S = Left$(File, InStr(File, &quot;.&quot - 1)
TheDate = Right$(S, 2) & &quot;/&quot; & Mid$(S, 5, 2) & &quot;/&quot; & Left$(S, 4) 'the name of the file is in yyyymmdd format so it needs some formating before compared to From Date and ToDate. s holds the fileName without the extension

If TheDate >= FromDate And TheDate <= ToDate Then
Call RecallExport(Left$(S, 8), FileLen(File))
If DFlag <> 0 Then
Call DelDate(TheDate)
DoCmd.TransferText acImportDelim, &quot;type&quot;, &quot;table&quot;, File, 0
End If
Call SaveExport(Left$(S, 8), FileLen(FileName))
End If
File = Dir
Loop



RECALLEXPORT checks the second file I mentioned and sets the DFLAG to 0 if it matched a previous import. SAVEEXPORT saves the inserted filename and size to that file. DELDATE deletes the old import of that file from the db.