?
Solved

Converting strange formated txt file to Access Database

Posted on 2012-09-17
12
Medium Priority
?
373 Views
Last Modified: 2012-09-18
I have a large txt file that is formated in a strange way (see attachment).

Note that it is not tabs the makes the indents, but four spaces.

Is it in any way possible to convert this file to an Access Database with the six fieldnames (NAME, SYNOPSIS, SYNTAX, DESCRIPTION, RELATED LINKS, REMARKS)?

And if it is possible, how on earth is it done?

Regards
Kasper K
sample.txt
0
Comment
Question by:Kasper Katzmann
  • 6
  • 5
12 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38404710
This will have to done in code (maybe quite a lot of code, depending on the requirements)
But can you clarify what the outcome has to be.

Are you saying that, for a given Name,   everything between say SYNTAX and DESCRIPTON  goes into the same field and similarly everything between  DESCRIPTON  and RELATED LINKS goes into one field.  In genereal is there just one record for a given Name/Synopsis or does your illustration mean that you are expecting to create multiple records for the same Name/Synopsis?
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38405081
Test this sample.
TextImport27867124.mdb
0
 

Author Comment

by:Kasper Katzmann
ID: 38405227
What I am thinking is that all data between each field name are written in to a new record with the content under the other fieldnames in their block.

Id   Name   Synopsis   Syntac   ...
1    A1         A2             A3
2    A7         A8             A9
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

Expert Comment

by:als315
ID: 38405284
Have you tested my sample? Results are in separate fields in table ImportedText. If you don't need multiline text, remove vbCrLF after line.
0
 

Author Comment

by:Kasper Katzmann
ID: 38405433
I'm out of office for today. I will check tomorrow tell how it works for me :-)
0
 

Author Comment

by:Kasper Katzmann
ID: 38408443
Hi again. It seems to work, but not only on the sample file. I get an error message stating that "The field is to short for the amount of data you're trying to add. Try to add less data." (Translated from danish).

When I hit Debug, this line is highlighted: rst("[" & SectionName & "]") = rst("[" & SectionName & "]") & Trim(Str) & vbCrLf
0
 
LVL 40

Expert Comment

by:als315
ID: 38408533
Have you changed anything in my sample?
0
 
LVL 40

Expert Comment

by:als315
ID: 38408587
You have text length in some fields more then 255 characters, so in example memo fields were used
0
 

Author Comment

by:Kasper Katzmann
ID: 38408605
A little typo from me. I should have written "It seems to work, but only on the sample file."

I haven't  changed anything and it works great with the sample file.
You're right. Some of the fields contains more than 255 characters. In fact, it could be all of them, except the Name field.

Sorry for the confusion.
0
 
LVL 40

Expert Comment

by:als315
ID: 38409140
You can look at content of SectionName in debugger - it is name of field, where type should be changed to MEMO. May be you can prepare new sample with problems. May be some parts are missing?
0
 

Author Comment

by:Kasper Katzmann
ID: 38409657
Yes yes yes!!
You are my hero. I buy you a beer next time you are in Denmark :-)

The case was, that I had made an extraction of all available Exchange 2010 Powershell commands including their help text from Exchange Management Shell. Now, with your help, it has been put into an Access Database for easier accessibility.

I have attached the database for anyone who might find it usefull.
PowerShell4Exchange2010.mdb
0
 
LVL 40

Expert Comment

by:als315
ID: 38409724
Will be glad to see you in Russia.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question