Aggregate Text Files
Jul 2, 2009
To go through all the database files (.txt format) in a designated folder in order to update an Excel Sheet with the required information from those database text files. Then to summarise the information in a simple table.
The text files are individual Market Exports from a game I play, and are titled as "Region-ItemType-YYYY.MM.DD HHMMSS.txt"
eg. "Heimatar-Tritanium-2009.07.02 133353.txt".
I need to know how to sift through the text files to find the most recent one with matching "Region-ItemType" parts in the file name, and refresh the Excel Sheet with the updated data.
After that, I need to be able to combine all of the cheapest sale prices for each item, with a minimum amount for sale (differs per item), while also being limited to location the item is being sold at, into a single sheet of the important information I wanted to extract in the first place.
At the end of the whole process, I need to have a single Excel Sheet with every different item's lowest price for each different place, displayed in a table I can use to make formulas from.
So if I have an export from a place called "place1" and one from "place2", looking at the same item called "item1", I should have two rows of tabulated data, one for each place's lowest price. And this should update at the touch of the "Refresh All" button, or... However you'd have to do it
I have attached a copy of one of the database text files.
View 11 Replies
ADVERTISEMENT
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
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
Mar 12, 2008
i have 10 columns, the last one is an amount. What i'm looking for is to aggregate the rows with the same key(columns 1 to 9) just into one and get the summation of the related amounts in the column 10.
Example:
row1: a b c d e f g h i j 100
row2: x x x x x x x x x x 100
row3: a b c d e f g h i j 100
row4: a b c d e f g h i j 100
the desired output is:
row1: a b c d e f g h i j 300
row2: x x x x x x x x x x 100
View 5 Replies
View Related
Jan 30, 2014
Just wondering the better way to 'get' a score for some data.
For example, I have a data set where there are a number of records are interrogated for validity across X rules. The returned count of errors of course could include one record for all X tests.....
what is the better way of weighting these to get one score
Please see attached example : XL4M.xlsx‎
View 1 Replies
View Related
Jan 10, 2008
Aggre ABC1Brand Sales 2A5 3B 2 4C7 5A1 6D1 7 8Above are my data 9 10Brand Sales 11A6 12B 2 13C7 14 15Above is the result I want. 16 17 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jan 31, 2014
I run a golf society and need to set up a golf aggregate sheet that will allow me to enter the players weekly scores and the sheet will then look for the lowest score entered for each player and replace it with their score of this week.
View 13 Replies
View Related
Dec 8, 2008
I have column E listing all of the favorites and column G the underdogs, each row represents one game, there are 34 all together. I want a formula to pick the team based on a random generation. That part i have....
=choose(randbetween(1,2),e7,g7)
but here is the rub.
I only want the total number of underdogs to be 20% and the favorites to be 80% of all of the picks.
I am not sure it can be done as each formula must look to what the other formulas are doing...aside from it being circular i think it just impossible or i am not as advance as some of you.
View 14 Replies
View Related
Apr 16, 2014
I have a worksheet in which rows have empty column values, e.g.
Name,Column1,Column2,Column3
Bob,Apples,<blank>,Peaches
Joan,<blank>,Oranges,<blank>
Ted,Apples,Oranges,Peaches
Note: The commas above indicate separate columns.
I'd like a formula to evaluate these results and strip out the blanks. Results should look like this:
Bob,Apples,Peaches
Joan,Oranges
Ted,Apples,Oranges,Peaches.
View 3 Replies
View Related
Mar 2, 2012
I am using Excel 2010.
I have a large spreadsheet with brands and volumes.
Many of the brands run the same processes, so I can consider them as one.
For example:
Brands - Volumes
A--------10
B--------16
C--------18
D--------20
E--------16
Imagine that A and C are basically the same. So the value that I really care about it (10 + 18) = 28
B and D are the same, so I care about (16+20) = 36
The output I need is:
A&C - 28
B&D - 36
E --- 16
Can I use SUMIF to basically say: Sum this IF =A OR or =B ?
Can I apply an IF function with concatenate to merge the A's with C's and B'd with D's?
The spreadsheet is large and this process will apply to many different groups of brands.
View 2 Replies
View Related
Jun 16, 2013
I have a list of employees and their e-mail addresses. There is also a column denoting whether they should be part of an email distribution list or not (if yes, denoted by an asterisk).
I need to aggregate all of the asterisked e-mail addresses in one cell. It also needs to be "active", i.e. having an IF statement for whether or not it has the asterisk and therefore should be included. Last requirement: the addresses need to be separated by a semi-column, then space (for easy copy and paste into Excel) like this:
name@email.com; name@email.com, etc.
View 1 Replies
View Related
Oct 17, 2009
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.
Cells A1 to B5 contain a lookup table:
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
LetterNumber D4 C3 B2 A1
Cells A7 to C8 contain the input values:
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}..................
View 9 Replies
View Related
Aug 5, 2009
I have a book with 250 worksheets, with one cell of data per sheet. It is the same cell in every sheet (C7). I need to somehow aggregate all of this into one column on the first sheet, 250 rows.
View 5 Replies
View Related
Jul 4, 2014
I have a database file where users name can be located in 2 neighbor columns. Against each user name (in next 2 cells on the right) there is a evaluation (good or bad).That database will be growing and new additions will be added each month. That means one user can appear several times on a different row with different evaluation.
In the same file I have a separate sheet where I need after running a macros to have aggregated list of users (in one column) and against each user I would like to see how many how many good and bad score he have. It will be good if users are sorted in alphabetical order.
I am providing an attachment with example file.
View 2 Replies
View Related
Sep 17, 2007
I m trying to write a macro which could take the text from a single column row T2 to row T313 and write it to a .txt file. Have the .txt file name created by the text in T4 or I could also put the text to name the file in T1 if you think it would be easier.
Then carry on to the next named sheet and produce another .txt file in exactly the same way until all 15 sheets have been completed. It would also be helpful if prior to starting to write each text files, it could test for any text in cell A2 of the sheet. The first empty A2 cell of a sheet would determine the end of the run, if it was prior to sheet 15 being reached.
View 2 Replies
View Related
Mar 15, 2005
I'm trying to take a large text file and break into smaller text size files. I want to open the data file, and parse into 5000 line smaller files.
Here is what I have based off of some MS KB
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim CounterMax As Double
Dim sPath As String
Dim FileCounter As Double
'Output File
View 9 Replies
View Related
Apr 24, 2008
how I would go about addressing this issue. I am given a directory with individual files in it. Each file has critical information I have to extract from it. So, I want the user to provided this directory to the macro and I want the macro to cycle through each file in this directory (excluding super and sub direcories), open it, retrieve info, and close it
View 5 Replies
View Related
Mar 10, 2008
I have opened a .txt (by right clicking and selecting Open with rather than open from within Excel) file with numbers using the format 1,234,567.00 but in my country we use 1 234 567,00. So I created a macro changing the , into "" and the . into , and it works fine while creating the macro itself (by using the macro recorder) and the numbers shows and behaves like numbers. However, when running the macro on a new file some numbers remain as text whereas others are numbers. Theres is no common string, eg that numbers starting with - (minus) remains text.
It does then not matter if I go to the format function and select number with decimals and thousand dividor - the number still remains as text. I have also tried the suggested multply by 1 but it still remains as text. The only thing that helps is to either double click on the number or use the Konvert text to number. But I would then have to click each number in the whole table which is what I wanted to avoid.
View 7 Replies
View Related
Jun 23, 2008
What I have is a bunch of text files and 2 spreadsheets that sort the text files. I’m not sure but I think there might be a better way but the problem is I don’t know VB all that well. Can someone take some time and look over this stuff and let me know if there is actually a better way to make this happen. I also noticed that there are 2 txt file missing and not added in the sorted file (PS3_HIGH, PS3_URGENT)
The text files come from another network and are transferred manually every day to a folder. The purpose of the .xls files is so that someone can look at the counts and try to lower the numbers from day to day. If something is alarming frequently, the current files do not put that information together well. The numbers are still important, but is there a better way to do this sort in excel?
View 9 Replies
View Related
Dec 11, 2009
Hi all, first up I'm a total novice but I'm trying to populate a worksheet from a text file, but here's the kicker, I'd need specific lines of the text file to populate specific columns.
None of the text files will contain the same data, but, they will all have the same related data on the same lines. So I'd like to see:
.txt file 1 line 1 > Worksheet A:1
.txt file 1 line 6 > Worksheet B:1
.txt file 1 line 12 > Worksheet C:1
.txt file 2 Line 1 > Worksheet A:2
.txt file 2 Line 6 > Worksheet B:2
etc.
Everything needs to go into the same worksheet. Some .txt files won't have any data in the specified lines so skipped if blank, or just blank data copied.
I need to run through about 1300 .txt files in a directory in this fashion, can it be done?
View 14 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
Apr 27, 2007
I am gathering data over several sheets and need to export it as a.txt file.
The problem is that in excel the data reads 1,2,3,4 and when I export it or save as or open and paste to the resulting data reads "1", "2", "3", "4,".
How to I get the txt file to not have the ""'s?
View 9 Replies
View Related
Dec 3, 2008
I have two text files with huge data in each which I need to compare for difference between each
for e.g.
file 1 would have following records
12345 KKKK 3510 ABCD
file 2 would have
12345 KKKK 3210 ABCD
file 2 would have similar records but 3510 would have difference. What i need is " if 12345 and ABCD is matching then what is the difference value of 3510 and 3210 ? the result should be 300 . This i need as an output in another text file.
View 9 Replies
View Related
Sep 15, 2009
i have a txt file see attached and i need macro to:
1)open the file
2)find the word "Dimension no: 3"
3)go 2 lines dwn and copy the number " 64.999"
4)paste it into excel
file:me.txt
Dimension no: 1
Plane:PL1
Flatness 0.003 0.200 *---
--------------------------------------------------------------------------------
Dimension no: 2
Circle:CR1
Diameter 113.001 113.000 +0.000 +0.300 0.001 *--+---
--------------------------------------------------------------------------------
Dimension no: 3
Circle:CIR0
Diameter 64.999 65.000 -0.300 +0.300 -0.001 ---*---
--------------------------------------------------------------------------------
Dimension no: 4
Plane:PL1--Plane:PL3
Lengthavg 15.500 15.500 +0.000 +0.100 -0.000
View 9 Replies
View Related
Nov 14, 2009
I have thousands of lines in a txt file and would like to total the sum of a field. here are the few lines in the txt file.
I would like to calculate the LPG_Amt for each of the Company_code and the count of number of lines for each of the Company_code. Request the members to help me with a vba code that could run in an excel file and give the output in excel.
Company_codeShip_to_PtLPG_Amt
DK01100281757,454
DK011002817620,630
DK011002818012,929
DK011002818121,783
DK011002818324,470
DK011002818512,478
DK011002818621,692
DK011002818714,424
FI011002872728,708
FI011002873122,114
FI011002873237,827
FI011002873362,425..................
View 9 Replies
View Related
Sep 3, 2006
What is the need for storing information in a text file instead of just keeping the data in excel on a worksheet or in vba code.
for example.
I have a file that was given to me that on the OpenWorkbook event runs through an "INI read " procedure. All this does in get certain data from a text file such as:
file paths, rates, file names
Is there any need for this as you could easily store the same information on a worksheet and declare variables in the code.
View 9 Replies
View Related
Feb 8, 2007
I have a text file that has been saved via PL/ SQL code from a CLOB (due to the MSDAORA not supporting CLOBs). Unfortunately, due to passing the file between operating systems it appears that the CR and LF characters are not consistent. This means that my third party application in Excel cannot read the text file properly.
View 2 Replies
View Related
Jul 17, 2014
Below code Works for TAB delimeter but if there is ' ,' in a row its not reading correct data.Basically I am trying to read multiple .txt files to .csv .
Sub txt2csv()
Dim Fname As String, ipath As String, retstring, fs, a, i As Long
With Application.FileDialog(msoFileDialogFolderPicker)
[Code]....
View 2 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
Sep 26, 2009
Im trying to loop through all the files in the folder. and look at the filename if the filename contains JPE_s.jpe delete the file.
View 14 Replies
View Related
