Importing Selective Lines From Multiple Text Files
Jun 18, 2009
I have many text files basically with the same data from different dates. I am trying to select specific lines from each text, one that includes ".txt" which is the date, ex: "2008_308" and one that includes the word "PIXEL" and the following 5 lines after that.
I found a very similar post to mine and someone provided the script using VB for the question. It works, but it was only for one text file. I would like to use it for multiple text files. The files are all dated, i.e. "2008_308.txt". Although some of the dates are missing.
This is the script, from another posting, for importing selective lines from one text file:
Sub test()
Dim fn As String, txt As String, delim As String, a() As String
Dim i As Long, ii As Long, iii As Long, n As Long, x, y
fn = "c: est.txt" ' ©Change here (File Path)
delim = vbTab ' ©Change here (delimiter)
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
If InStr(1, x(i), "txt", 1) > 0 Then
n = n + 1 : y = Split(x(i), delim)
For ii = 0 To UBound(y)
a(n, ii + 1) = y(ii)..........................
View 9 Replies
ADVERTISEMENT
Apr 16, 2008
This is a great bit of code (I found), to import multiple text files.
I have one small issue -
currently it imports with each file going to a new column, like this: text file1 A1:A4, txt file2 B1:B4, txt file3 C1:C4 etc etc
And eventually i risk running out of columns because I'm working with a lot of text files.
Can someone tell me what to do to modify this code so it imports like this? :
text file1 A1:D1, txt file2 A2:D2, txt file3 A3:D3 ETC ETC
.e.g. first file imports across row1, second file across row2 etc
Sub test2()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "\BP1MELIS001SHARED_DATAEVERYONESolutions IntranetPriceMasterlogs" '
View 9 Replies
View Related
Nov 22, 2006
I have about 1200 text files with data regarding different machines.These file contain a line called 'Validation date" and also the programe number on different line.
I need a macro which will extract this date for each program number and write in excel in two columns like program number and validation date.
I have tried with some of the macro help available on this site,but I have to do this one at a time.The time required doing this way is same as going in each text file and copy/pasting in excel.Is it possible to automate this process.
View 9 Replies
View Related
Apr 26, 2007
I am using excel 2003. I have more than 500 text file which are result of some numerical analysis. I have another bunches of the same file number. I can record and play around with macro.
What I want to do is
-copy selected cells from imported text file in excel and paste in new or in first opened excel file. I have recorded macro for one file including importing from text to excell, copy and paste the selected file (look the macro below). But I have more than 500 files and I want to do the macro the same thing for each file in one excell file. Do i have to make one macro for each file??, that takes much more time than manual import and copy paste. The cell position and range to copy is the same, but have to be pasted in new row (in one excel summery file). The file name of each text file is different and all are in the same folder.
Sub text_to_excel()
Workbooks.OpenText Filename:= _
"I:ResearchVALERI_germinationvaleri_slopevaleri_slope_COREL_DHPoutputDSCN2589.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _ ..................
View 9 Replies
View Related
Apr 30, 2013
I'm trying to create a macro that will import multiple multiline text files.
I have tried another Macro from this site however that imports the each text file into one cell, but i need each line to be in a new cell and each file to be in the same sheet.
View 10 Replies
View Related
Apr 10, 2014
I have around 50 text files with similar design per attached file. I need to import the text files with criteria below:-
1. include file name
2. let user choose the folder
3. exclude data from "work in process summary" to "work in process cost totals"
4. only have one title in the excel files which all text files is combined "Item, Line ....."
5. If the text files do not have title like "Item, Line, ..." do not import
Is it possible to have all criteria listed above by running a macro?
A.txt
View 1 Replies
View Related
Jun 26, 2007
i try to input an txt into a excel spreadsheet, how do skip to certain lines or number? for example if the txt is like
1,2,3,4,5,6,7,8
and i want the excel sheet to start at the the third number
3
4
5
6
7
8
View 8 Replies
View Related
Sep 25, 2002
I have a whole lot (hundreds) of .txt files that contain fixed width data that I need to convert to Excel format. I can open each file and step through the Text Import Wizard, then save it as an .xls file, but I'm wondering if there's a way to convert all the files in a given folder from .txt to .xls without having to open each one while (here's the kicker) still placing the data between the spaces on the .txt file into individual cells and columns like the wizard does.
View 9 Replies
View Related
Feb 27, 2008
I have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.
The code I am using is:
Sub su()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:Historical" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
' do something
sFil = Dir
Loop ' End of LOOP
End Sub
In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed:
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
View 9 Replies
View Related
Oct 28, 2008
So I am trying to use this VBA:
http://www.cpearson.com/excel/ImportBigFiles.aspx
It´s basically so I can important a very large log file, about 800,000 lines, many of them repeating, I want to import into Excel to better control the data.
I keep trying to import the data, and I cant get it to make me new sheets.
View 3 Replies
View Related
Jan 16, 2014
Below is a macro I recorded for importing the text file AJ9285 into excel from an external location.
I would like to modify it so that excel will give me a prompt and I can type the text file name in.
Sub adt()
'
' adt Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:AJ9285.ADT", _
[Code] ..........
View 2 Replies
View Related
Feb 8, 2006
With reference to Importing large text file. i need to get large text files to automatically split across Excel sheets sheets while trying to Import. Can it be done only through Macros or is there any function in Excel itself.
View 7 Replies
View Related
Dec 3, 2012
I have a workbook that contains over 100 worksheets with stock data and price information. It uses a screenscraper to update the workbook each day with the latest day's price, and then exports each of these .csv files into a local directory. The macro for this is working. In effect it is generating a price history file automatically in my absence for me that can be read by charting software.
What I would like though is a macro to loopthrough and backfill missing price histories on each worksheet. I have a source of .csv price histories already, but would like to avoid having to cut and paste each of the 105 files manually, as it may become a regular occurence.
Each worksheet that requires backfilling has the stock code in the cell "A2", so that can be used to search for the filename Range("A2") & ".csv"
This is as far as I have got - however it results in a runtime error (91) Object variable or With block variable not set, pointing to this line:-
VB: Workbooks.Open Filename:="D:FinancialData SheetsSpreadsheetsPension" & Ws.Range("A2").Value & ".csv"
VB:
Sub BackFillData()
Dim Ws As Worksheet
For i = 1 To 105
'Sheets.Add
ActiveSheet.Name = "Fund" & i
[Code] ......
View 2 Replies
View Related
Jun 15, 2013
I am troubleshooting my macro that seems to cause a data shift with the Letter "F" when I import multiple CSV files into one spreadsheet. When I go outside of the macro and record a macro and import the CSV into a workbook it works perfectly fine. But there must be something in this code that is causing the shift:
VB:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Public Function ChDirNet(szPath As String) As Boolean
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
[Code] ....
I have attached an excel workbook that shows how the macro imports vs. a regular import. Why "F" is causing a shift in the data.
Data.xlsx
View 3 Replies
View Related
Nov 17, 2008
I know similar questions have been asked before, but I couldnt work out how it is done.
Basically I have several xls files with the same name, eg. "test" with an alternating number at the end. 1 - ....
each of these workbooks contain several sheets but all books are the same just with different information on it. each workbook has got a summary sheet in it, i want to import all summary sheets into a single workbook called summary. but each sheet should be imported as in individual sheet.
View 9 Replies
View Related
Sep 14, 2012
I have a folder in my D drive where I used to keep my Text files after downloading it from our support centeral. I want to Import all these Text files in one excel sheet in a way that data of each file got append in excel sheet below previous file data.I am trying to do this through FSO but unable to do the same.
View 1 Replies
View Related
May 23, 2006
I have checked the forum about reading from text files, but I wasn't able to find and similar thread. Here is the situation: I have one text file, but first 204 characters are the file header. 204. character is an asterisk. Then, the records begin. Following is the beginning of the file:
$$GNCSBU/PML/0002 TCPX 11006 IST Bayplan 23.05.06 14:35:57 TRIST 11006 *
And it continues:.....................
View 2 Replies
View Related
Apr 5, 2012
I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.
Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?
View 3 Replies
View Related
May 11, 2008
I load an text file into an excel sheet and after some eventually changes I save (overwrite) it back into the text file again. Now the text file has become a file with 50 pages (49 empty pages). How can I avoid this or how can I set an EOF when no more written lines follows. The code for saving the excel sheet is this:
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Sheets(wsImport).Select
Sheets(wsImport).copy
ActiveWorkbook.SaveAs FileName:=WorkFolder & WorkFile, _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
'ThisWorkbook.Activate
Sheets(wsKwartaal).Activate
View 6 Replies
View Related
May 21, 2008
I am trying to wite a macro that will allow me to pull up specific files. All files will be located in the same folder. For example, I want to type the workbook name "sawn" into a cell and hit the macro button. The macro will then look in a "pool" folder for the workbook named "sawn" and open it. Is this even possible?
View 14 Replies
View Related
Jul 13, 2006
I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Sub Master_Importer()
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
With Application.FileSearch
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
View 5 Replies
View Related
Jun 8, 2014
I would need only the first row data to be copied into a master file in column A (row A from each file to column A, column B.... etc - basically transpose value into master file)
View 1 Replies
View Related
Jan 23, 2010
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.
View 9 Replies
View Related
Jan 16, 2007
I sometimes import data in the copy-paste way from completely different sources, eg. a program on the web or whatever, which has arranged the data in a table.
It is often succesful with the data neatly arranged in the spreadsheet in columns and rows and all, but sometimes the gridlines disappear even though 'cell format' is set to 'none' in 'grid lines' and the settings have a mark for 'visible grid lines' and their color is 'automatic'. Only the imported area misses the gridlines.
It is of course possible to set some grey gridlines manually in cell format, but then I get them on the print also and I don't want that.
View 9 Replies
View Related
Mar 19, 2013
I've been asked to streamline some processes for a company I used to work with before I went on maternity leave (been two years now, my brain feels like its gone to mush). One of the things they have is a document where they input EVERYTHING manually into a word, sometimes the same thing several times. (felt like I had gone back into the dark ages). I know I can set up a template in Excel where all the required information can be imported into specific places within the word document, but Im struggling to find a solution to one part.
They get their clients to fill out an expenses form showing all their monthly expenses, some fields will be filled in but others are not relevant to them so they are left as zero.
How do I go about just importing ONLY the fields that have values input against them?
For example;
Mortgage $2000
Rent $0
Electricity $300
School Fees $0
Contents Insurance $200
Water $80
Pet Care $50
Groceries $1000
Takeaways $0
Then when the data is imported into word it would show up like this (formatted better of course)
Mortgage $2000
Electricity $300
Contents Insurance $200
Water $80
Pet Care $50
Groceries $1000
View 1 Replies
View Related
Dec 21, 2007
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
View 9 Replies
View Related
Jun 6, 2007
I want to import a excel file in the current sheet but want that few of the starting lines are not imported. Suppose i have a file with 500 rows but i dont want to import starting 39 rows.
View 9 Replies
View Related
Oct 27, 2006
For the following formula:
=”TEXT1”&”TEXT2”
The result is:
TEXT1TEXT2
Is there any kind of modifier that I can use to force the output to multiple lines, i.e.:
TEXT1
TEXT2
If this wasn’t a formula I could just use ALT+ENTER. Wrapping the text will not work because I need fixed break points not dependent on the text box width.
View 5 Replies
View Related
Sep 26, 2007
I have about 100 Excel files in one folder that need to be saved as text files. They can keep the same name, but simply need to be converted to text files. I'd like to use VBA for this and I can't find examples that do exactly that...or ones that my limited knowledge can handle.
Excel files exist in C:Source and ALL of them should be saved as text files in C:Destination. Maybe there is an easier way, but I thought for sure there was a routine I could use.
View 4 Replies
View Related
Jul 24, 2014
I am trying to cut and paste from a PDF document into excel and a grab of text when pasted sits in three boxes instead of one. How can I manage this?
View 9 Replies
View Related