Importing multiple text files to spreadsheet

Just wondering if anyone might know if there's a way to automate this
problem:

I have 200 text files that need to be imported to 1 worksheet on Excel.

	- each file should be imported to 1 column in the Excel worksheet
- the 'next' file imported to the next column
	- thus after the import, there should be 200 filled columns 
		- starting with zero columns filled

??? Any ideas?

==========================

FYI: essentially, i'm trying to get information from the text files -
which represent 1 record - to a database, & i'm using a spreadsheet to
do this coz after the import, I will still have to adjust the size of
each column to align the fields (each line in a text file = 1 field,
but some lines are missing from some text files). So if anyone can
suggest another method to do this, by all means......

ps. I actually want each file to fill a row, not a column, but this
seems easy enough w/ a "Paste Special" & "transpose" function.....


-- 
lanwu
------------------------------------------------------------------------
lanwu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15017
View this thread: http://www.excelforum.com/showthread.php?threadid=266348

0
10/5/2004 7:42:26 AM
excel 39879 articles. 2 followers. Follow

1 Replies
700 Views

Similar Articles

[PageSpeed] 9

Ianwu,

The following VBA sub will open each .txt file in folder C:\MyFolder in
turn, and import each line in each file horizontally, so no need to
transpose afterwards. Open a new workbook, right-click on a sheet tab and
select View Code; when taken to the VBA window, select the file you want to
save the code in in the upper left panel (the new workbook, or, Personal.xls
so it is globally available), and go to menu item Insert > Module. Pste the
code below in the right hand side panel, and change the folder from
C:\MyFolder to your actual folder name. Run the code (F5 in the VBA window
or Tools > Macro > Macros while in the spreadsheet) and it's done.

Sub Import_Text_Files()
    Dim fs, f, f1, vLine
    Dim i As Long, j As Long
    i = 0
    Range("A1").Select
    fldr = "C:\MyFolder\"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.getfolder(fldr)
    Set f1 = f.Files
    For Each f2 In f1
        filenam = fldr & f2.Name
        If Right(filenam, 4) = ".txt" Then
            j = 0
            Open filenam For Input As #1
                Do Until EOF(1)
                    Line Input #1, vLine
                    ActiveCell.Offset(i, j).Value = vLine
                    j = j + 1
                Loop
            Close #1
            i = i + 1
        End If
    Next
End Sub

HTH,
Nikos

"lanwu" <lanwu.1dn85b@excelforum-nospam.com> wrote in message
news:lanwu.1dn85b@excelforum-nospam.com...
>
> Just wondering if anyone might know if there's a way to automate this
> problem:
>
> I have 200 text files that need to be imported to 1 worksheet on Excel.
>
> - each file should be imported to 1 column in the Excel worksheet
> - the 'next' file imported to the next column
> - thus after the import, there should be 200 filled columns
> - starting with zero columns filled
>
> ??? Any ideas?
>
> ==========================
>
> FYI: essentially, i'm trying to get information from the text files -
> which represent 1 record - to a database, & i'm using a spreadsheet to
> do this coz after the import, I will still have to adjust the size of
> each column to align the fields (each line in a text file = 1 field,
> but some lines are missing from some text files). So if anyone can
> suggest another method to do this, by all means......
>
> ps. I actually want each file to fill a row, not a column, but this
> seems easy enough w/ a "Paste Special" & "transpose" function.....
>
>
> -- 
> lanwu
> ------------------------------------------------------------------------
> lanwu's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15017
> View this thread: http://www.excelforum.com/showthread.php?threadid=266348
>


0
10/5/2004 12:53:41 PM
Reply:

Similar Artilces:

Help: Formula put into entire row creates large file size
Hi, I have a spreadsheet where I would like one column to be the concatenation of two other columns. For example, in column C I would have =A1&B1. This excel file will be given to people and they will fill in values into columns A and B. The trick is, I don't know how many rows there will be, so I don't know how many rows in column C should have this formula. If I paste this formula into the entire column C, the excel file blows up to 3 megabytes. (It's only 15KB without this column). Are there ways for me to accomplish this more efficiently? Thanks for any help Are y...

How do I print multiple records to one publication page?
I want to merge records from a data source in access to publisher. I want 4 different records per page in postcard format. Each time I use the catalog merge feature it prints 1 record 4 times on the page. How can I fix this? You need to have one card on your screen for the merge to work. (Print preview will show the same entry on all four cards, its a bug). -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jagodie" <Jagodie@discussions.microsoft.com> wrote in message news:CF2C96BA-6A33-4AF0-9703-A692713A5EA5@micro...

Corrupt Access 2003 File
I was working on this file earlier today, I even just opened it and it was fine and all of a sudden there is a write delay to the save the file, I closed it and I try opening it again and it is corrupted. I don't get it. The message I am getting is below. What does that mean. I just finished making a lost of update to the file. The last time I backed it up was Tuesday, and I have done a lot of work to it between then and now. I don't have the time to go back and redo all that work again. Any help will be appreciated. "Microsoft Office Access has detected corruption in this ...

Importing column in comma saparated values.
Hi, This is my first post here..:) I have some values in a column in excel and I want to have them i comma saparated values. The Outout can be in any type of file as lon as I can copy it..I am sure, the experts here can solve this and hel me a great deal...Please help... Thanks in advance. -- Message posted from http://www.ExcelForum.com You could File/Save As then select .CSV ??? --- Message posted from http://www.ExcelForum.com/ If it's just one column (out of many used columns), then I'd just copy and paste into NotePad and save from there. But I'm not quite sure how y...

Querying MS Access from C# on stored file paths
Hi folks, I'm storing some particulars about file usage in my MS Access database, incl. the full paths of those files. I tried to query the database in my C# code. My idea was to retrieve from the database by means of SQL when a file was used the last time, but I only know the directory it is in, so in my code I tried to construct a WHERE clause as follows: String whereClause = "WHERE Filename LIKE '" + dirpath + "*'"; Of course I'm working on Windows and I don't want to think of portability now. Obviously, the variable dirpath contains double...

help
I have some texts files, which i want to read in each line, and then write back each line to a new text file. So for example, I want to read in the 2 lines below: "C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8 "",0,0,0,318.592,83.04552 I store each line of data in the following vector: std::vector<CString> FileData; So FileData[0] =3D ""C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8" FileData[1] =3D """,0,0,0,318.592,83.04552" (the data is in the vector correctly) Then I go to write the data to a new text file... FILE ...

Converting XLS file to QIF or to OFX
How do I safely and securely convert an excel file (xls) to a QIF or OFX file? "dreamchaser" wrote: > How do I safely and securely convert an excel file (xls) to a QIF or OFX file? In Excel, save the file to CSV and ustilise iCreateOFX Basic from: http://icreateofx.co.uk/Convert-CSV-to-OFX to convert the saved CSV file to OFX. ...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

PST IMport problem
I am having problems importing data from a pst backup file. I have successfully imported outlook folders from it recently, however when I attempt to import more now, the files are not coming into my current tree, but a new personal folder is created with nothing in it. The PST file in question has a filesize of over 600Mb ! Any Help will be greatly appreciated! Thanks! For starters, importing and exporting are not recommended for PST files. You've demonstrated one of the reasons. Just open the file. -- Russ Valentine [MVP-Outlook] <neil@energycell.co.uk> wrote in message news...

Dynamic text
Hi, I would like to add a serial number to a publication I'm designing. Is there a way to get a different number (sequential or otherwise) on a publication? Thanks Brian W Mail merge, create a data file. Read the help files, it is the way to all knowledge. -- Mary Sauer MS MVP http://dgl.microsoft.com/ http://mvps.org/msauer/ "Brian W" <brianw@gold_death_2_spam_rush.com> wrote in message news:eFVmhdGUDHA.2008@TK2MSFTNGP11.phx.gbl... > Hi, > > I would like to add a serial number to a publication I'm designing. Is there > a way to get a different number...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

execute batch file does not execute all command lines
I am using the following code to execute a batch file which contains multiple commands, each on a separate line. Each command-line creates a file. What I am finding is that only some of the files are being created ( 7 out of 11 ). If I then run the batch file directly ( either by double-clicking, or pasting its path into cmd.exe window ), all files are created ( 11 out of 11 ). private void executeBatchFile(string pathBatchFile, string pathWorkingDirectory, Label outputDump) { ...

Precompiled headers and source files in subdirectory
Hello, I'm using MSVC++ 8.0 SP1 on WinXP Pro and I have a simple, app-wizard generated MFC application. It was using precompiled headers to speed up build times (as is the default), but when I tried to move the c++ source files to a subdirectory "src" to in the project/solution directory, I can't build the program anymore without disabling precompiled headers. I get these errors: error C2859: c:\coding\msvc\toolbar-demo\debug\vc80.pdb is not the pdb file that was used when this precompiled header was created, recreate the precompiled header. error C2859: c:\coding\msvc...

What's the name of the file that stores AutoComplete addresses
My Outlook had to be re-installed and I lost my contact list which is very large. I see several files in the recycle bin. Is one of them by contacts? Autocomplete cache is an NK2 file, named for your profile, often outlook.nk2. Paste the following line in the address bar of windows explorer; %USERPROFILE%\Application Data\Microsoft\Outlook and it will open to the folder where its stored. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subsc...

Opening Text Files and Re-Saving Them
Hi. I am trying to write some code which will allow a user to select a folder and then will open all of the text files in that folder and re-save them as excel files. I have sorted out the code to enable the user to select the folder they wish to use, but am looking some assistance on the looping to enable each of the text files to be opened and re-saved. Can anyone suggest how I might do this. Thanks. If the text files are all the same, you could record a macro that Opens, saves as an excel file, and closes the file. Then that recorded macro could be modified to open...

the system cannot find the file specified outlook 2003 #2
Attachments show up in emails, but when you try to open them, the messages comes up "the system cannot find the file specified", but other recipiants on same email can open theirs. ...

Importing marketing list as campaign responses
Hi there guys and thanks in advance for any light you might shed on this matter. I created a .csv file from an excel sheet and I want to import this into CRM as campaign responses. When I try to do so, and after mapping all fields from the file to correspondent fields in CRM, it just doesn't import anything. It seems like it is going to import, but then, just doesn't do nothing. And of course, nothing shows as campaign responses. If I import the .csv file as leads, it works, so I guess the import file is not corrupted or has any bad data. Anyone knows what the problem might be? ...

Multiple Series Pie Charts
Is there a way to display multiple series pie charts in excel? Was thinking the Pie Charts could be displayed next to one another. Thanks, Chad Chad - Make a pie chart, not as a separate chart sheet, but located on a worksheet (a blank one, if you like). Make another and position it on the worksheet next to the first. Etc. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Chad BATES wrote: > Is there a way to display multiple series pie charts in > excel? Was thinking the Pie Charts could be displayed > next to...

Multiple chioce in drop-down list
Hello, everyone I have to create a field on the form with a drop-down list where user can select more than one value. It seems like standard insruments (creating attribute with picklist type) are not working cause it's not allowed to choose more than one value. If you have ideas about that, please assist -- ---------------------- regards, Ilya Milshtein MBS Master, MBSCP, MCP Softline International www.softline.ru As you noticed, multi-value picklists are not an option in crm 3.0. What you can do is create a new custom entity instead of a picklist. So if your picklist was for "...

Limiting The Amount of Text
Hi, Is there a way I can limit the amount of Text I can enter into th cells of a selected column? For example, when listing on eBay, the description bar on eBay i limited to 40 characters. Since I list items on eBay with Excel, i would save me a lot of time if I were able to lock the cells in m "Description" column to 40 characters instead of going back an counting all the characters in those cells. Is this doable ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum....

trouble opening office 2003 excel files
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am having difficulty opening Excel files that were created in the 2003 version withthe 2008 version. The files were originally created on a windows XP machine, then imported into a Mac using Office 2003 under system 10.4. The files are rather large, approximately 60 columns by 10,000 rows. I haad no difficulty in working with the files between the Windows and Mac systems. I recently got a new MacBook Pro with an Intel processor and installed Office 2008. When I try to read these large Excel files on my new lap...

Can I convert footnote numbers to text
I'm working with a document where the author has used the automated endnote facility in Word. Since this is about to be translated into Quark for typesetting, I need the endnote marks and the numbers with the endnotes to be real numbers, not endnote marks -- Quark will not read them as numbers. I was sure I did the conversion using ActiveDocument.ConvertNumbersToText in Visual Basic once before, but I can't seem to make it work. Help! There are 20 chapters with about 40 endnotes each. ActiveDocument.ConvertNumbersToText only works with paragraph numbering (and LISTNUM fi...

Excel 2003 / Send To / Mail Recipient / body text is removed
When a user is working in Excel 2003 and hits File.. Send To.. Mail Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email as an Attachment. If they type any text into the body of the email it is replaced with <<...>> How can I change this ? If you don't get a good reply here, you may want to post in one of the .Outlook newsgroups. jmaynard2 wrote: > > When a user is working in Excel 2003 and hits File.. Send To.. Mail > Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email > as an Attachment. > > If they typ...

odd files created
Every time I open and edit an excel spreadsheet on a network share, small odd files get created. They are usually no larger than 25-40k and don't have any extensions to them. Looking at the properties page for any file, the file description says File. Anyone know what this is from or how to get rid of them? Permissions are setup correctly for me, Word files don't have this behavior. Excel 2000 SP3 Thanks, Hi Brian, A file the same size as the workbook would be created in the same directory as the workbook. The filename would be nonsensical (or appear to be random) character...

Supported File Attachment for Faxes
What are the supported file attachment types for fax activities? So far I've gotten simple text files and Bitmaps to attach properly, but Word documents do not work. Anyone know? Is this completed undocumented or what? Try this! If you are attaching a file under the Notes tab, you need to save your fax activity first then you can attach the file. Hope this help, DK "iparadox" wrote: > Is this completed undocumented or what? > > It does'nt work with Word attachments, it only works with .txt files. But it can't be this is what MS wanted. Can anyone p...