Modules & VBA :: Importing Multiple Text Files?

Dec 17, 2013

Currently I have a code that imports a single text file into a table without problems.

I need to be able to selecte multiple text files and import them at once.

Here is my current working code.....

Code:
Private Sub Import_Click()
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Text File (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
DialogTitle:="Please select new file...", _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
DoCmd.TransferText acImportDelim, "Import Specs", "Data Table", strSaveFileName, True, ""

View Replies


ADVERTISEMENT

Importing Multiple Text Files

Aug 1, 2006

I am trying to import several txt from a directory.

The following code is on a button in a form:

Private Sub ImportData_Click()

Dim myfile
Dim mypath

mypath = "G:FinanceAccountingRoyalty2006exports3rd QTR 06JUL 06 est"
Do
myfile = Dir(mypath & "*.txt")
DoCmd.TransferText acImportFixed, "import_data", "tbl_import_tables", mypath & myfile, False, ""
myfile = Dir
Loop Until myfile = ""

End Sub

The problem is this causes a infinite loop

Can anyone help?? Thanks

PS This code was from previous posting, where the transfer was excel files.

View 5 Replies View Related

Modules & VBA :: Importing All Records From Multiple Files (ACCDE)

Jul 25, 2013

I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).

I am using a sub that feeds a string to another public sub as follows:

Code:
Private Sub cmdImport_Click()
Dim strFolder As String 'source folder path for import
Dim strFile As String 'individual file path
strFolder = GetFolder(Environ$("USERPROFILE") & "Documenti")
If Len(strFolder) <= 1 Then Exit Sub

[code]....

View 3 Replies View Related

Modules & VBA :: Importing Text Files Into Access Within Date Range

Mar 17, 2014

I want a string of code that allows me to import a series of delimited text files into access using VBA. I have the first bit of code down:

Code:
DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "msfs3109data1shareeveryoneprorep ranhistDelivery" & currentdate & ".txt"

The text files hold data for a specific days work. Each day has its own text file. I would like to be able to import the text files within a specific date range, specified by the user. Below is the total code i have for my form button:

rivate Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdatex
Dim count As Integer
count = 0

[Code] .....

View 12 Replies View Related

Modules & VBA :: Import Multiple Fix Width Text Files - Eliminate Multiple Headers And Footers

Aug 1, 2013

I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.

Also, it appears in the old Database Table once imported as:

J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date

If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.

View 3 Replies View Related

Importing Text Files

May 1, 2006

Hi!
I'm trying to import csv files into an Access 97 database, but the filename is different each time otherwise I would just use a commandbutton with the transfertext macro.

I've been searching for help to browse to a file and keep coming back to the same few pages but not being a programmer I can't figure out how to use the code.

Can anyone help me with simple instructions on how to do this?
Thanks.
Anna.:confused:

View 5 Replies View Related

Importing Text Files

Feb 7, 2008

All,
I have a database that I import text files to create tables. I ve been doing this awhile but now I have to pass it on to someone else to do and are trying to find a better way. this is what I've been doing: I import the text file into a new table. Then I copy and paste the data to the corresponding table. I know I can do an append query to do this but its about 12 text files I have to import into 12 different tables and I didn't want to take the time. I then run a query to combine and sum data from two different tables. The problem is that I have to copy and paste because if I import the text files directly into the corresponding table, the query don't run. I've tried change the field data type in the import wizard but it still doesn't work. My table datatype fields are: field1-text field2-number and field3-number. There is not field for number in the wizard but I use double as a field size in the design table so I select this in the wizard. I don't know how to resolve this other than spending time to create an append query or cut and paste. Please help
Thanks

View 1 Replies View Related

Importing Multiple Csv Files Into Table

Jul 19, 2005

I have been given the task of setting up a database which will store data which I will receive on a month basis, I have managed to import some csv files however, I will be receiving well over 200 csv files each month. Is there a way to import these multiple file.

The filename save structure is as follows: areacode-Jul 05data.csv

areacode represents each different area.

below is a copy of the csv file.....

View 2 Replies View Related

Importing Fixed With Text Files

Jun 7, 2005

Dear All,

I need some help. I am writing a database for my new and small company. I receive internet orders from my ISP in an email. I want to find a simple way of automatically importing these emails into my "Customer Information" table.

The issue is that I have no control on the format of the email I receive from my ISP. The data is fixed width and stores the information horizontally rather than in columns. For example:

Quantity : 1
Price : GBP 199.00
Delivery : GBP 5.00
Total : GBP 204.00

I have been exploring the GetData option and using the advance settings to align fields. It seems (and I am not an expert user of Access) that the GetData function relies on the import source to be aligned in vertical columns rather than horizontally. This makes mapping the fields almost impossible.

Does anyone have a clever work-around? Either in Access or manipulating the data before it goes into the database?

Many thanks, Matthew (matthew@byatt.com)

View 4 Replies View Related

Importing SAP Text Files And Join Help

May 27, 2005

I imported a table into Access from a SAP generated text file. The integrity of the data is fine after import. I then created a new table from the original that has the exact same amount of records and has the same unique field, which is MATERIAL. The problem I have is when I join the two tables in a query, Access returns approx 140,000 records. I am doing a regular join, what could be the problem? Is it possible the table data types are not formated correctly, b.c I imported them all as text so Access would import all lines.

I look forward to anyone's response.

TIA

Ease

View 1 Replies View Related

Importing Text Files Option Missing

Mar 3, 2006

Im trying to work with access 97, to import a text file into access. All the books i have looked in and web pages found say, file - get external data - import, then in "files of type" drop down you can select text.

I dont have the option to do this.

I have Uninstalled office 97, reinstalled it. with all the extras.
I have gone into the help and added all the valupack but still nothing.

Thanks :confused:

View 2 Replies View Related

Importing Large Text Files From Excel Into Access

Dec 1, 2012

I have a data file I am importing into MS Access 2010. One of the fields is a large text field. When i import that field into Access the text is getting cut off. How do I get the full text field to import without cutting off?

View 6 Replies View Related

Modules & VBA :: Importing Excel Files

Jul 4, 2015

Access Version 2010
Excel Version 2010

I have 200 excel files in folder C:UsersLburchDocumentsHistoric DataTedan Data in Excel Worksheets - CopyAAA - Copy.The files only have one worksheet and the column format are the same. Worsheet name is different for every file though.Headings in first row.write a macro to import into 1 Access table so I don't have to do it manually.

View 14 Replies View Related

Modules & VBA :: Importing Excel Files - How To Add Additional Columns

Dec 31, 2013

I need to add two additional columns for each imported spreadsheet, which are not in the original files.

First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.

Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".

Code:
Dim strFile As String
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Data;"
' Set file directory for files to be imported
strPath = "C: est"

[Code] ....

View 14 Replies View Related

Modules & VBA :: Import Multiple Files To Multiple Tables On Button Click

Sep 20, 2014

I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.

At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:

Function GetAllFiles()
Dim fd As Object
Dim strFilter As String
Dim lngItems As Long

Const msoFileDialogOpen As Long = 3
Const msoFileDialogViewDetails As Long = 2

[Code] ....

View 4 Replies View Related

Importing Multiple Lines Of Text

Aug 27, 2007

Good morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.

1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338

I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.

View 4 Replies View Related

Import Multiple Text Files Into Single Table

Dec 18, 2004

Hello everyone!

Here is my problem:
I have an Access database that contains a table with three columns (A & B & C). Field A is for code values and field B is for the value for that particular code. Field C holds the case number. I have a folder that contains the information for over 5000 case numbers. Each case number needs to be placed inside of the table. Considering there are 5000 case numbers, I dont want to use the import wizard 5000 times.

Furthermore, the text file only has two columns (for A and B).

I am trying to create a VBA that will append rows to the table by going into each file and placing the information into the table. The first row for each file contains the case number. So when the VBA runs, it should open the text file get the case number from the first row (field B on the text file) and place that in the case number field of the table for the entire file; and then repeat this action for all of the files in the entire folder.

Any help that can be provided will be greatly appreciated.

Thanks.

View 13 Replies View Related

General :: Report To Multiple Text Files Based On Filtered Value

Jun 14, 2014

I created report "rpt" with a few subrpts. I would like to export this report to word by value in column M_AGENDA_KOD.

M_AGENDA_KOD column has about 370 rows, so i do not want to export each manually.

So I would like to have each M_AGENDA_KOD value as separated word file named by that M_AGENDA_KOD value.
Is that even possible?

View 14 Replies View Related

Queries :: Import Multiple Unstructured Text Files Into A Database

Nov 24, 2014

I have an excel spreadsheet, that has a long list of names and unique numbers (of medical lab results)

I have another folder which has many text files (the actual lab reports), each of which has a file name that corresponds to one unique number in the excel spreadsheet.

How can I import the content of the text files into a single memo field (e.g. "LabResultContent") in the original datasheet?

View 4 Replies View Related

Modules & VBA :: Importing Text Using Text Wizard

Jun 2, 2015

I have a list of 30 files I need to import into my access database. The files in the folder are named

1. Txt
2. Txt
3. Txt
4. Txt
5. Txt
6. Txt
7. Txt
8. Txt

When I did the first import I did it by doing an import text file, located the file 1.txt. The Import Text Wizard opened and I picked Fixed With so I could break out the information myself.After dividing that information I hit advanced and changed the field name to match what they should be. Next, then it ask me to save Import Steps and I said yes.

First: how I can use the saved import to import this file again using the text wizard
Second: how can I set it so it imports a numbers of files, maybe one or maybe all 30?
Third: how difficult would it be to all the file name in the first or last column?

View 2 Replies View Related

Modules & VBA :: FTP Multiple Files Using Loop

May 15, 2014

Basically what I am trying to accomplish is uploading multiples file automatically into server. The code I am trying to use works great if the user wants to upload a single file manually because the code prompts you to choose the file and I am wondering if there is a way to tweak the code. here is the code I am using

Code:
Private Sub Form_Load()
Dim objFTP As FTP
Dim strfile As String

[Code].....

View 3 Replies View Related

Modules & VBA :: Printing To Multiple PDF Files

Jul 8, 2013

My report has tens of pages and I need to save each page as separate file with ID as filename. PDF prints with ID but all in one file. I use this code.

Option Compare Database
Private Sub tisk()
Dim cesta As String
Dim kod As String
Dim Sql As String
Dim rs As Recordset

[Code] ....

View 5 Replies View Related

Creating Archive Table For 30 Days To Contain All Data Imported From Multiple Text Files?

Oct 30, 2014

i'm in the process of creating an Access database to import data in text files and then export the data as fixed width text files, this is now working fine

My next step is to be able to setup an "Archive" Table which will hold all the data i have imported across multiple import proceudres for the last 30 days, i then want to be able to "De-Dupe" any files i import against this to ensure i never load duplicate data.

View 1 Replies View Related

Modules & VBA :: Recordsets - One Access Report To Multiple Files

Sep 9, 2014

I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).

This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.

StateAbb = Me.State
PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY")
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRptName As String

[Code] ....

View 14 Replies View Related

Modules & VBA :: Import Data From Multiple Excel Files

Apr 24, 2014

I have a requirement to create a piece of vba that will open all xlsx files in a folder one at a time then import the data in a range (sheet1!A1:G14) into a table named Weekly Input.

View 4 Replies View Related

Modules & VBA :: Operate Macro On Multiple Files In A Directory

Jul 21, 2015

I have contract notes files in .xls format towards sale of equity shares from the brokerages. I have to do some editing in these files like deleting some rows, adjusting width of columns and inserting date of contract.

a) Contract-A.xls shows original file.
b) Contract-B.xls shows modified file
c) VBA macro code used for deleting rows and modifying column width is shown in modify_contractnotes.xlsm file.
d) Presently I open the file and copy and paste the contents on the macro-enabled workbook sheet . After macro operation I transfer back the contents by copy and paste to a master file(Contract-C.xlsx) and clear the contents in the macro enabled workbook to make it ready for another operation.
e) It is time consuming process for around 60 files per month.
f)After this operation I run RDBMerge add-in to consolidate and normalize all these files in a directory. RDBMerge smoothened out merged cells also.

Is it possible to have a macro-enabled workbook open all the time and the contract files be opened one after other and macro contained in macro enabled workbook by modifying its code suitably operate on these contract files.

Files are attached in modify_contractnotes zipped folder.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved