I have what appears to me to be a unique situation. I have a text file with raw data that is used for a report. The software uses segment identifiers to place data where it needs to be. I know what the identifiers are and what they mean in the report. I have been asked to put them in a format so that the data can be either sorted differently than what the report allows and to be able to link to other data to add city and state to store numbers in the report. The text file is delimited with an * but you have the segment id's on the line before the group of data and most of the data when looking at the line is formatted differently. Is there a way to import data using segments to isolate the data to different tables, i.e. items sold to one table, items on hand to another table and dollars sold to another table. I can manipulate the data once it is in the tables, I am just having trouble getting the data to the tables. This is small part of the text file. The entire report is 84 pages, there are almost 500 lines in the text file.
LIN**SK*175856*VP*24-ACD*UP*794909241502~
ZA*QS~
SDQ*EA*92*0522*.01*2769*.01*4129*.01~
SDQ*DO*92*0522*.01*2769*.01*4129*.01~
ZA*QE~
SDQ*EA*92*0522*2*2769*1*4129*1~
LIN, SK, VP,UP, ZA, QS, SDQ, EA, DO, QE are segment ID's and the ~ is end of line. The delimiter is the * .
Any help would be most great appreciated.
Full Version: importing text file
Hi Charles,
One way or another you will have to parse the text and do some cleaning of it.
A great way to "pre-digest" the text is to bring it in using the import wizard from the menu: File, Get External Data, Import. Select Text Files from the File Type combo. As you step through choose Delimited then Other for the delimiter. Type * as the delimiter and continue through to the end. This will split the data into fields and records, loose the delimiters and allow final cleaning much more readily.
I tried it with the small sample you gave and it created a table which is certainly way ahead of the text format.
You can then open the table as a recordset and step through it to examine, group, clean or manipulate to your heart's delight via code.
Les
One way or another you will have to parse the text and do some cleaning of it.
A great way to "pre-digest" the text is to bring it in using the import wizard from the menu: File, Get External Data, Import. Select Text Files from the File Type combo. As you step through choose Delimited then Other for the delimiter. Type * as the delimiter and continue through to the end. This will split the data into fields and records, loose the delimiters and allow final cleaning much more readily.
I tried it with the small sample you gave and it created a table which is certainly way ahead of the text format.
You can then open the table as a recordset and step through it to examine, group, clean or manipulate to your heart's delight via code.
Les
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.