Importing A CSV File And Sorting The Data

Sep 12, 2008

I have a group of CSV files that I will need to periodically import and sort in Excel.

The CSV files will contain one column I wish to use (It is the first one and the rest can be ignored or imported) and sort the desired data based upon the existance of a particular bit of text. Each entry will either be just a username or a username with a '-label' appended to it so for instance each of the CSV files might contain:


Basically I want to seperate the names with the -label from the ones that do not have the -label. Doing searches, I was able to get part way using the code from this thread:


But am running into problems trying to modify it to suit my needs. Is there an easy way to accomplish this? I'm not very good with VB so that hasn't helped me. It would be nice to move the sourted output to seperate columns if that is possible.

View 9 Replies


Capture Name Of Data File When Importing Into Another File

Aug 7, 2007

I am using the following code to browse to a CSV file and import it into my current workbook. How can I “capture” the name of the text file (myFile) for future use?

myFile = Application.GetOpenFilename("All Files,*.csv")
If myFile = False Then
Exit Sub
End If

' Open file
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("A1"))
.Name = "DATA"
.FieldNames = True...............

View 9 Replies View Related

Importing Data From External File

Apr 13, 2009

I have bit of code that opens an external excel file (call it the source workbook), grabs certain data, copies the data to other excel file (call it the destination workbook), and then closes. I have most of the code completed but am running into an issue with how to grab certain data from the source workbook and copying it to destination workbook.

I want the code to read the data in the source workbook line-by-line.

For each line that is read, I want to copy part of that data (value in column A and column B) to column A and column B of destination workbook only if the value in column D is equal to “Block”. For column C and column D, I want to do some form of lookup, being I want the code to look at each line of data until it finds corresponding line in which column A = “60612105” and in which column B = the value in B. Once this line is found, copy the value in C to column C of destination workbook and value in D to column D of destination workbook.

I have attached two files. First file is the import file (destination workbook) which has two tabs; (1) import tab (2) desired result. Second file is the raw data file (source workbook). Hopefully this makes since.

View 8 Replies View Related

Importing Data From Another Excel File Automatically

Mar 4, 2007

I am trying to have the total (cell e78) from one of my own excel files automatically filled into a cell in a different excel file when the number of that file is typed into a third cell.
i.e. if the total on Ticket 4126 in cell e78 is $4500, then when I type 4126 into the Ticket # cell on my seperate Invoice I'd like it to fill in the third cell under Amount as $4500.
Is this possible?

View 10 Replies View Related

Importing Selected Data From CSV File To Excel?

Sep 17, 2013

is it possible to import just a portion of a csv file into my excel workbook? For example:

My csv file has data from 2010-2015. I want to somehow import only data between 2013-2015 from the csv file into my excel workbook every time I click refresh.

I cannot delete data from the source file because many other people are using the same file. Is there some sort of "SQL" for excel that makes it possible to trim csv file?

View 1 Replies View Related

Importing Data Into Excel With HTML File

Feb 15, 2012

I'm working on creating a custom HTML report with javascript that I will be using to import in to excel to populate some graphs. Basically everything is working, except if I use javascript to show me a value.

When I use Import Data from Web via excel, everything gets imported except for the javascript part of the html doc.

An example of the html file (stripped down to nothing but 3 peices of data): [URL]

View 6 Replies View Related

Importing Data From Multiple Documents Into Single File

Sep 28, 2009

I have thousands of Excel files, each with a generic names (i.e. 2009092812163503.xls)

Each of them contain a header with contains column titles like Company Name, Executive Contact, Address, etc and then a single row of data for a single company.

I want to be able to task excel to extract all the data in the second line and enter them all into a single spreadsheet for further work.

We're talking 30,000+ unique files here, what would be the best way to approach this?

View 10 Replies View Related

Selecting And Then Importing Data From Huge Tab Text File To Excel

Apr 11, 2012

I wanted to see if there is a VBA code to do the following :

a) Select a TabText Delimited file based on a criteria
b) Import the Selected Data to Excel

I have the vba code where I can open the tab text delimited file in excel, use a selection criteria and then copy the data into excel. But I am having problems with the case where the Tab Text Delimited file exceeds the row limit that excel currently has and wanted to see if the data import can be done without opening the text file into excel at all.

Here is my current query :

Sub TEST()
' TEST Macro

Workbooks.OpenText Filename:="E:Testdata.txt"
ActiveSheet.Range("$A$1:$B$1048576").AutoFilter Field:=1, Criteria1:="1234"
Range(Selection, Selection.End(xlToRight)).Select

[Code] .......

View 3 Replies View Related

Macro - Importing CSV File Into Specified Worksheet Overwriting Existing Data?

Sep 5, 2013

- Construct a macro to import a csv-file to my worksheet named "Info". The data in the .csv-file should start in row A5 in my worksheet.

- If the worksheet "Info" already has data, I want to overwrite the existing data with the new data starting in row A5

The directory of the csv-file is C:Testmycsvfile.csv

View 3 Replies View Related

Out Of Memory While Importing Data From CSV File To Create Pivot Cache

Dec 20, 2013

We are trying to create pivot cache from an external .csv file.

This works fine when the csv file is small (~500 mb).

Attaching sample code for reference -

Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim SQL As String

''Creating ADODB connection object

[Code] ...........

View 5 Replies View Related

Importing CSV File Into Excel With Approximately 1.9 Million Rows - Not All Data Being Imported

Feb 14, 2014

I have a number of large CSV files with approximately 1.9 million rows, (this is more then excel can bring in). I typically have to import/delimit the files when brining them into excel. However, when I try this I get the message not all data imported. I would like to be able to split the records in the csv file to multiple tabs in the excel woorkbook when doing the import/delimit but do not know how to accomplish this. The delimit of the data varies each time due to the nature of the data so doing a macro is more trouble to create each time. Currently I am having to open the csv file in notepad and split it up into multiple files then import each new file seperately.

View 4 Replies View Related

Sorting Irregular Data From CSV File?

Jul 18, 2013

im looking for a way to sort some data exported from an old stock recording software. the system outputs the data as a .csv file but each item bought is grouped into a particular heading, a line is only generated if money is spent under that particular heading. i cannot make the software generate lines if no money is spent so the number of lines generated each period changes.

period 1
3202 fuel £3000
3203 PPE £250
3204 major units £5000
3205 planned maint £212
3207 unplanned maint £457
period 2
3202 fuel £2500
3203 PPE £120
3204 major units £1000
3208 storage £212
3210 windows £457

if the data was regular i could simply add the totals each month and divide to get the average.

what i am looking for is a way to auto sort the data onto lines by possibly injecting the missing headings with zero as the total spent.

View 1 Replies View Related

Importing From A Csv File

Jun 3, 2009

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 3 Replies View Related

Importing A File

Aug 8, 2006

How can I limit the number of lines when importing a file? Limit the import
to the first 53 lines of a file that has 50,000+ lines.

Want to import the first "XX" set of lines, then I want to skip a set of
lines, then continue to import the rest of the lines. The second import needs
to be inserted 2 or 3 lines down from the first import.

There is the function:

Workbooks.OpenText Filename:=

But it only has a StartRow and can not specify the "EndRow". Also can not
specify where to start in the worksheet, i.e. Cell A5

View 10 Replies View Related

Importing From Some Other File

Jun 13, 2007

I have made a macro to import the a excel file into a new file after truncating few initial rows. Here is the code for it:

Sub Import()
Dim Prompt1, Prompt2, Prompt3, myDir, myFile, mySheet As String
Dim ClCnt As Long 'column counter
Prompt1 = "Enter the location of input file:"
myDir = InputBox(Prompt1)
Prompt2 = "Enter filename:"
myFile = InputBox(Prompt2)
Prompt3 = "Enter sheetname:"
mySheet = InputBox(Prompt3)
With Workbooks.Open(myDir & myFile).Sheets(mySheet)
ClCnt = .Range("A33:CB33").SpecialCells(xlConstants).Count ' Count the number of columns present..................

View 5 Replies View Related

Importing XML File Into Excel?

May 27, 2014

I have a multilevel XML file. Attached (XML files cannot be uploaded as is, hence I had to zip it). When I import the XML into excel, it creates multiple columns for the multilevel data. However, I need the multilevel data as additional rows. I have attached another excel file sample.xlsx which shows how excel imports the XML and how I actually need the data to be.

Screen shot attached for your quick reference sample.xlsx.jpg

View 1 Replies View Related

Importing A Pdf File For Analysis

Aug 13, 2008

What's the technique for opening a .pdf file and copying its contents into Excel for subsequent analysis by VBA?

On my Mac version of Excel there doesn't seem a facility to import it or even copy and paste it. Is this something that's available in Windows versions of XL?

View 10 Replies View Related

Importing Pictures From File

Aug 15, 2009

I'm trying to create a geography project for my upcoming primary class but my Excel expertise is limited.
I am trying to import small jpg's from a folder and then make them appear in a userform or Msgbox. Is it possible?

So far I've created a very simple way to display the text in a message box but I would also like to be able to display the flag. Macros 2 & 3 are initiated by clicking on images while macro1 displays the information:

View 3 Replies View Related

Importing A Larger File

Dec 12, 2006

I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest
in D.

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.

Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object

'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") ........................

View 9 Replies View Related

Importing .txt File With Wildcard

Sep 10, 2007

I am trying to create a macro that imports a text file that has a changing name (it has date/time info in it). The file name will have some consistent parts and the rest I hope to be able to use wildcards on. In the code below, the path will be constant and the file name will always have "Heavy Water" in it. How can I change the code to import any version of the file?

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersG-MoneyDocumentsEVElogsMarketlogsThe Forge-Heavy Water-2007.09.09 184540.txt" _
, Destination:=Range("$A$1"))
.Name = "The Forge-Heavy Water-2007.09.09 184540"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells

View 9 Replies View Related

Importing Text File

Jul 7, 2006

importing a text file. 3 times per day, I pull a report off a website and save it as a txt file. Typically, the data consists of approx 2000 customer ID's, and their respective data. When I import the data into Excel, it uses 2 or 3 rows to list information for each customer ID. I need a to seperate out only the customers ID which are 9 numbers long (no text in the ID), their SS #'s, and are listed as "FUL Request" or "PTR Request".

Since the data takes up 2 or 3 rows, the "Text to Column" feature does not work.

So the VBA should be able to look at each row, and if it has a 9 number customer ID in the row directly above it, and meets all other criteria, them post the customer ID number, the SS# and the verbiage "FUL Request" or "PTR Request" onto a seperate sheet.

Or the VBA can simply delete the rows that don't meet the criteria listed above.

View 3 Replies View Related

Importing Text File With VBA

Oct 3, 2006

I am using Chip Pearson's method of importing a text file into Excel (found at )

It works great except for one aspect. Two of my columns of data are in the format "dd/mm/yyyy hh:mm:ss", which is very necessary.

I have set the worksheet, the file is being imported into, to the same format. My problem is that, during the import of data, Excel (in it's wisdom) decides to automatically transpose the dd and mm numbers (if they meet the natural calendar conditions i.e. 01/09/2006 becomes 09/01/2006, 09/01/2006 becomes 01/09/2006 whilst 21/09/2006 remains the same).

As you can imagine this causes havok with several other functions that I have running off that source data (once imported). I have looked at the code and, whilst I can follow the general gist of it, I am unable to find any reference to format of the data.

View 9 Replies View Related

Importing Text File With Formatting

Jun 2, 2009

I have a program in unix that ouputs a text file in .csv form which allows me to import my data into an excel spreadsheet very easily. I am wondering if there is anyway I can add formatting, like to tell excel to make a cell green or something like that.

If it can't be done with .csv, is there any other tool or format available that will accomplish this?

View 9 Replies View Related

Importing A Partial Text File

Oct 30, 2008

I have VBA code that imports a text file using a fixed width separator within the code. The code is working just as I need it to work and placing the information/data in the columns that I have specified in the code.

The issue I have is I only want to import certain data that is contained within the text file. It is going to be a little hard to explain, so I have attached a sample of the text file with dummy data to better explain. The file is broken up into different segments. Each segment is identified by a “Base Number” in column A of the text file (once it has been converted into an Excel file).

For my example, I only want to import data for Base Number 38451. The information for this segment is easy enough to identify when the data starts and when the data ends. As in this example, the first occurrence of 38451 begins at row 257. Once the first instance of this number is identified, the first row of data that I require is always two rows after.

I think the coding should go something like this, but I just can’t figure out how to put it into my
If cell in column A = 38451 then
- start importing from 2 rows down
- continue to import until next row in column C is not = date

If row in column C = blank then
- search in column A until cell = 38451

If cell in column A = 38451 then
- start importing from 1 row down
- continue to import until next row in column C is not = date

- stop import

I have included two files. (1) the text file with the data (2) the Excel file. There are two tabs in the Excel file “ReconSheet” (contains the data that is imported from the text file) and “Desired Result” (this is how I want the end product to look).

View 5 Replies View Related

Importing Values From .xml File Into An Excel Row?

Jun 16, 2008

Basically, I have a column that contains a list of names of files without the extension, ie "A2=File1" What I want, is for excel to open the file associated with the name in the column, "(A2).xml", and extract a value from that.xml file and insert it into B2.

I have searched and all of the examples I find aren't quite what I am looking for. I have an add-in that works exactly the way I want it, but does not extract certain values that I need it to. This is a commercial add-in so I don't have the source code for it or else this would be a snap.

View 9 Replies View Related

Importing Tab Delimited Text File

May 4, 2009

am in a time crunch to write VBA code(not use the macro recorder) to do the following:

Input data from a tab delimited Text file into a pre-formatted sheet.

Each column of the text file is as follows (product code, buy/sell, quantity, price, date) Example row below.

YM Buy 50 12800 1/2/2008

Is there code I can use to do this? I need each individual piece of data to import into it's own cell while keeping the Row/column alignment of the original text document.

View 9 Replies View Related

Filter Text File While Importing

Jul 5, 2007

I am importing a text file, Comma Seperated, small sample below....

View 9 Replies View Related

Sorting By Variable File Instead Of Specific Named File?

Jul 18, 2014

I recorded what I wanted but don't know how to generalise it so regardless of sheet name it can be sorted instead of specifically looking for "leanne final test" worksheet and sorting it by precise cells.

I have starred the section out so you don't have to focus on the rest of the code. Initally I ask for the file to be opened using WeeklyFN, would I need to use it again somehow ?

View 4 Replies View Related

Importing Text File With Complex Structure?

Dec 15, 2011

I run reports that tell me when an individual is supposed to be in the office and when they actually log in, but this is a manual process where I have to go through several hundred people each morning and check. I have created a macro that pulls in their time in and out and makes there name appear as Last, First.

I run a seperate report that creates a schedule with headers and other information that is useless in this process. I have attached a dummy file and get this down to the last, first name and then the times in and such with no headers? I am also trying to keep the total number of people at the bottom in it's own cell.

I am at a loss as the file uses multiple spaces and not ,'s or ;'s etc.


From: 12/14/11 Data TotalView 12/14/11
To: 12/14/11 Company ABC 13:45
Daily Start/Stop Unit Name RED


View 2 Replies View Related

Importing Text File And Getting Sheet Hidden

May 24, 2006

Need Macro where I can import a text file saved on my computer into a new sheet named yesterdays or todays date in format "ddmmyy" and then the sheet gets hidden.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved