Batch Import External Data?

**avenue** · 01-29-2007, 03:03 AM

I have several thousand text files, each with three columns of numbers. My goal is to convert each of these to a dbf file with three distinct columns (which is why simply renaming each with .dbf at the end doesn't work.) I can do this for an individual file by using the "Import External Data" Tool in Excel, selecting the "Fixed Width" option (which divides the figures into three distinct columns), and then saving the file as a dbf. I need to do this for several thousand files, however, so I can't do each by hand. I would really appreciate it if anyone had any idea how I might be able to automate this task. Thanks very much.

**mudraker** · 01-29-2007, 03:46 AM

Record a macro as you import the data set the column withs and then save file as requird (if savinging is required at this stage).

Post a copy of the recorded macro and we can assist you into modifying the macro to repeat the process looping through the files.

Add extra details as to files location are the files to be appended to previously imported data.

**avenue** · 01-29-2007, 03:55 AM

Thank you! Below is the macro I recorded. This was the complete procedure for the first file, El_1. The other files are El_2, El_3,..., through El_2000. I really appreciate the help.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/28/2007
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\points\El_1" _
, Destination:=Range("A1"))
.Name = "El_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileFixedColumnWidths = Array(16, 16)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ChDir "C:\points"
ActiveWorkbook.SaveAs Filename:= _
"C:\points\El_1.dbf" _
, FileFormat:=xlDBF4, CreateBackup:=False
End Sub

**mudraker** · 01-29-2007, 07:33 AM

I have modified the macro to loop through the files in "C:\points\"

The macro adds a new workbook > gets a file name to import > imports the data to the new workbook > saves the workbook as a dbf file > clears the imported data then repeats the process from the get a file name to import until all files have been processed

A couple of concerns I have about the code.

Does the text files use normal name convention ending in .txt - this I could not determine from your discription or the recorded macro. I have used code assuming that the file names end in .txt

As I can not fully test the completed code please ensure you have a backup copy of your text files.

Please Login or Register  to view this content.

**avenue** · 01-29-2007, 12:47 PM

Thanks very much for the code. Unfortunately, it gives me a VB error: "Object Required". Do you know what this might be referring to?

(By the way, the files are not actually textfiles--they are unrecognized ascii files--so they have no .txt ending, so I just clipped off that part of the the sFile line. This doesn't seem to be the issue, however, because I renamed some of the files with .txt and tried your original code on them, but got the same error message.)

**mudraker** · 01-29-2007, 04:45 PM

Sorry I had misspelt command I used Workbook.Add when it should have been Workbooks.Add

I have also modified the code for file names that have no extention eg .txt

Please Login or Register  to view this content.

**avenue** · 01-29-2007, 06:21 PM

I'm really sorry, there now seems to be a different problem. This time, with the new code, I get the error message:

System Error &H80070057 (-2147024809). The parameter is incorrect.

I looked through the Microsoft help on this error message (http://support.microsoft.com/kb/171217) but I don't see how any of what they write is relevant to this macro....

**mudraker** · 01-29-2007, 09:01 PM

It would make it easier for me if you could post a copuple of the text files. That way I can fully test the code before posting it.

Can you tell me which line of code is highlighted when that error message is displayed.

What version of Excel are you using? 97 I assume by the microsoft website details.
I am using 2003 and it could be some of the commands that I have used are not available in your version

**avenue** · 01-29-2007, 09:25 PM

I'm actually using Excel 2003. Also, no line is highlighted when the error message is displayed. I've attached the first three files, zipped (El_1, El_2, El_3.) Thanks so much for your continued help.

**mudraker** · 01-30-2007, 02:06 AM

Macro appears to work correctly on my PC

I have inclosed 3 dbf zipped files for you to verfy they are correct.

Based on your posting @ 09:21 AM today we need to check some Visual Basic Editior settings on your pc

From the Tools Menu select References
I have 4 items ticked - the macro appeared to run ok with only the 1st 2 items ticked (which I can not untick)

Visual Basic For Applications
Microsoft Excel 11.0 Object Libary
OLE Automation
Microsoft Office 11.0 Object Libary

If you have any others ticked I suggest you note which ones they are and then Untick them then try running the macro again

I will also suggest a couple of changes to the macro - these are enhancements only

After the Loop comand and befor the End Sub command insert these 2 lines of code.

Please Login or Register  to view this content.

**avenue** · 01-30-2007, 03:17 AM

Well...guess I'm an idiot. I'd been putting the code into one of the sheets rather than into the entire workbook. Can't imagine a more novice mistake...

Anyway, it works great now. Thank you so much for all the work--it was extremely helpful and is much appreciated.

**mudraker** · 01-30-2007, 07:21 AM

Glad to hear all is now well

**avenue** · 02-03-2007, 05:11 AM

Mudraker or anyone else:

I am now trying to do a similar procedure, in which I load each dbf, delete a few rows and columns, and save it as a .csv. I recorded a macro and tried to follow a similar procedure as above to loop it (my attempt is below) but I run into the following problem: the original dbf file remains even after it is saved as a .csv, so the macro keeps reloading test_final_1.dbf rather than moving on to test_final_2.dbf and others. Should I do this instead as a for loop? And how would I do a for loop if some of the numbers of missing (i.e. the files may be test_final_1.dbf, test_final_2.dbf, test_final_5.dbf, test_final_8.dbf, etc.). I'd really appreciate any suggestions for how to get this to work. Thanks!

Sub Macro2()
Dim sFile As String
Dim spath As String

spath$ = "C:\points\"
sFile$ = Dir(spath & "test_final_*.dbf")
Do Until sFile = ""
Workbooks.Open Filename:= _
spath & sFile
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
spath & Left(sFile, Len(sFile) - 4) & ".csv", FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWindow.Close
Loop
End Sub

**mudraker** · 02-03-2007, 07:29 AM

If you use sFile$ = Dir(spath & "test_final_*.dbf") before a loop you need to add a DIR command inside the loop to get the next file

When adding the Dir command inside a loop use only sFile$ = Dir
Never use sFile$ = Dir(spath & "test_final_*.dbf") as you will not get the next file

Please Login or Register  to view this content.

**avenue** · 02-03-2007, 05:10 PM

Thank you!

**emte** · 10-16-2012, 08:54 AM

Hello,
I have very similar need, but maybe simpler even. I would like to import into excel external data from various tab delimited texts files from a folder and its subfolders. Every imported text should land into a new sheet.
Name of the new sheet: The best would be if each of these newly created sheets had name same as the name of the each originating text file.

Text formatting while insert is tab as separator, all columns formatted as text.

Please Login or Register  to view this content.

I hope you can help too

Thanks in avance for the interest!

**Cutter** · 10-16-2012, 01:54 PM

Hello emte, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.