By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Import Multiple Text files into a single table

P: 6
Hi all,

How can i import text files with same specefication into one table

I should be able to select multiple text files and import them automatically in the table.

The import specefication for all of them is the same.

Thanks for your support

BR,
vvasude2
Mar 6 '12 #1
Share this Question
Share on Google+
5 Replies


100+
P: 759
Create two tables with the structure you need.
Use first table for import a text file at a time and the second one to keep all imported data

Something like this:
Expand|Select|Wrap|Line Numbers
  1. Do
  2.     Remove information from the first table (Delete query)
  3.     If the last text file has been imported Then
  4. Exit Do
  5.     End If
  6.     Import (next) text file in first table
  7.     Append first table to the second one (Append query)
  8. Loop
Mar 7 '12 #2

P: 6
I have about 160 files that i need to merge and it would be not feasible to import them one at a time.

Do you happen to know the code, where i can choose an entire folder and the contents of the file gets merged based on the import spec i define
Mar 7 '12 #3

100+
P: 759
This code find all files with a certain extension in one of my PC folder:
C:\Documents and Settings\Camy\My Documents\Downloads

Hope this is what you are looking for.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub Test() 'How to apply
  4. Dim FolderPath As String
  5.     FolderPath = "C:\Documents and Settings\Camy\My Documents\Downloads"
  6.  
  7. Dim FileExtension As String
  8.     FileExtension = ".*" 'All files in folder
  9. '    FileExtension = ".txt" 'All text files in folder
  10. '    FileExtension = ".zip" 'All zip files in folder
  11.  
  12. Dim FilePath As String
  13.     FilePath = FilesInDirectory(FolderPath, FileExtension)
  14.     Do While FilePath <> ""
  15.         FilePath = FilesInDirectory
  16.         Debug.Print FilePath
  17.     Loop
  18. End Sub
  19.  
  20. Public Function FilesInDirectory(Optional Folder As String, Optional FileExtension As String) As String
  21. Static FolderPath As String
  22. Dim FullPath As String
  23.     FullPath = Folder & "\*" & FileExtension
  24. Dim NameOfFile As String
  25.     If Folder <> "" Then 'First apply
  26.         FolderPath = Folder
  27.         NameOfFile = Dir$(FullPath)
  28.         If NameOfFile = "" Then
  29.             MsgBox ("No file mutched criteria in this folder")
  30.             FilesInDirectory = ""
  31. Exit Function
  32.         End If
  33.     Else
  34.         NameOfFile = Dir$
  35.         If NameOfFile = "" Then 'No more files
  36.             FilesInDirectory = ""
  37. Exit Function
  38.         End If
  39.     End If
  40.  
  41.     FilesInDirectory = FolderPath & "\" & NameOfFile
  42.  
  43. End Function
Mar 7 '12 #4

P: 11
When you say you want to merge them, what are the rules for merging. It may be simpler to load each file into a separate table and do the merge using SQL
Mar 7 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
vvasude2:
Do you happen to know the code, where i can choose an entire folder and the contents of the file gets merged based on the import spec i define
No. There is no such command. The pseudo-code provided in post #2 is the closest you'll get to how it can work (and the logic therein is fundamental and solid). What are you saying is wrong with that? Are you expecting this to be done for you?
Mar 7 '12 #6

Post your reply

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