Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


SUPER Large CSV File That I Cannot Open

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Large CSV File: Too Large To Open. Split
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 Replies!   View Related
Lookup Data From Large Csv File
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 Replies!   View Related
Open .xls File From .bat DOS File & Save As .csv
What I would like to do is to open an excel workbook from a bat file in dos with one arguement.

so something like

start c:lacklist.xls 2315t

Once the blacklist.xls is opened I would like to have the macro to save the excel file as a csv file ignoring all the prompts and use the argurement 2315t as the directory to save it to on the j drive ie save to j:2315tlacklist.csv. Then close the workbook and continue with the rest of the commands in the bat file.

I'm guessing I have to use GetCommandLineA to read in the arguement but I can't find anything on the net that gives a clear explanation of how to use it.

View Replies!   View Related
So Large File That Excel Cannot Open It...
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 Replies!   View Related
Open Large Txt File With Query
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 Replies!   View Related
CSV Files Open In New File Instance
I regularly process CSV files that are downloaded from various websites. The problem is, for some reason many (but NOT all) downloaded CSV files do not open in "Microsoft Office Excel" and thus, my primary workspace. They instead open in a separate instance of "Excel.exe" which is actually kinda nice because I can treat each separately, move windows around, etc. The problem is, macros that are in my primary workbook, cannot be seen by the secondary instance(s). So while I have created a number of nifty macros to clean up all the data and format it exactly how I want it in the blink of an eye, I can't run them when a file opens in its own instance.

1) How can I force ALL .csv files to open in the primary "Microsoft Office Excel" application? NOTE: Using the normal Windows Explorer "Open With..." function and setting the default application only works for LOCAL files, and does NOT work to control how remotely downloaded .csv files are opened.

2) How can I make it so that macros can be seen between Excel instances - such that when I choose to see macros in "all open workbooks", it will actually see the macros in the open workbook in the other primary Excel instance?

View Replies!   View Related
Large File Takes Long Time To Open/Save
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 Replies!   View Related
Copy Data From New Everyday Csv File To Next Empty Row In Master File
I have csv files auto generated (with date stamp in name) and saved in a specific folder everyday. I need to find a macro that will copy the row(s) every day and add to the next empty row in the master excel file. Some days the csv file may have a single row of data and on some other days it may have multiple rows of data to copy and paste.

View Replies!   View Related
Import CSV File Using Cell As File Name & Active Workbook Path
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.

Sub test()
Dim wsName As String
wsName = ActiveCell
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With

With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................

View Replies!   View Related
Modify/Edit File Properties Of CSV File
I need to modify/edit the properties of a CSV File. (Ex: Title, Subject, Author, Category, Keywords, Comments) through VBA

View Replies!   View Related
Ignore A Super Script
I am using the vlookup function to pull in values from another sheet that I will call sheet2. I am using a cell reference for the lookup value on sheet1.

On sheet1, the value in the reference cell has a superscript "1" that corresponds to a footnote at the bottom of the page. The corresponding lookup value on sheet2 does not have any superscript. Is it possible to make the vlookup function ignore the superscript for the purpose of the lookup?

View Replies!   View Related
Open Csv With Wordpad
I would like to Use my Excel VBA program to do this:

User Selectable: Open a CSV file using wordpad.

Once the program is opened I would like to search each row in the document for a name (value of 5 as a unique identifier) if the name exists then I would like to delete the entire row.

After the whole document is done I would like to close it and save changes.

The reason that I cant do this using excel is because the file gets screwed up, whenever I try to do the above in excel, even when I save in csv format and everything. The file has to be procesed through a nother program once I delete the unvanted info that is very picky about commas and any alteration in excel results in the file being incorrectly used.

The only method that I found that works is when I MANUALY open the csv in wordpad delete the lines I dont want and then close and save changes. Using this method the other program acepts the chaged file without a problem.

I can write the code to use excel to do it and it seams preaty simple. If what I need requires too complex an answer then please dont worry as I probably wouldnt understand it anyway.

I have included the code that does what I want in excel but results in the csv file being unusable.

'Read in path from setup area on front sheet and open "Open" box
DPath = Cells(2, "C").Value & Cells(2, "D").Value
ChDrive (Cells(2, "C").Value)
ChDir (DPath)
strFile = Application. GetOpenFilename(" (*.csv), *.csv", 1, "Select your System CSV file in Price Folder (Double Click on it)")
If strFile = False Then Goto 1001

View Replies!   View Related
Convert A CSV File Into A Formatted File
I tried many ways to convert a CSV file into a formatted Excel (.xls) file via VBA. I have a file with 5 lines (header included) and about 10 columns (delimited by commas).

How can I format it via vba on button click action?

View Replies!   View Related
Converting Csv File To Excel File
How can I convert csv file to excel file with proper columns filled with data being separated by "comma" in csv?

View Replies!   View Related
Open CSV Files By Date
I have a folder with several downloaded payroll files. I want to write a macro in my "excel payroll converter" file which will selects and opens the last file downloaded. (So I can copy and paste it into my payroll converter.)

View Replies!   View Related
Why Does Excel Open Some Files As Csv
I receive excel reports every day. But the last couple of days when downloading the reports I get them as csv. Also older reports I have saved on the hard disk open as csv.

Well, the ones I have saved have are now csv files and I haven't done anything (knowingly) to change them to csv.

View Replies!   View Related
Macro To Save As Csv All Open Workbooks
i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file.

Dim strFilename As String
strFilename = ActiveWorkbook.Name
strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv"
ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV
End Sub

View Replies!   View Related
CSV File Into Text File
Each week I am sent a csv file that contains leading zeros. I need to save the file as a csv in a pipe format. (I know how to do that.) If I open it, the zeros go away and I would need to use a formula to put them back and then turn the column into text to retain the zeros. Then I could save it in the format I need. The file contains thousand of lines and I'm trying to save time. I need to import the file into an application that needs the pipe.

Is there a way to convert the file into a csv in a pipe without opening the file.

I don't know all of the correct terminology, and I hope I have been clear.

View Replies!   View Related
Save CSV File As File
I’m using the follow code to rename a sheet, change a cell value, remove all formulas from sheet , then save the file to a specific folder and with the name of the file as the value of a cell.

My Problem is this a “CSV” file and it must be safe as excel worksheet but the code does not.

At the moment it save the file as a CSV file and change the sheet name to the file name – that’s not what I want.

Sub RenameSheet_SavebookFASE3()
Dim FPATH As String

Application.DisplayAlerts = False

Sheets("DATA2").Name = "Sheet1"
ActiveCell.FormulaR1C1 = 0

View Replies!   View Related
Opening Large File
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 Replies!   View Related
Export Large File
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 Replies!   View Related
Csv File To Xls
I have a csv file with x amount of delimits .. i would like to import xyz fields from ths csv file to an xls file.

after its imported i would like to sort the file by colA, colB then colC. AND i would like to remove any duplicates, again based on cols A to C.

at the moment for a file which has 8-9k rows, any new data i add is taking forever to sort and remove duplicates.

there must be a better way!

the way I do it is i import the csv to my working file, sort it by cola-c .. then i export to destination file, then again i sort by colA-C .. and then remove duplicates.

it seems sort and removing duplicates for xls files with about 9k rows of data is hardwork ....

View Replies!   View Related
CSV File Format
i have an issue with CSV file format when opening in excel, between Col G and L i have cells which are overlapping to the next cell, not all of them overlap but there is quite a few, is there a way i can fix this, i have 20000 rows of data, i dont want to be going through manually trying to fix.

PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De--------------------------------------------------------------------------------------------------------------------------------------------ABA001AUDCUST NOMINEES AUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N

This is what i want to achieve

PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De--------------------------------------------------------------------------------------------------------------------------------------------ABA001AUDCUST NOMINEES AUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N

View Replies!   View Related
Importing From A Csv File
I am importing data from a csv file into another program. For some reason this particular cell is causing everything past it in the same row to be thrown off. It is moving bits and pieces to the next column, then the next, etc....all in the same row though.

Can you see anything in this data that would cause issues in a csv file when importing. The data in this cell is html code for an ecommerce item description.

View Replies!   View Related
I am making a report that puts out a CSV file my trouble is that some of the numbers are converted to date when they are opened in excel. This report is sent out company wide and I need a way to keep the numbers from changing to date. I have added a tic(') mark to the front of the number but it comes over as '12308. What can I do.

View Replies!   View Related
Output Csv File
I'm wondering if it's possible to use a button in excel where I can output a cvs file (from another sheet in the file) with just a click on that button.

View Replies!   View Related
Macro -open The Look In List” And Stop So I Can Pick A File To Open
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box

View Replies!   View Related
Import LARGE Text File
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 Replies!   View Related
Large File - Stop From Lock Up
I have a file that is 40, 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 Replies!   View Related
Little Data Saves As A Large File
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 Replies!   View Related
Inserting Quotes Into A CSV File
I have a csv file that needs to be formatted and then saved as a csv file, and i have written code to do this but i need all of the values in the csv file to have one set of double quotes but at present all of the values do not have any quotes, how can i go about this.

Here is an example of the formatted csv file ....

View Replies!   View Related
Viewing A .csv File In Excel
is it possible that when i open a .csv file in excel i can make it only display some of the columns? The program i am using exports more info then i need and it gets very tiresome to delete each column manually.

View Replies!   View Related
Amend Csv File From List
I have a csv file (Import.csv) containing rows similar to the following:

001032,117.5,19/09/2005,Ref1,Ref2-1,100,44100,#Sales#,,Import routine
001033,129.25,19/09/2005,Ref2,Ref2-2,110,44100,#Admin#,,Import routine
I also have a worksheet with the following list (which will grow):

Cost Centre Codes


Excel tables to the web >> Excel Jeanie HTML 4

The deprtment codes in the csv file are all contained within '#' markings.

I need a macro that will find and replace all department codes in the csv with the relevant Code from the list, and remove the '#'s. Unfortunately, some of the fields are numeric with leading zero's so I dont want to lose these by opening and re-saving the file in Excel as a csv.

The output for the above is as follows:.........................

View Replies!   View Related
Saving CSV File (with Delimitator)
I have a spreadsheet file (excel 2003). I'd like convert it in CSV with delim.

But when I save it appear message that say "the file contains some things not compatible with CSV with delimiter" then I can save it and lose information. After when I open it with Word it looks ok but delimiter is ";"

I set delimiter in windows and inside excel too to ","

But I can't undertand what are things not compatible. there are only words and number as general format....(there aren't "," and each other strange character).

View Replies!   View Related
Copy Data From .CSV File
I am trying to automate a process that I do manually today. I have a specific folder that contains 4 .csv files. We will call them:


They are the only format that ARS Remedy can export in. I wish to copy the contents of these four .csv files to unique tabs in an Excel workbook. The name of each tab should match the name of the .csv file that provided the data. For exmple sheet1 would contain all the data from sheet1.csv and so on... I'm just learning the ropes to VBA and I am running out of time to play around with this.

View Replies!   View Related
Macro In Csv File Only Looks At First Line
I've download the CSV files from my bank. Somehow I thought they'd import painlessly. I dunno what's happened, these files are bloody ridiculous to look at.

Somehow I've ended up w/ Date, then Amount in the cell under it, then a description of what I bought in the cell below, and then something to the cell to the right of it (a further description sometimes, at other times nothing).

I have about 1500 lines of this so doing this automatically is not in question. I want to figure out how to run a macro to organize this. The logic is fairly simple. Where-ever the cursor is, copy the stuff in there over 2 lines, go back to original starting point, copy next cell, paste it next to where i previous pasted, goto third line, copy, paste next to second place i pasted, and then goto next set of data/dates. So I did that once figuring it could keep repeating the cycle. NOOOO that didnt happen. I did this for one set and then just stopped. Everytime I run the macro it deals w/ the same data. For example I created the macro to start of w/ cells A2, A3, and A4, then goto A6 and stop. I figured once there I could run the macro again, and it would do the same stuff for A4, A5, and A6 that it did for the previous one. Nope, it went back to A1-3.

Of course if someone can give a better idea of how to work w/ CSV in the first place that would also help. It just never made the colums in the right place. I got to choose the variables that would decide when a new column starts but I was never really getting the result I wanted. If this macro works though I wont need to work w/ that anymore.

Here's a sample though of how my CSV looks at the moment. These are three entries. The first one's description takes up one cell, the next 2 have 2 cells each.


View Replies!   View Related
Macro To Write CSV File
I have a CSV file that contains data in columns A-Q. Some cells may be blank.

I'm having trouble opening this CSV file because when I re-save it after editing it, Excel removes any quotes that were around the data.

Here is the criteria that the data must be formatted like:

If the value in column A = "H", the following columns need quotes around the cell - A-F, H-I, K-M, P-Q. The values in the rest of the columns do not need quotes.
If the value in column A = "D", the following columns need quotes around the cell - A-D, F, I-O, Q. The values in the rest of the columns do not need quotes. Data is only in columns A-Q.

Can somebody write a macro that will format the data as list above and save?

View Replies!   View Related
Auto Complete From .csv File
I have desinged an excel invoice, but I think I might want it to do the impossible.

I have my companies database as a .csv file, is there any function where I can enter in the company account number and it will automatically fill in the company name, address, contact etc like a mail merge type thing and for it to work vice versa enter the company name and it fills in the account number and address all automatically taken from the information on the .csv file?

View Replies!   View Related
Macro And Creating CSV File
I have a text document to which i need to do some changes and then turn it into CSV. Here are some lines of what the document looks like and below i will show you what i need it to look like. So i open the document with Excel and next I'm not sure how to do what i will show you below. Original:

HTML 111text

Here is how i need it:

HTML 1,1,1,"text"

View Replies!   View Related
Save As CSV File (with Script)
I'm trying to write some script which will save a worksheet as a csv file. I can get it to create the csv, but the system requires that any text formatted values be enclosed by "". So I wrote a little script which adds the "" around text values prior to saving as csv. To my bewilderment, the result was text surrounded by 3 sets of "". So TEXT should be "TEXT" but came out """TEXT"""!

I've attached the original xls spreadsheet (Example Feeder.xls), the csv file produced by the main code below (Example Feeder.txt) and the csv prodcued by adding the code at bottom, which wraps all text values in "" prior to saving (Example Feeder (with quotes).txt).

View Replies!   View Related
CSV File With More Than 65536 Rows
I have a CSV-file of 30 Megabyte. But if I open it in excel he says that he could not open the complete file?? The maximum rows of excel are 65536. Can I enlarge the number of rows that excel can handle or is there anybody with the same problem and wrote an program for it in excel, VB or maybe word??

View Replies!   View Related
Save List As CSV File
I have email list into excel as four columns: email, firstname, lastname, telephone OK. I want to put the data out as a csv list? I seem to get loads of tabs and double quotes which I don't need?

View Replies!   View Related
Editing A Csv File With Vba
I have two CSV files that I would like to edit through Excel VBA. Both files have too many lines to be shown in Excel though, so I can't just use cell rerences. Every so many lines I want to delete a row and insert a row from one CSV file to the other. I could easily write a loop do this with cell references, but since all the lines aren't shown I can't do that. I have no idea how to reference all the rows in the CSV file with VBA when most of them aren't viewable on the worksheet.

View Replies!   View Related
Start New Line In CSV File
how to write a new line to a csv file? Or how to write a "Character return" to the file? I use the following code to write a line but I don't know the command to start a new line. Write #1, "abc",

View Replies!   View Related
MM:SS Data In Csv File Misinterpreted As HH:SS
I've got csv files with data formated as MM:SS. When opened in excel, it is interpreted as HH:MM. I then use Excel custom format mm:ss to correct the problem but this produces another misenterpretation. e.g

csv value: 33:12
open in Excel: 33:12:00
then apply mm:ss format: 12:00

Without post-processing the data, is there any quick way to force excel to inerpret the csv data as MM:SS?

View Replies!   View Related
Copy 40 CSV File In To One WorkSheet
I have about 50 CSV files in a directory named C:POSTAL. Each file has about 4000 records with three fields, Name, Address, Phonenumber. I would like to find some way to combine them into one large CSV file named ALLRECS.CSV so that other parts of the program can open that file and search it by Phonenumber, so I can display the Name and address on the screen. Obviously it is too large for Excel, at the moment. In old DOS you were able to use

copy *.CSV ALL.CSV"

to combine all the CSV files into one. Every now and then I want to delete the old ALLRECS.CSV file and replace it with a new one, as we update the phone numbers in the individual 50 files. For example I would like to use a button so the user can simply update whenever they wish by clicking on it, deleting the old and adding the new.

View Replies!   View Related
Splitting A Large File Into Two And Mining The Data
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 Replies!   View Related
Referencing To CSV File In Strange Format
I am working on an excel dashboard to display life information about energy consumption.

there is currently a bespoke syatem which does this but we need the dashboard to be changed so the job has fallen to me.

Energy usage across the site is recorded in a spreadsheet (automatically) every half an hour and is saved in a folder named CSV data, however the files are saved in a format which i assume is something for the current package: *.ew7

I can open and view the data by clicking "open as" and selecting excel.

I can reference to this data from another excel spreadsheet (dashboard) whilst they are both open it will update.

However when i close all spreadsheets and open just the dashboard it tells me that the links cannot be updated.

I need a solution or a way around this so i can reference from one workbook to another.

Has anyone had any experience of this kind of thing? can anyone offer a humble analyst a bit of advice?

I was not able to attach the file here (as its in the ew7 format) but i have uploaded it to rapidshare for anyone who wants to take a look...

View Replies!   View Related
Saving To The CSV (comma Delimited) File
I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess

What it looks like NOW

What is looks like after save


If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.

View Replies!   View Related
Copyright © 2005-08, All rights reserved