473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Automatically creating tables from spacing deliminated fields

I am trying to figure out how to write some sort of code that will go through the txt files in a folder and pull the data into fields plus the txt file name as well.

For example I would like to pull all data after a : or ? plus the file name into a database and then go to the next row in the database and pull the data from the next txt file in that folder.

This is an example layout of one of the files :
Expand|Select|Wrap|Line Numbers
  1. Computername______________________: ABCDEF001234AB
  2. Asset Tag: 1234567890
  3. Serial Number: MXL1310D1F
  4. HDD Info
  5. DiskSize = 160041885696
  6. Hard Drive Serial Number__________: WD-WCAV12345678
  7. Hard Drive Model Number___________: 00AABB-12A3B
  8.  
  9.  Carefully review the information and make corrections if needed
  10.    *** Please fill in the following answers and save it ***
  11.  
  12. HD encrypted ( Yes or No )? Yes
  13. Primary user (Last, First)? Doe, John
  14. Your Name? Name, My
Nov 16 '11 #1
2 1389
TheSmileyCoder
2,322 Expert Mod 2GB
Based on your supplied example, I think you need to load the entire string into VBA, and then use string manipulation to try and read your info. You can load the file using code like this:
Expand|Select|Wrap|Line Numbers
  1. Public Function LoadFileToString(strFilePath As String) As String
  2.     Dim intFileNumber As Integer
  3.     Dim strContent As String
  4.  
  5.  
  6.     'Get next free file number
  7.         intFileNumber = FreeFile
  8.  
  9.     'Open the file
  10.         Open strFilePath For Input As intFileNumber
  11.  
  12.     'Read to string
  13.         strContent = Input$(LOF(intFileNumber), intFileNumber)
  14.  
  15.     'Close File
  16.         Close intFileNumber
  17.  
  18.     'Return value
  19.         LoadFileToString = strContent
  20. End Function
You can then use the string manipulation tools provided by VBA such as instr, instrRev,Left,Right,Mid,trim,split to try and work your way to the information you require.

As to placing it into a table, I would suggest using a recordset. Example goes here:
Expand|Select|Wrap|Line Numbers
  1. Dim myRS as DAO.Recordset 'Requires reference set to Microsoft DAO 3.X library, x is version dependent
  2. 'Open recordset
  3.   set myRS=CurrentDB.Openrecordset("tbl_MyTable",dbopendynaset)
  4. 'Add new record
  5.   myRS.AddNew
  6.   'Add information
  7.   myRS!FieldName="Example" 'Replace FieldName with the name of your field
  8.   'Update
  9.   myRS.Update
  10.  
  11. 'Cleanup
  12. Set myRS=nothing
This is just information intended to get you going. You will still require a moderate understanding of VBA to convert this to working code.

Best of Luck.
Nov 16 '11 #2
I think it would just be easier option would to see if I could pull the DiskSize, Hard Drive Serial Number, and Hard Drive Model Number through VBS then through a comma after each value when writing to the txt file.
I have figured out how to pull the data for the first 3 lines in my text file (with the commas) and I can have popup prompts for the user to type in the last 3 lines. I just can't find anything on Google to run a straight VB script (not inside Access, or VB.Net, etc.) to pull the variables directly without having to use diskid32.exe and still have the hassel from the post above.

Thank you for your help thus far!

By the way the Mid function would work if only I could parse each line output from the diskid32 seperately and write the new variables to the txt file.
Nov 18 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: nuve | last post by:
Hi Please help.. What I want to do is to create a web form that once submitted it takes the fields and opens a new instance of a Word Template and populates bookmarks within the document with the...
1
by: Ed Hawkes | last post by:
I am having the following problem and any help would be GREATLY appreciated: In an application I am developing, at some points we create a new table. When I create this table on another users...
31
by: Neil | last post by:
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back end. I currently have a selections table in the front end file which the users use to make selections of records. The table...
5
by: -elniniol999- | last post by:
I am DESPERATE for a quick reply.. have exhausted my options... i have a table called order details which contains: order id product id product name quantity unit price sale price
2
by: Chris via AccessMonster.com | last post by:
I have done something like this a million years ago my developer skills have diminished to nothing but now I am at a new job trying to regroup what I lost. This one came across my desk recently. I...
3
by: Reader | last post by:
Hello all, I am joining two tables in a query and the output should be all those records where two fields in table1 match two corresponding fields in table2. I joined the tables using both...
4
by: sroussie | last post by:
Hi, I have spent a few days on this problem and it is driving me nuts. I have two tables called Parcels and Assessments. Both tables have two fields Printkey and SWIS. I have to join the fields...
1
by: DWiggin | last post by:
We are getting deadlock errors (sporadically) on a batch job we've created. This job runs against a SQL Server 2000 back-end. The first step of the batch job is to run a DDL script to drop...
3
by: Guv | last post by:
Hello, Is there a way either via a make table query or code to create multiple tables with the same format. eg if table 1 had ccy, ccy1 and ccy2 as fields I would like to create additional tables...
4
by: gillianbrooks91 | last post by:
Forgive me for asking this question, I've trawled through nearly every available post on this subject that I can find for a few weeks now but nothing quite points me in the right direction. I'm...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.