I have text files placed into a folder like AIN28_01012014_1355.txt
I need to import these but the files have the date and time added to the name which i cannot stop AIN28_ will always be the same is there a way to import the file just off AIN_*.txt or something like that
I was thinking i could use file copy and change the name but that needs to know the exact names doesn't it...
i am trying to download an excel document from a url and the import it into a table i can successfully download the file to my desktop but when i try to import it i get the below error the Microsoft office access database engine cannot open or write to file 'C:documents and settingsJHalliweDesktop' it is already opened exclusively by another user, or you need permission to view and write its data..i have checked and the excel book is not open ( i think its trying to open while its still downloading is there away around this
Code:
Dim strUserName As String
strUserName = Environ("UserName")
Dim FolderLoc As String
FolderLoc = "C:Documents and Settings" & strUserName & "Desktop"
Dim StFile As String
Dim MySelect
MySelect = Forms!FrmIndex!Text134
[code]....
I have been searching all over on how to create an import button on a form in access 2010 where I can set which table it should copy the data into but the user could locate the import file.
NOTE: the reason being I have locked all the ribbons...
If necessary I could make a specific import template file which would have all the headings locked...
There is a manual process I'd like to automate. Instead of the user manually importing an external file into a table, I want a function to grab the most recent file in a particular directory. Is that possible?For example, I have a table called Input, and I want to append the file I just saved on my network, which in this case is G:Files2014TA_140928_011007.txt. The two ways to know which file to use is that the date is part of the filename (TA_ YYMMDD_ 999999. txt), and also the Date Modified attribute.
I am aware that if the user needs to work the files out of date order, he/she cannot use this code.
We receive a daily csv file from another database and we are trying to automate this process as much as possible.
At the moment, the other system will extract any new records, save a file as importddmmyyyy.csv in a specified location e.g. c:importpendingimportddmmyyyy.csv and then make a call to open access giving access the file path as follows
masaccess.exe "C:mydatabase.mdb" /nostartup / Excl/ cmd Filepath="c:importpendingimport09082013.csv"
I have configured access to automatically open a form, "ImportForm" when it opens, and I have configured this form to automatically run an event procedure on loading "importdailyfile".
My problem lies in writing this procedure. Basically I need it to run the Transfer Text command (I have already create an import specification)
DoCmd.TransferText acImportDelim, "myimportspec", "table to append the data to", filepath, true
The problem I am facing is that as the filepath changes on a daily basis given the naming conventions (which i cannot change) and my knowledge of VBA s pretty basic, I don't know how to make the procedure pick up the filepath that the other system is providing in the call to open access.
They are receiving e-mail with some data that they want to transfer to access database to track. The one think they can do is copy past, copy paste. That is a lot of data to copy and paste. The date looks like this format
Field1
Text1
Field2
Text2 (text2 can be more than 255 char)
Field3
Text3 (text3 can be more than 255 char)
Field4
Text4
Field1
Text1
Field2
Text2 (text2 can be more than 255 char)
Field3
Text3 (text3 can be more than 255 char)
Field4
Text4
and so on.. It can be 50 records
I am thinking they can copy this to the text file. Then the code form Access grabs the text file and imports to access table with format below.
Field1 Fied2 Field3 Field4
Text1 Text2 Text3 Text4
I have a daily file to import. If I hard code the entire file name my code works. My problem is getting around the daily name change of the file.The owner has an automated process that creates the file so the name every day is slightly different due to the timestamp. Here's an example
Today's file is "mydailyfile_20140317_1035.xls"
Tomorrow will be "mydailyfile_20140318_1033.xls"
I can account for date using "sFileDate = Format(Now(), "yyyymmdd")".The hour/minutes is never the same.is there a wildcard solution??
I need to import a html file automatically BUT my file has many tables in it, when I am doing the importation it asks about which table I want to import, the thing is that I always want every tables. The number of tables is variable. VBA code so it can have a looping which says to import every table in my HTML file?
View 1 Replies View RelatedI'm trying to import a pipe delimited text file into a table. I can import the entire table using the following code, but I only get one column of data (the entire data set in one column). If possible I would like to import with the columns defined or if not possible use some code for a function similar to text to columns.
Code:
DoCmd.TransferText acImportDelim, , "tblTest", "C:Work2015PPVMasterData.txt"
We have a function that will allow a user to navigate to a text file and import it to a database. I've been asked to make it import ALL the text files from all sub-directories in the directory IF they've never been imported before (based on FileName and LastModifiedDate). I've set up a table to track what's getting imported (tbl_ImportHistory). I'm thinking what I need to do is use the previous coder's GetFileName function in a recursive loop removing the navigation request and adding the comparison and then, if it imports the file, the name and date need to be added to the tbl_ImportHistory table. But I'm completely unsure of how to do it.
I'm posting the GetFileName function below. I believe once I've got it modified the rest of the code can be left intact as it just parse's and loads whatever file is selected in the GetFileName function.
<code>
Function GetFileName() As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
Dim vrtSelectedItem As Variant
[code]....
I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).
Code:
Public Sub ImportTextFile()
' to use the ADODB.Recordset, be sure you have a reference set to ADO
Dim rst As ADODb.Recordset
Dim rst2 As ADODb.Recordset
Dim strFile As String
Dim strInput As String
Dim varSplit As Variant
Dim intCount As Integer
[code]...
I have a form which i use for a user to select an excel file they want to import and then click a cmd button to import the file into a table which works fine, however i want to append a date into a date field from an unbound txtbx before the file is imported so it will look something like;
id;date;excel info;excel info;excel info.
hello,
I would like to automate something presently done on a one-by-one basis. Here: a number of text files(containing data) are to be exported into an MS Excel file, with each text file to occupy a different worksheet. Presently, the idea is to use the Data/import external data/import data feature of MS Excel for importing the text files one-by-one into newly created worksheets(within the same workbook).
I would appreciate some advice on how to go about creating a useful MS Access application to achieve the above. I have checked the available Macros in MS Access, but I could not find one to suit my purpose. can anyone pls assist, on how I can get started?
Tokunbo
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
hello everyone, i really need some detailed help as the deadline is approaching and I need to find a solution for this. Any help would be greatly appreciated
I currently have a batch file that ftps a text file from a Red Hat Linux Server to my W2k C:. I would like to make another command in the batch file that imports this text file into an existing access table. I would like the text file to repopulate the table everytime it is imported. I do not want the data added on to the existing data in the table.
Thank you for your time and insight.!!!:)
I have the following piece of code for importing the .csv files from a selected folder and then renaming them into 'imported & filename' if succesful and 'failed & filename' if import failes for whatever reason (bad formating, etc.).
The problem is that the first time it encounters a 'bad' file after another, instead of going again to the Error handler, the command
Code:
DoCmd.TransferText acImportDelim, , "Activitate", filename, True
gives me the default MS Access error, namely 2391. I would like it to go the the Error_handler again ang follow the course of actions and rename the files into 'failed' and 'imported'.
Here's the code:
Sub ImportActivitate()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String, sFullName As String, sFilename As String
DoCmd.SetWarnings False
[code]....
Also, how can I make the routine stop cycling through the files after I renamed them all?
I have MANY scanned image (.tif) files in multiple folders based on certain criteria. I need to find a way to import only the names of these (.tif) files into a table or even into an excel spreadsheet. Since they are scanned images you can not do a simple copy / paste.
I would appreciate if anyone has any ideas how this could be mastered
I have SQL code that would import an Excel file as follows:
Code:
Str = "SELECT [Ed_TEST$].* INTO [tblAct_Import_File] FROM [Ed_TEST$] IN '" & impFile & "' [Excel 8.0; HDR = YES;];"
cn.Execute Str
I do not want to use do not want to use "DoCmd.TransferSpreadsheet" I am finding do to rounding some figures are braught in a little off. However, I have found that if I use code.
I was working on project that involved writing the data into Access database using a C program. The insert query execution was taking very long, so I decided to write to a .csv file and then import it to one of the desired tables. This worked very fast as compared to directly writing to the DB. Now I want to automate this process. The user should specify the file name at the command prompt and I want to call a script so that the script automatically imports the contents of the .csv file into the access DB. I already have connection established to the DB. All I am looking for is the script that can automatically import the .csv file into access DB. Please help me out. :confused:
View 2 Replies View RelatedI have an excel file worksheet(player info sheet)that the user would input information. I then copy that info into another worksheet(player info) in the data fields that I have defined in Access. I then open up my Access database and do a file-get external data-import. I then select my excel file and the worksheet named "player info". I get the import fine but there is a table that gets created that is called: 'Player Info Sheet$'_ImportErrors. I cannot figure out why. Any help would be appreciated. Thanks.
View 4 Replies View RelatedHello All
Hopefully someone can help me here.
I have been given a .sql file which I want to upload into an access database.
When I open the file this is the heading in notepad (which I hope helps)
SQLyog Community Edition- MySQL GUI v6.05
Host - 5.0.37-community-nt : Database - sft_provisiontool
************************************************** *******************
I am not sure now though how to import this into access.
Can anyone help?
Thanks
Mav
:mad: Hi,
I m really struggling now with this problem.
I am importing a csv file with addresses and Post Codes on into a Properties Table. However, when I import the csv file, there is another field in the table called Area. I want the import routine to somehow use a lookup table that will contain all Post Codes and what Area they are in and then insert the relevant Area in each record in the properties table. I have tried using DLOOKUP in an update query but this just inserts the first Area in the lookup table into all records in the Property Table.
Any help would be much appreciated.
Lee
A curious problem, I import CSV data into an AC97 table and this has worked without any problems for years. However we have recently taken on a new member of staff and one of the fields being loaded are his initials 'SF' and this results in Import errors. I tested any other letters 'ME','MF' etc. and no problem - is 'SF' some kind of reserved word ?.
Simple solution - call him 'SEB' not 'SF', but I wondered if anybody else has come across this ?
Hi.
I create SQL queries on my wesite and I put them in files for the user (.sql, but any file type would be possible). I would like them to easily import them in their MS Access.
I can copy the SQL-line and paste it in Acces' Design View -> Query menu -> SQL Specific -> Select Union. But I think that is too complicated. I would just like to import them from a file to make it easier for the user of my site.
Is that possible?
hello guys!
need your help, i need to run the import file in a switchboard. is this possible? thanks in advance!
Hi
I have a log file that records an action in following format.
50144021 12-17-2004 21:00:44 Mail Sent Subject: Test file TO: bert@xxxxxx.com
I want my database to look into this file and return the date/time of the last send in the log to match up with a record in one of my tables that has following fields: "email","last sent", "subject". ( The match will be done on the email address)
I can therefore look at each record and identify when each email address was last sent the file
Any help would be much appreciated
Thanks
Mat