471,084 Members | 821 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,084 software developers and data experts.

Import Multiple Text files into a single table

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

Mar 6 '12 #1
5 13555
759 512MB
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
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
759 512MB
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
  3. Public Sub Test() 'How to apply
  4. Dim FolderPath As String
  5.     FolderPath = "C:\Documents and Settings\Camy\My Documents\Downloads"
  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
  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
  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
  41.     FilesInDirectory = FolderPath & "\" & NameOfFile
  43. End Function
Mar 7 '12 #4
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
32,342 Expert Mod 16PB
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.

Similar topics

2 posts views Thread by Chris Murphy via DotNetMonster.com | last post: by
2 posts views Thread by ManningFan | last post: by
3 posts views Thread by emaghero | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.