Importing Multiple Text files using VBA


Hello,

I am after some help on writing soem VBA to import multiple text sheets into 1 access table. The files are in the same format and they will all be in one folder and there will be nothing else in folder.

Any help on this would be great as I need to import lots of files on a rolling basis.

Cheers


Sponsored Links:



Hi,

I want to import multiple text files to Access 2007 Table called "Data_Import" using VBA code. The text file names are not same so I the are located in a folder. So I should be able to select them through windows. When i import the text file, the first Row of the table should contain the file path along with the file name and the second row should just contain the file name and the data from the text file should be appended after that. The same procedure should be followed for subsequent text files.

Any Help would be appreciated.

Thanks
rk




Hello, I'm very new to Access and writing code (this is day one). I am trying to import multiple text files from one folder into a table and add a new column that identifies the file name.

I've tried to adapt the following code to my needs, but Access 2010 apparently does not like FileSearch. I apologize if my question is too simple for this forum.

Function ImportCSVFiles() Dim FilesToProcess As Integer Dim i As Integer Dim bArchiveFiles As Boolean Dim sFileName As String Dim sOutFile As String Const TOP_FOLDER = "H:Test" 'adjust folder name to suit Const ARCHIVE_FOLDER = "H:TestImported" 'adjust folder name to suit Const DEST_TABLE = "tblUsers" 'change to suit Const IMPORT_SPEC = "CSV_Import_Spec" 'change to suit Const PATH_DELIM = "" 'set to False if you DON'T want to move imported files to new folder bArchiveFiles = True 'the FileSearch object lets you search a folder and, optionally its subfolders, 'for files of a defined type. It loads the names of all found files into an array, 'which we can use to import those files. With Application.FileSearch .NewSearch .LookIn = TOP_FOLDER .SearchSubFolders = False 'we only want to search the top folder .FileName = "*.csv" 'change this to suit your needs .Execute FilesToProcess = .FoundFiles.Count 'check that files have been located. If not, display message and exit routine. If FilesToProcess = 0 Then MsgBox "No files found, nothing processed", vbExclamation Exit Function End If For i = 1 To FilesToProcess 'import each file DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, _ .FoundFiles(i), True 'archive the imported files If bArchiveFiles Then 'code for archiving imported files... sFileName = StrRev(Left(.FoundFiles(i), Len(.FoundFiles(i)) - 4)) sFileName = Left(sFileName, InStr(1, sFileName, PATH_DELIM) - 1) sFileName = StrRev(sFileName) sOutFile = ARCHIVE_FOLDER & PATH_DELIM & sFileName & " " _ & Format(Date, "yyyymmdd") & ".csv" FileCopy .FoundFiles(i), sOutFile Kill .FoundFiles(i) End If Next i End With End Function 'The StrRev function reverses a text string. We are using it here to simplify 'extracting the file name: once the full path is reversed, we can pull out everything 'to the left of the first path delimiter. Reversing this string gives us the file name. 'Note: VBA has a StrReverse function that you can use instead of this custom function. Function StrRev(sData As String) As String Dim i As Integer Dim sOut As String sOut = "" For i = 1 To Len(sData) sOut = Mid(sData, i, 1) & sOut Next i StrRev = sOut End FunctionThanks




hi,

I knw how to import a delimited text file in VBA if I know in advance what the delimiter will be. The reason you have to know in advance is that you have to save the specification so that you can then use it in the

	Code:
	Docmd.TransferText acImportDelim, SavedSpecName

command. So, for instance, if you know you will be importing a file that is comma-delimited, then you can save a specification that contains that information. My problem is that I am not going to know in advance what the delimiter will be. The only thing I can think of is to anticipate as many possibilities as I can and save them all as separate specifications. This just does not seem very satisfactory to me, so can anyone suggest a better way?

I thought I could solve it if I could work out how to save a specification using VBA but I can't work out how to do that.

Thanks in advance,
Chris




Hi Everyone-

I am using Access 2003, and I am trying to import several text files from the same location. Each text file will have a different name. For example:

From location:
Y:Incentive Data ReportsRejectsUPLOAD

I want to upload the following files:
HBACK-Rejects_06162010.txt
HBACK-Rejects_06172010.txt
HFAIR-Rejects_06182010.txt
HFair-Rejects_06222010.txt

The files are all formatted the same way, and I've created an import specification called (DM_Rejects). I want the files to be uploaded to the Table named "LoadFiles" in Access.

The file names will change daily.

Does anyone have any suggestions?

Thank you!




Hi,

I am tryig to import many text files into MS Access. They are all tab delimited and have NO column headers, but they all have varying number of columns and data types.

Rather than using the Import wizard for every single text file, is there a way to write some code in VBA to upload the file, which I can then just edit as neccessary for each type of file?

Thanks for your help.




HI

Basically, I was hoping that I can import text files into SQL server via MS Access Project (adp) by using (SQL Server 7.0) DTS package to minimize performance overhead.

Spec:

Users have a number of folders containing various text files. They need to create reports from the data of any set of text files within a folder. I used the wizard to generate my vb script. I copied the code from the bas (text) file and pasted it in a new module in Access then I customized it so that I can pass one text file at a time to the main (Sub). I tested it first with one single file and it works just fine but it does not read all files when I loop through all files in a folder.

I have it working with mdb file using docmd.TransferText but it is sooo slow

Thanks guys!




I searched this forum and found vba code to import multiple text files into ONE Access table, but I need mine to be kept separate. Also, I didn't understand the copyrighted code for that posted procedure. Any suggestions? Thank you.




I am trying to import a text file into Access but I want to let the user select where the file is located. In the "TransferText" macro the File Name is mandatory. Is there a way around this? Can the DoCmd.TransferText command get around this? If so, how do you do this? Help!




Hi Guys,

I've had a bit of an issue with my DB, lost all of the records in a table (about 100000 records gone). Luckily, I have all the data in text files (.SIF files, standard interchange format). SInce they were generated by the DB, I though it would have been easy to re-import them, but I am a bit stumped, since access does not recognise them as text files

I have attached an example of the file as a guide, the header information is not really important (the first line of data is line 8).

Is there any way I can grab this data back easily from these files?

Is there a way to set-up a VBA script to automatically grab this data from every file in the folder?

Thanks for any help you might be able to give..

Forgot to mention, the format does not change between files, only the number of lines varies.

Forgot to mention, the format of the file does not change at all, the layout stays exactly the same. Just the number of lines changes.




Hi all,

I just started using VBA in access and I hope someone can give me a hand with the following:

I have multiple text files in a folder that I need to import in a table, but I want to include the filename (excluding the txt extension) in the first field of my table. I have code that allows me to batch import the file contents, but I can't figure out how I can include the filenames in the first field of my table.
The code is below:
----------------------------------
Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path As Variant

' get the DB path
DB_Path = "C:IWCBirdSTATF1Files"
FileCriteria = "C:IWCBirdSTATF1Files" & "*.txt"

' create field with path and filename to import'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0

' Import each file that meets the criteria 'File for*.txt'
While NextFile ""

' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = DB_Path & NextFile

' Import file into table
DoCmd.TransferText acImportDelim, "3711_0 Import Specification", "Attendance Import", ImportFile, False

' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "Attendance Import"
End Function
-----------------------------
Thanks!




Hi,

I am pretty new to access and I am trying to import a custom text file in VBA, I have tried searching but most articles discuss delimited text files.

I'm using Access 2003

Text File contents

Line 1: Authorisation 1
Line 2: -----------------
Line 3: A,00.0000001.Y
Line 4: B,00.0000002.Z
Line 5: Authorisation 2
Line 6: ------------------
Line 7: C,00.0000003.F
Line 8: Authorisation 3
Line 9: ------------------
Line 10: C,12.0000005.B

There can be multiple numbers within each section i.e Line 3,4,7,10

I would like to import the data into a table like the following;
A,00.0000001.Y | Authorisation 1
B,00.0000002.Z | Authorisation 1
C,00.0000003.F | Authorisation 2
C,12.0000005.B | Authorisation 3
And so on - ignoring the --------- separator.

I hope someone could kindly help as I cannot figure out where to start

Many thanks

Spike




Hi everyone. I'm a new Access user. I've outgrown my Excel database and am converting to Access.

I need help linking a CSV file using VBA code. I've been successful linking the CSV file using the "External Data" > "Import & Link" > "Text File" wizard. I've also been successful in making a button Macro import the CSV file. I was hoping to use the "Convert Macros to Visual Basic" function to see what the underlying VB code is, but for some reason the button is grey and cannot be clicked.

My Macro uses the "ImportExportText" Action. What is the equivalent VBA code for this action? I cannot figure it out.

I've search Google and tried using

Code: DoCmd.TransferText acImportDelim, "", "Table1", "C:Winxptesting.csv", True, "" ' where table1 is your table but that didn't work.

Someone please help.

Thanks in advance.




Hi,

I cannot find a solution to these two issues.


1) My Table "UNIVERSITIESOtherName" is made of University names in non-english characters:

ID UniversityOther
87 "Երևանի ""Հայբուսակ"" Համալասարան"
190 جامعة الخليج العربي
191 الجامعة الخليجیة
192 جامعة البحرين
237 Белорусский государственный экономический университет

When I export them to multiple text files with the script below (Source: Larry Larson) I get ?????????? characters instead of the original ones.
I guess I have to somehow tell the vba script to set the character to Unicode UTF-8 and/or to use a previously saved Export Specification Schema.

Here the Function code I am using slightly adapted from Larry's one



	Code:
	Function Save_to_file()
Dim rst        As DAO.Recordset
Dim x          As String
  
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM UNIVERSITIESOtherName")

    Do Until rst.EOF
        x = FreeFile
        Open "C:temp" & rst!ID & ".txt" For Append As x
        Print #x, rst!UniversityOther
        Close #x
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing

Save_to_file_Exit:
    Exit Function

End Function

The question is what is the simpliest way to set the above VBA script export function to unicode UTF-8 characters or make it use a pre-saved Export Specification Schema?

2) The second minor problem is that whenever i run the above Export Function module, content is appended to the previous one instead of being replaced
So I get ID.txt files containing several lines of question marks:

???????????
???????????
???????????

How can I modify the above script so that it replaces the existing content instead of appending to it?

Thank you very much for your time and suggestions.

FF




Hi,

I have about 100 text files of the following format:

Name: John Doe
Address: 123 Main Street
Zip: 55555

Name: Jill Doe
Address: 234 Clyborne
Zip: 66666

I need to import all these text files into a Single Table in Access. Obviously, the table would look like this:
Name Address Zip
John Doe 123 Main Street 55555
Jill Doe 234 Clyborne 66666

Due to my limited knowledge in Access, I don't know how to get this done nor use the right terminology to search (I tried in this forum but was unsuccessful).

Would appreciate if anyone can point me in the right direction to solve this problem please. Thanks!




I am trying to import multiple fixed length text files that don't have a .txt into Access 2007. I am able to get this to work one file at a time but I need to load a high volume of files from the same folder.

I currently get an error that the Object doesn't support this property or method.

This is how far I have gotten:

Public Function ImportNonTXT()
Dim fs, Fn, FLength, Fext, FDot, FOrig
Dim strFileName As String
strFileName = Dir("C:import")
Do While strFileName ""

Set fs = CreateObject("Scripting.FileSystemObject")
Set Fn = fs.getfiles(strFileName)

' This holds the file's original name for rename later.
FOrig = Fn.Name
' Get the length of the file name.
FLength = Len(Fn.Name)
' Set this value = to the last four characters of the file name.
Fext = Right(Fn.Name, 4)
' Set this = to the first character.
FDot = Left(Fext, 1)

' If there is a dot in the fourth from the last position...
If FDot = "." Then
'... and the extension is not .txt.
If Fext ".txt" Then
' Remove the extension from the file name.
Fn.Name = Left(Fn.Name, (FLength - 4))
' Add the .txt to the file name.
Fn.Name = Fn.Name & ".txt"
End If
Else
' If there is not a dot in the fourth position
' add the .txt extension.
Fn.Name = Fn.Name & ".txt"
End If

' Transfer the file to a new table.
DoCmd.TransferText acImportFixed, "kr 1075 01rt", "usatest", "strFileName", False, ""
' After the file is transferred, rename it back to
' its original name.
Fn.Name = FOrig
strFileName = Dir()
Loop
End Function




Data supplied in tab delimited text file. Unfortunately there are more than 255 columns in the text file and can't directly import into an MS Access table (max 255 columns). Now trying to parse the text file (using VBA) to remove unwanted columns - idea being that once below 255 columns I can load into table. I'm okay with parsing on tab character, chr(9), but I've hit problem if data record contains comma character. VBA INPUT verb appears to consider comma to be a variable delimiter.
Does anyone know a method to read in tab delimited text file so that each record is a single variable regardless of content?
I've got one idea and that's to first import the file into table comprising one memo field. Then I can parse the data records on that table but was hoping to find a method to process the text file directly and not have intermediate table.
Any thoughts appreciated.




Hi all,

Done a bit of research on this already, but not sure if I'm heading in the right direction. I regularly get a text file that is exported from another system that comes out in rows with records repeating one after the other. The way the other system exports data can't be altered so I've got to find a way of getting massive text file row records into columns to import into an access database. The data comes out as shown below: -

Name U78E-8 .net
Advertising Rates Full Page Colour GBP 2850.00
Mechanical Data Type Area 265 x 215mm
Bleed Size 286 x 238mm
Trim Size 280 x 232mm
Col Length 265mm
Film Digital
Page Width 215mm

Name U78E-5 3D World
Advertising Rates Full Page Colour GBP 2289.00
Mechanical Data Page Width 215mm
Type Area 265 x 215mm
Col Length 265mm
Film Digital

Name U78E-9 Computer Arts
Advertising Rates Full Page Colour GBP 2470.00
Mechanical Data Page Width 190mm
Film Positive, right reading, emulsion side down
Trim Size 297 x 210mm
Type Area 272 x 190mm
Col Length 272mm
Screen 60 lpc


The only records I need to be listed in separate colums are name code i.e. U87E-8, advertising rates as currency only and mechanical data - type area, e.g. 265 x 215 without the mm. So they'll appear delimited in some way

U87E-8 2850.00 265 x 215
U78E-5 2289.00 265 x 215
U78E-9 2470.00 272 x 190

What I've read so far rules out the transfer text function as the data isn't column delimited for it to work (correct me if I'm wrong). Some threads have mentioned parsing text files using VBA to extract the data, but I'm not entirely sure how to do this, but I'll kick it off here for your amusement if nothing else.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim buffer As String

Set dbs = CurrentDb

Open "c:testdata.txt" For Input As #1
While Not EOF(1)
Line Input #1, buffer
If InStr (1, buffer, "Name") = 1 then ' trying to identify when a certain word occurs then parse the data after it, but not sure how to yet


Is there a program, vba code or template that anyone can point me to to help. Any assistance as always is greatly appreciated. Thanks




Hiii
I am having one query which gives output from multiple tables based on the relation ship between these tables.
Two of these tables are imported from text files.To import text files I have made a module whose code is like this:
Function Import_CSPS()
stDocName = "CSPS"
stSpecName = "CSPS Import Specification"
stFileName = Form_Init_Mod.csps_location
DoCmd.DeleteObject acTable, stDocName
DoCmd.TransferText acImportFixed, stSpecName, stDocName, stFileName
End Function
But as soon as I want to import text file it says table can't be deleted because it is in relationship with other table.
This haapens due to the code
DoCmd.DeleteObject acTable, stDocName
Is there any other way to import text file into the table without deleting it so that relationship remains active during imorts.




Hi all

I regularly want to link a number of CSV files. Using VBA, I can import specified file names using

DoCmd.TransferText acLinkDelim, , "Baseline_001", "C:Baseline_001.csv"
DoCmd.TransferText acLinkDelim, , "Baseline_002", "C:Baseline_002.csv"
etc.

In order to extend this, I created an Excel table, called "ListOfTables" that contains names of all the CSV files under the single field "List". I planned to amend the above code using a loop to link one CSV file at a time, but have been unable.

Thanks in advance

r12wan