Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data for .Txt Files By Unique Field Data
Excel 2003 - I have approximately 150 coma delimited .txt files created from
Access over the past four years acting as an historical database of equipment movement over time in ~ two week slices. The files are named i.e. 60713Inv.txt, 60628Inv.txt, 60612Inv.txt, 60410Inv.txt, 20830Inv.txt in a YMMDD format and contain serial number, Bar Code number information for Identification, and Site ID information of where the equipment was located (among other data) at the time the file was created . I would like to find the file (time slice) where a particular piece of equipment moved e.g. field "Site ID" 55 before to field "Site ID" 70 after - where the change happened between 60606Inv.txt and 60510Inv.txt. I would like to use the last six characters (general format) of the field "Bar Code" or "S/N" to id the file. The amount of equipment has grown over time as new equipment has been added.There are about 2800 pieces of equipment in the lastest file and the first file contains only 1700 pieces. Lastly, one piece could have moved more than once over the years. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data for .Txt Files By Unique Field Data
Send us part of a typical file to check please
"La" wrote in message ... Excel 2003 - I have approximately 150 coma delimited .txt files created from Access over the past four years acting as an historical database of equipment movement over time in ~ two week slices. The files are named i.e. 60713Inv.txt, 60628Inv.txt, 60612Inv.txt, 60410Inv.txt, 20830Inv.txt in a YMMDD format and contain serial number, Bar Code number information for Identification, and Site ID information of where the equipment was located (among other data) at the time the file was created . I would like to find the file (time slice) where a particular piece of equipment moved e.g. field "Site ID" 55 before to field "Site ID" 70 after - where the change happened between 60606Inv.txt and 60510Inv.txt. I would like to use the last six characters (general format) of the field "Bar Code" or "S/N" to id the file. The amount of equipment has grown over time as new equipment has been added.There are about 2800 pieces of equipment in the lastest file and the first file contains only 1700 pieces. Lastly, one piece could have moved more than once over the years. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data for .Txt Files By Unique Field Data
"SiteID","InventoryID","Serial.","ItemCategory","I temDescription","Model.","BiosDate","VABarCode."," MSCertificate.","WinIPcfg","IPAddress","CmptrNme", "OpSystem","SvcPack","McAfeeDat","UserName","Updat ed","UpdatedBy","Notes","NextPlacement","NextPlace mentDate","NextPlacementOfficial","PurchaseDate"," WarrantyExpiration","PurchaseOrderNumber","Assigne dTo","AssignedToNote"
110,423,"23GNGK8",3,"IBM Personal Computer 300GL","6282-58U",,"190868","05597-OEM-0021693-10205","00-E0-29-03-02-CF","204.176.55.72","memremsw4",,,,"vhamembodfoj", 1/31/2002 0:00:00,"vhacowillil",,,,0,,,,, 75,424,"23-M1752",6,"IBM 14"" Color","6540-00N",,"195407",,,"204.176.55.75",,,,,"vhamemtrallj ",10/29/2004 0:00:00,"vhacowillil",,,,0,,,,, 54,430,"JPHK050046",5,"HP LaserJet 5N","C3952A",,"191659",,,"204.176.60.220",,,,,,6/27/2006 0:00:00,"vhacowillil",,,,0,,,,, 110,431,"SG63C2208X",9,"HP Color Scanner 4C","C2520B",,"190804",,,,,,,,,5/17/2002 0:00:00,"vhacowillil",,,,0,,,,, "PY & Associates" wrote: Send us part of a typical file to check please "La" wrote in message ... Excel 2003 - I have approximately 150 coma delimited .txt files created from Access over the past four years acting as an historical database of equipment movement over time in ~ two week slices. The files are named i.e. 60713Inv.txt, 60628Inv.txt, 60612Inv.txt, 60410Inv.txt, 20830Inv.txt in a YMMDD format and contain serial number, Bar Code number information for Identification, and Site ID information of where the equipment was located (among other data) at the time the file was created . I would like to find the file (time slice) where a particular piece of equipment moved e.g. field "Site ID" 55 before to field "Site ID" 70 after - where the change happened between 60606Inv.txt and 60510Inv.txt. I would like to use the last six characters (general format) of the field "Bar Code" or "S/N" to id the file. The amount of equipment has grown over time as new equipment has been added.There are about 2800 pieces of equipment in the lastest file and the first file contains only 1700 pieces. Lastly, one piece could have moved more than once over the years. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data for .Txt Files By Unique Field Data
We would try this, not tested
Dim datefrom& Dim datetill& Dim i& Dim Fname$ Dim Newfname$ Dim rownr% Dim j% For i = datefrom To datetill Step 14 Fname = i & "Inv.txt" Workbooks.OpenText Fname, etc rownr = Match(eqmt, equipment_column).Row Rows(rownr).Copy ThisWorkbook.ActiveSheet.Cells(j + 1, 1) j = j + 1 ActiveWorkbook.Close Next i ActiveWorkbook.SaveAs Newfname "La" wrote in message ... Excel 2003 - I have approximately 150 coma delimited .txt files created from Access over the past four years acting as an historical database of equipment movement over time in ~ two week slices. The files are named i.e. 60713Inv.txt, 60628Inv.txt, 60612Inv.txt, 60410Inv.txt, 20830Inv.txt in a YMMDD format and contain serial number, Bar Code number information for Identification, and Site ID information of where the equipment was located (among other data) at the time the file was created . I would like to find the file (time slice) where a particular piece of equipment moved e.g. field "Site ID" 55 before to field "Site ID" 70 after - where the change happened between 60606Inv.txt and 60510Inv.txt. I would like to use the last six characters (general format) of the field "Bar Code" or "S/N" to id the file. The amount of equipment has grown over time as new equipment has been added.There are about 2800 pieces of equipment in the lastest file and the first file contains only 1700 pieces. Lastly, one piece could have moved more than once over the years. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
consolidation of tables in excel with text and figures | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Printing data validation scenarios | Excel Worksheet Functions |