Lookup Data From Large Csv File
Jan 21, 2010
I want to use the attached Test.xls file below and do a lookup from the Data.csv (see zip file) where "Empl ID" (column A in Test.xls) matches "ID" (column A in Data.csv). I want to bring back "SupvID" (column B in Data.csv) into column D in Test.xls.
The problem I have is that in real life Data.csv is a very large file and contains more than Excel's maximum number of rows. I want to automate this using VBA to lookup the values without opening the csv file.
I have looked at a number of forums for answers and I can see where using an ADO connection will probably help accomplish this, but I can't seem to get it to work on these specific files.
View 4 Replies
ADVERTISEMENT
Nov 18, 2008
In column A I have a list of product names..(e.g....ABCD, JKLM....)...this is the "master product list" -- 1000 rows of products.
In columns C:G, I have additional product names, each column represents products sold by state. (each column lists between 100-250 products).
I want to do a VLOOKUP and I wanted to know what products that are in
A1:A1000, that is NOT listed in C:G?
My experience with VLOOKUP is limited...but I do know that I can only look in 1 column at a time.
How can I write it to look at the ENTIRE range? I suppose I can copy all the all the products to 1 column...?
View 6 Replies
View Related
Jan 9, 2008
I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?
View 3 Replies
View Related
Apr 17, 2008
I've been scouring the Internet for a few days now trying to get a macro working with varying degrees of success. It's driving me mad and I'm really hoping that someone out there can help me out. I'm trying to write a macro that will ...
1. Take a .xls file
2. Split the file in two
3. Take cells B2:B11 and paste them in to a csv file (to be called Upload1.csv) using the rows as column headers (i.e. B2:B11 now become A1:J1)
4. Take cells A13-J13 downwards (this file will have different numbers of rows each time it's generated) and paste them into a different csv file (to be called Upload2.csv) populating all the rows (except all rows in column I which are to be left blank) and all rows in Column J which are to contain the info in cell B7 of the original document.
5. In cell B11 of the original document I must only take the information that says MyAddress:xxxxx and discard all the other info
6. I must also loop through the second document until I find the words Total and copy everything UNTIL that row into the new document.
I know this probably sounds pretty awkward but in practice it's not that difficult to understand, it's probably the way that I've explained it
So far I've managed to take the original document and split it, populate both CSV files but I'm having problems with looping the macro until it sees the word Total and discards it and also populating the appropriate rows with the info from B7 ... I don't know how to make it populate only the rows that have info on them.
I've attached the original Excel file that is generated and also my attempt at the 2 csv files. I've also written in RED what needs to go where in each document.
View 14 Replies
View Related
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Mar 3, 2014
I am trying to use excel tools to clean dirty data and compare the two cells. The information is there but tainted with additional information that is not relevant. I have tried to use Left/Right tools to capture alpha characters leading an address number with no real success. Also, when I get the data it seems to have some embedded breaks that I can't seem to get rid of that cause my tasks to error too.
View 4 Replies
View Related
Mar 8, 2013
In sheet1, I wrote my data for lookup porpose. The data named (Table). Then I saved it as Add-in (named TEST). That Add-in, already added to my excel Add-in
I open another excel file but How can I use lookup formula from that data (Table) in sheet1 in that Add-in (Test)?
View 1 Replies
View Related
Jun 1, 2013
I have some quality information that is stored in a single .txt format. All the information for several different parts are stored in the same file "merge__chr.txt I then have a single excel file for each different part. I need to be able to use a macro to extract the data from the txt file and fill in the excel file based on 3 criteria.
Part #
Serial #
Characteristic #
The part # is always in cell "B4" of the excel file, and the first column of the .txt file The Serial # is variable, but will always be in column B (starting at row 11 and down) of the excel file, and the second column of the .txt file The Characteristic # will always be in row #7 (starting at column D and right) of the excel file, and is in the 3rd column of the .txt file.
I need this to copy the information from the 6th column of the .txt file and store it in the cell where the characteristic and serial # meet. I only need to be able to do this for 1 serial # at a time. When the current serial # is selected, I would like to use a button to fill in all the information for all Characteristics of that part. I attached a sample set of data with "QA-PRINT" as the Part #.
View 6 Replies
View Related
Apr 16, 2009
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:
1 – car
2 – truck
3 – boat
4 – motorcycle
For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.
View 2 Replies
View Related
Mar 16, 2013
my colleague did something to one of our files and it is now 845918 kb. I transferred it to a flash drive and then to my hard drive but now I cant open it to see what is wrong. Is there a trick to opening this up? When it was opened on her computer it wouldn't let me add any tabs or move tabs. I think I unprotected it but that didn't change anything.
View 1 Replies
View Related
Oct 30, 2002
I have a database of 75000 rows in text format which has to be converted to excel, but the limitation in excel is only 65536. hence, I want to a macro that will copy the 1st 65536 rows into the 1st sheet in excel. The remaining rows should be copied into the second sheet. I know that it can be done with access. but i dont want to use access.
I have got a code to do this, but it takes much too long. if the code could be modified to make it faster,
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g.
View 9 Replies
View Related
Feb 4, 2009
I have a 14.3 MB xlsb file that I am trying to open with no success as it just hangs. Anyone have any ideas on how to get the data out of there? I would like to be able to get the data to a pivot and copy the pivot to another file to try and reduce the load on one file. I tried to import to Access but was told I was using a file version before 2007, which is impossible since it is a xlsb file.
View 9 Replies
View Related
Feb 24, 2013
I've been having tremendous issues with my Excel file, mostly with it crashing or not responding. Would this be because my file has so much data in it? If so, is there anyway to make Excel process all the data without crashing so much?
View 6 Replies
View Related
Aug 1, 2008
I have a SUPER large CSV file that I cannot open in 2002 excel
Is there a way to write in VBA to import the csv but only include rows that contain "TESTIMPORT" in a cell?
View 9 Replies
View Related
May 4, 2009
I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size.
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else.
Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file.
View 9 Replies
View Related
Aug 25, 2009
I have a large text file, 75mb, that I would like to import into Excel but it goes well beyond the 65536 lines available.
Is there a process to get these files into excel? File splitter?
View 9 Replies
View Related
Jul 20, 2006
I have a file that is 40,000K...is there anyway to decrease this file size without having to delete data or break up the spreadsheet?
I couldn't think of anything but wanted to double check.
View 3 Replies
View Related
Sep 20, 2006
I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.
The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).
View 4 Replies
View Related
Sep 20, 2006
I have a large text file (greater than 64k lines) which I need to import into excel. I only need anything imported from the text file that is for dates I specify. So I would like to run a line by line query and only import the lines I need.
The dates are always in position 43-50 (dd-mm-yy) in the text file itself, so the code would have to look at that section, see if the date is within the range specified and only import if it is (and then move onto the next line).
View 5 Replies
View Related
Jun 7, 2013
My pivot table file is currently 12.7 MB and I need to get it much smaller so I can email it and it still be functional with the data.
I have tried right clicking on the pivot table, going to PivotTable Options, clicking on the data tab, unchecking the 'save source data with file' box, and checking the 'refresh data when opening file' box. This shrunk the file perfectly but when i email the file the Pivot Table was unusable because there was no data to back up the function.
The Error messages I got were 'Cannot open PivotTable source file'..... and 'Pivot Table report was saved without the underlying data. Use the Refresh Data command to update the report'. When I click on Refresh Data I still get the same error message.
Any way I can make this File smaller and still functional.
View 4 Replies
View Related
Apr 1, 2009
I have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.
I then highlight all the rows and try to delete them, but I receive the following error popup box:
"Microsoft Excel cannot create or use the data range reference because it is too complex."
How can I delete all these filtered = 0 rows without getting this error??
View 9 Replies
View Related
Aug 31, 2009
I'm using Excel 2007. I have a cell with a dropdown box that has the values 1 thru 10. When I save the file (as an Excel 97-2003 compatible .xls file), it's 17kb which is normal. I want multiple dropdown boxes going down the same column, so that every time the user goes to the next row, another dropdown box will be displayed. I copied the cell with the dropdown box, selected the entire column, and pasted the dropdown box.
This time when I save the document, it's over 2MB in file size. I've seen other spreadsheets with several columns of dropdown boxes spanning thousands of rows and they stay under 30kb. I don't know what they did differently to keep their file size small. Why is my file size so large and how do I fix this? Do I need to use a 'paste special' function?
View 3 Replies
View Related
Mar 29, 2014
i always receive 200 mb file. which i has, around 10 sheets , with pivot tables and formulas.
i have to open this file update refresh and save as this file, which i am doing via vba.
however the challenge for me here is though i set xl calculation to manual at the begining and xl calculation to automatic at the end. Though i used this code, its taking close to 5 mins to complete this via Vba.
how or what is the alternate way to reduce.
View 6 Replies
View Related
Dec 12, 2009
On this forum, a script is provided to break a large Excel file into smaller 500-line files:
Split Worksheet Into Multiple Files By Every Nth Row
I copied the code and pasted it into the VBA editor in Excel 2007, but when I run the macro, it generates an error message:
Runtime error 91: Object variable or With block variable not set.
The line the debugger identifies as the one with the error is this one:
For lLoop = 1 To rLastCell.Row Step 500
Here is the full code from the previous thread: ...
View 9 Replies
View Related
Aug 11, 2014
I have an Excel file that's updated monthly. when it does save its around 16mb and can take up to 12 hours to save, and sometimes just doesn't.
I have tried saving as binary, I have made sure exact size of area to be saved is required, I have tried save with no calculations.
Basically the only reason I need to save it is so that another analysis spreadsheet can pull data from it. The file is heavily formatted, charts, vlookup tables etc, none of which is needed when analysis spreadsheet links to it.
View 1 Replies
View Related
Jun 26, 2013
I have a file that has been produced using Statistica software. The file is supposed to have around one million rows, and when exported to Excel the file is about 30 MB. When I open the .xlsx file in Excel 2010 only two pages of data appear. The rows also have weird numbering. The first square of the A column is A1. The second is A3833, the third is A6789, then A8161, then A8162, then A8163, then A8164, then A18070.
The scroll button to the left of the screen is long, as if the document was only two pages long. When the scroll button is click-and-dragged a small beige square appears with a row number. This small square seems to be aware that not all rows are shown as it shows many more rows than are visible to me.
View 3 Replies
View Related
May 19, 2008
I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??
View 3 Replies
View Related
Apr 18, 2012
I have a large file that takes a long time to close.
I would like to display a message that says " SAVING - PLEASE WAIT".
But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.
How can I do this?
View 7 Replies
View Related
Jan 30, 2013
code to import a tab delimited text file with about 3 million rows so that it creates a new tab every time it hits the 1 million row limit?
View 3 Replies
View Related
Apr 19, 2008
I found following code on this site to extract txt files. As mentioned this code can extrack huge txt data to multiple sheets.
Sub ImportLargeFile()
' Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
'If a file in a different format is to be imported amend the extension at line 10
Dim strFilePath As String, strFilename As String, vFullPath As Variant
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object.............
However I am getting Run time error '-2147467259(80004005)': could not find installable ISAM for section 'Open an ADO connection to the folder specifiedin above code
View 2 Replies
View Related
