Free Microsoft Excel 2013 Quick Reference

Import text file with criteria

Hi.

Example of text file:
HTML Code: 
|0000|002|0|||01012012|31012012|NOME|87654093000192|ES|31325309||00|2|
|0001|0|
|0100|nome do responsaveil|08988938993|RR9999999-9||31987280|NOME DA RUA|269||CENTRO|4287654800|4287493480|reg@DDD.com.br|7699999|
|0110|3|2|1|
|0111|4251311,98|542042,96|0|0|4793354,94|
|0140|04|NOMEDEQUEM GEROUAINFORMACAO|86366363636366454|766666666|9854388|||
|0150|SA10405289892|DFSDFSFSFSFDFSDFSDSFS|1058||99999999990||2787609||RUA MMMMMMMM|14||KKKKKK LLLLLE|
|0400|2102|TEXTO DA OPERA|
|0400|5102|IUTROTEXTOOPERACIONE|
|0990|11002|
|A001|1|
|A990|2|
|C001|0|
|C010|22222222222222|1|
|C100|0|1|SA104MAPBH104|01|00|1|139816||02012012|02012012|129,9|9|0|0|129,9|9|0|0|0|129,9|22,08|0|0|0|2,14|9,87|0|0|
|C170|1|9999999999999|NOMEPRODUTO|1|PC|129,9|0|0|000|1202|1202|129,9|17|22,08|0|0|0|0|||0|0|0|50|129,9|1,65|||2,14|50|129,9|7,6|||9,87||
|C100|0|1|SA10411444901|01|00|1|139819||02012012|02012012|59,96|9|0|0|59,96|9|0|0|0|59,96|10,19|0|0|0|0,99|4,56|0|0|
|C170|1|789136172|XIC CHA QUARTIERE|4|PC|59,96|0|0|000|1202|1202|59,96|17|10,19|0|0|0|0|||0|0|0|50|59,96|1,65|||0,99|50|59,96|7,6|||4,56||
|C990|31991|
|D001|0|
|D010|88888888888888|
|D500|0|1|S22288888 01|21|00|||3984|12012012|23012012|5,26|0|5,26|0|0|0|0|0||0|0|
|F01|0|
|F10|99999999999999|
|F00|0|1|SA9999941 01|21|00|||3984|12012012|23012012|5,26|0|5,26|0|0|0|0|0||0|0|
|M001|0|
|M010|66666666666666|
|M500|0|1|S87878781 01|21|00|||3984|12012012|23012012|5,26|0|5,26|0|0|0|0|0||0|0|
|1001|0|
|1010|77777777777777|
|1500|0|1|TTTT77777 01|21|00|||3984|12012012|23012012|5,26|0|5,26|0|0|0|0|0||0|0|
|9001|0|
|9010|23333333333333|
|9500|0|1|KJ7777771 01|21|00|||3984|12012012|23012012|5,26|0|5,26|0|0|0|0|0||0|0|
This data is imported into Excel and in the Sheet1 records that begin with 0 (zero) should be copied on Sheet 2, beginning with the letter "A" to Sheet3, those that begin with "C" for the Worksheet 4 with "D" for Worksheet 5, "F" for the Sheet 6, "M" for Planiha 7, with the number "1" to 8 and the sheet with the number "9" for 9 Worksheet.
Already I am grateful for the interest and help


I know this is a "redundant post" but the topic description i had befor
wasn't very true to the subject.. and i deleted the other post

Using Excel 2000 9.0.3821 SR-1

So ive got this text file that is dumped from our reports server an
unfortunatly it isnt csv... so i've imported it, set columns an
created a new sheet for the data to pull to... however the data i wan
(theres 3 rows per job) skips every 6 rows... i tried using an offse
and then dragging it, but when i do that, it still increments by one..
also, due to pagination this set of 6 rows happens 7 times(7 jobs pe
page), then skips 30 rows and starts over... so:

Row1-Data
Row2-Data
Row3-Data
Row4-Skip
Row5-Skip
Row7-Data
Row8-Data
Row9-Data
Row10-Skip
Row11-Skip

and so on for 7 occurances, then the diff between the next set of
data rows during pagination is 30....

The data also comes from multiple columns...

For Instance my results sheet is 18 columns wide...
Results Imported
Col1 = A1
Col2 = D1&E1&F1
Col3-10 = C2:J2
Col11-18 = C3:J3

This represents one job in one row in the results file

any ideas? i can post images of the imported text file with highlite
contents desired and an image of the destination sheet if that helps a
all...

i dont know any marco coding but this is kinda whats needs to happen

//Easy macroing I can perform
Open Text Tile
Set the fixed column widths
Rename sheet containing text file to "closed"
Open new sheet
add headings to columns in new sheet

//Where the macro prolly needs hand coding
//I don't know how to convert this conceptual code into VBA

//The number of rows in column A of imported text file
$numrows=#rows in col A in sheetname"closed" - 6 rows from where a cel
in column A contains the word(corporate);

//row to start parsing from in imported file
$startrow=row# from sheetname"closed";

//row number to start adding data to in the new sheet
$currentrow=5;

//parse range
for (row=1,1

I'm running a program that creates a text file with some blnak lines and hard return keys. Please see the attached file.

How can I import this type of file into MS Excel?

Your assistance is appreciated.

Thanks

Hello.
I'm trying to import a text file with an Excel 4 macro. I tried the Open.Text function but I cant assign a date (YYYYMMDD) format to the first field.
How to perform this. An exemple would be welcome.
TIA.
Daniel

how can i create a text file with tab characters in it, so when i import that
file with tab delimeters, it will work correctly

thanks much
nrao

Is there a way to import a text file with no line delimiters without
resorting to VBA? The file I need to import has field-width fields
padded with spaces. The first field of the next row starts immediately
after the end of the last field of the previous row -- there is nothing
to indicate the end of a line/record.

I have used the import wizard to import similar files with line
delimiters and it does just what I need. If there is a way to do the
same thing with this wretched delimiter-free file, I will be very
pleased to hear it.

--
mikewilsonuk
------------------------------------------------------------------------
mikewilsonuk's Profile: http://www.excelforum.com/member.php...o&userid=35404
View this thread: http://www.excelforum.com/showthread...hreadid=551781

Is there a way to import a text file with no line delimiters without resorting to VBA? The file I need to import has field-width fields padded with spaces. The first field of the next row starts immediately after the end of the last field of the previous row -- there is nothing to indicate the end of a line/record.

I have used the import wizard to import similar files with line delimiters and it does just what I need. If there is a way to do the same thing with this wretched delimiter-free file, I will be very pleased to hear it.

I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours.

P.S. Please keep it simple if possible, Excel is all new to me.

TIA

I am importing text files to Excel and they contain mathematical operators as
part of the data. (For example "++" to denote a quantity of something.) Excel
will not allow these characters as it thinks they are mathematical. Is there
any way of stopping this happening?
Thanks in advance

When importing text files into an Excel worksheet I previously went to
"data" > "import external data" > "import text file"

With Excel 2002 my choices lead me to a"select data source" window which is
much more cumbersome. Does the "import text file" still exist in excel 2002?

When importing text files into an Excel worksheet I previously went to
"data" > "import external data" > "import text file"

With Excel 2002 my choices lead me to a"select data source" window which is
much more cumbersome. Does the "import text file" still exist in excel 2002?

I am importing text files to Excel and they contain mathematical operators as
part of the data. (For example "++" to denote a quantity of something.) Excel
will not allow these characters as it thinks they are mathematical. Is there
any way of stopping this happening?
Thanks in advance

Hi,

I'm new to these boards and any help would be GREATLY appreciated!

I am using Chip Pearson's method of importing a text file into Excel (found at http://www.cpearson.com/excel/imptext.htm )

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.

Is anyone able to enlighten me as to how I stop this occuring when importing a file?

If more information or clarity is required please let me know.

TIA for any help offered.

Cheers
KJ

Hello - I am trying to create a macro that allows me to import a text file via the import wizard. However, when I record the macro, I don't know how to modify the code so that the macro prompts me for the file name before importing. This would allow me to use this macro to open different file names. (the current file name is "PODetail_Orthopedic.txt")

Sample of recorded macro code:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:UsersjohenryDocumentsAscensionClient DataSpendPO DataPODetail_ORTHOPEDIC.txt" _
, Destination:=Range("$B$7"))
.Name = "PODetail_ORTHOPEDIC"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

I am wanting to use VBA code to open a text file with the
import wizard. Does anyone know how I would invoke the
import wizard to open a text file in VBA? Any assistance
on this matter would be greatly appreaciated.

Hi

I have a text file that has our customers emails separated by commas. I want
to get this list imported into excel with each of the email addresses
appearing on a separate row in one column.

I tried the import wizard and I get one of two results, all the emails in
separate columns. Meaning they all show up in Row A under Column A-ZZZ, or
they all show up in Row A Column A.

The goal is to get each one in a separate row all under column A.

Help is greatly appreciated.

Thank you

Brandon

Hi,

I have come across a problem to import data from a text file into excel. I have previously used software called Monarch Pro from Datawatch which was magical and converting data from text to excel was a breeze but unfortunately I do not have access to that software anymore. I shall try to explain what I want to do. I have hope that one of the brilliant brains in this community will be able to come up with a solution.

I have a text file with some headers (in rows) and then data in columns. The text file has page breaks. So if some data specific to a particular header is large, it will appear in multiple pages. Each page will repeat the headers. In case of less data, the next set of data appears in the next page. Please refer to the attached files.

What I am trying to achieve is that when I import that data into excel, the currency and account number (Headers) should become part of the data in columns.

The currency and account number will change as the headers change in text file.

Please note that the data is very huge. So it is almost impossible to import the data through “Import External Data” option and then manually insert columns and add the headers into columns. I’ll be grateful if this can be achieved through a macro.

PS: The calculation in the enclosed text file is not correct because I just copied and pasted the data without calculating the correct balance in the last column.

Hi,

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?

Thanks,

kissfrito

how could I import a text file with comma separated values into excel?
Thanks in advance

Id like to start by saying, You all have been a God send for me and i am glad that i joined this fourm. Not only am i learning alot but im able to complete projects that have been thrown aside due to lack of knowledge. So here is my issue. I found this code below on another site and would like to use it or maybe someone has a better idea. I need to import text files into my active sheet.

The text files are created by a magnetic stripe encoder and look like this: "%Fname Lname address city st. zipcode telephone?;member number?"( minus the "" they are there for seperation factor only. % . ; ? are there just as you see them) so im sure the data will have to be trimed. Field names are as follows in this order:

Member Number, First Name, Last Name, Address 1, Address 2, City, St, Zipcode, Phone

What im wanting to do is:

1: read the card with a mag stripe reader into a "collection window" so to speak and have it input the data into the named fields OR

2: have a macro that can open a selection window so the user can choose the text file then have the macro read the text into the named fields.


	VB:
	
 ImportTxt() 
     
     ' Modify the constants below according to your import configuration
    Const qName = "Pro_Co_Serv_Eval_NM" '  0 Then 
                n.Delete 
            End If 
        Next 
         
    End With 
     
     ' Something else
     ' ...
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have been readin here off and on for a year, and have always found what I needed by doing plenty of reading.

Finally I have an issue that remains unresolved. (actually two, other addressed under separate cover).

I am trying to figure out how to use VBA to instruct Excel to import a file of the user's choosing with something like the "FileSearch" method, etc., but I still need Excel to apply the formatting (Column arrays, etc.) as with the "OpenText" method.

Help! I am lost!

-Liver :o

If I manually import a .txt file with the DATA menu "Import Text File"
and specify column 1 as text and columns 2, 3 and 4 as general I get
de result I wanted. Fractions like 10/15 are shown as such.
I recorded this proces and , except for the first 2 lines, the code I
read is this :

Main.Worksheets.Add(After:=Worksheets(Worksheets.C ount)).Name =
"Import"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FTO,
Destination:=Range("B1"))
.Name = "VanHool4_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "!"
.TextFileColumnDataTypes = Array(2, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With

Then I wrote a sub where I added the first 2 lines ; the rest I just
pasted underneath. It works fine but SOME ratios in column 1 , like
10/15 , now show like oct/15 which I don't want.
Could anyone help me here? I really need that original 10/15 as
text.

Thank you very much for any input.

Herman

Is there a way to create a pivot tabel where the source data is a text
file with more than 65000 rows of data? Normnally I just import the
text file to excel and then create a pivot, but in this case I have too
many rows but don't want to lose data.

I guess I can use the create pivot tabel from external data source
function but could somone take me through the steps to do this from a
large text file - if this is possible!

Many Thanks.

--
morchard
------------------------------------------------------------------------
morchard's Profile: http://www.excelforum.com/member.php...o&userid=24761
View this thread: http://www.excelforum.com/showthread...hreadid=383306

I have a text file with over 2,000 names that look like the following:

Mr. Benoit Smith
120 Adam Street
Dorchester, MA 02124

Marcus Darbouze
1553 Blue Hill Avenue
Boston, MA 02126

American Public Health Initiatives
10 Fairway Street
Mattapan, MA 02126

Most consist of 3 lines some as many as 5. The text file has no
delimiters so that when I import/open it in Excel it becomes just one
very long column. I need to convert it or parse it into any other
database friendly importable format i.e., .dbf, .xls, .csv etc. What I
need is to convert it into a table with up to five header rows:

head1 head2 head3 head4 head5

Please help, I've been at it for way too many hours.

P.S. Please keep it simple if possible, Excel is all new to me.

TIA

I recorded a macro to import a text file using Get External Data,
Import Text File. It works fine until I went to change the name of the
file I am trying to import, to a variable that gets created when the
macro is run. It hangs up on the last line: .Refresh
BackgroundQuery:=False it says that: Excel cannot find the text file
to refresh this external data range. Check to make sure the text file
has not been moved or renamed, then try the refresh again.

I am creating this text import fresh every time so I am not trying to
update a existing query with data from a different file.

The file name that I am importing the data from is:
prowlerftpsharemfgGT_GAGERPT_030710.CSV

If I put this text in directly for the variable "Report" in the macro
it all works fine. It is just when I pass it this variable.
Do I have a problem with leaving my variable a Variant type or some
other problem?

Thanks for any help.
Scott

Here is my code:
Sub CreateReports()
a=prowlerftpsharemfgGT_GAGERPT_030710.CSV
gagereport(a)
End Sub

Sub GageReport(Report)
Sheets("GagesDue").Select
With ActiveSheet.QueryTables.Add(Connection:="text;Repo rt" _
, Destination:=Range("A1"))
.Name = "GT_GAGERPT_030626"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End sub