Total Noob Question

[skubeedoo]

I've got a text doc that is formatted like this:



ABC Company
1234 Main Street
Los Angeles, CA 92022
213-555-1212
Employees: 5
John Doe

XYZ Company
2552 Easy Street
San Diego, CA 92066
760-555-1212
Employees: 8
Jane Doe

and so on. How do I get this data into access where it will take this data and format it into columns where each line is a separate company?
Thank you in advance for your help!!!

[NTC]

first try the vanilla import that Access has - and see how things end up in the table. I will guess that you are going to get a new record per line, meaning that your company name is going to be about every 8 records.

in the import wizard it will offer to add a primary key - do that. it will help.

depending then how things end up in the table will determine how to best proceed.

[weekend00]

if you need to import this stuff repeatedly, you'd better write some code to it.
but if it's a one time job, you may format it in excel then import to Access.

[skubeedoo]

I don't know if I'll do it again, but I need it to be automated; there are over 5000 names. The bottom line is I'm trying to get a format that I can import into ACT!

[DaveT]

Post a small example file and somone can probably write a rountine to read your text file and write out a delimited file (one record per line) that most programs (Access, ACT, etc) can read.

It is important that your sample data represent all the cases that can occur. For example, do you always have one line for street address, etc?

Where is the data coming from in the first place? Is it possible that your source can write out the records in a delimited format?

[skubeedoo]

That would be AWESOME!!!

Here's the sample file:

[Access_Blaster]

If I were you I would try and get your leads or contacts in a Excel or Csv format.
Create a simple "Contacts" type database with a single table.
Then create a form to import and export your contacts. The imports can be done with a module and some Vba.

tblContacts
ContactsID
BusinessName
FirstName
LastName
Phone
Employees
Address
MoreStuff

[skubeedoo]

That is over my head. I can get them into an excel sheet, but the problem come in when importing into act; separating data into appropriate fields.

There's got to be a way to do it with access isn't there?

[weekend00]

if you have exactly 6 lines for each company and then one blank line, you can use some simple formulas in Excel to make one company in one line, it's much easier than doing in Access.
Then import to Access if you need.

e.g. copy text into excel sheet column A, use following formula in cell "C1":
=INDIRECT("A"&(ROW()*7-6+COLUMN()-3))
then copy cell "C1" to all cells from column C to column H

if you don't have exactly 6 lines for each company and then one blank line, you need VBA.

[NoellaG]

Hi,

I checked the text file and found that sometimes the record is split over 7 lines if the company name is to long. However the company name is always followed by a tab character chr(9). bases on this knowledge, these 2 functions should do the job:
===================================
Public Function fReadFileInTable(strFileName As String) As Long
On Error GoTo Err_fReadFileInTable


Dim lngRecs As Long
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim objFS As Object
Dim objTextFile As Object
Dim strTextLine As String
Dim intFields As Integer

If Len(strFileName) = 0 Then strFileName = getLoc()

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFS.openTextfile(strFileName, 1)
Set cnn = CurrentProject.Connection
rst.Open "tblImport", cnn, adOpenKeyset, adLockPessimistic
intFields = 0

Do While objTextFile.AtEndOfStream <> True
strTextLine = objTextFile.readline
Select Case intFields
Case 0
rst.AddNew
rst!impDateTime = Now
rst!impNameOrg = Left(strTextLine, Len(strTextLine) - 1)
If Asc(Right(strTextLine, 1)) <> 9 Then
strTextLine = objTextFile.readline
rst!impNameOrg = rst!impNameOrg & " " & Left(strTextLine, Len(strTextLine) - 1)
End If
Case 1
rst!impAddressLine1 = strTextLine
Case 2
rst!impAddressLine2 = strTextLine
Case 3
rst!impTelNr = strTextLine
Case 4
rst!impNrEmployees = strTextLine
Case 5
rst!impNameResp = strTextLine
Case 6
intFields = -1
rst.Update
lngRecs = lngRecs + 1
End Select
intFields = intFields + 1
Loop
objTextFile.Close
fReadFileInTable = lngRecs

Exit_fReadFileInTable:
Set objFS = Nothing
Set objTextFile = Nothing
Set rst = Nothing
Set cnn = Nothing
Exit Function

Err_fReadFileInTable:
fReadFileInTable = -lngRecs
Debug.Print Err.Number & ": " & Err.Description

End Function
Public Function getLoc() As String
On Error GoTo Err_getLoc

Dim fdo As FileDialog
Dim strLocation As String

Set fdo = Application.FileDialog(msoFileDialogOpen)
With fdo
.Title = "Select the file"
.ButtonName = "Select"
.InitialView = msoFileDialogViewDetails
.Filters.Clear
.Filters.Add "Text File", "*.txt"
If .Show = -1 Then
strLocation = CStr(fdo.SelectedItems.Item(1))
Else
strLocation = ""
End If
End With

getLoc = strLocation

Exit_getLoc:
Exit Function

Err_getLoc:
getLoc = "#ERROR#"
Debug.Print Err.Number & ": " & Err.Description
Resume Exit_getLoc

End Function

=================================
In the code I assume the records are imported into a table "tblImport" with the structure as in the attached zipped access database.

If you copy them into a module, don't forget you'll need the references:

* Microsoft Active X data objects X.X library
* Microsoft Office XX object library

where XX stands for the version installed on your computer


hope this helps
NG

[skubeedoo]

NG, thank you for your help.

The sample file has inconsistencies all the way through it as it's a "cut and paste" job from an OCR image to text doc and there's all kinds of spaces.

I opened the file you sent and it looks perfect. As I mentioned, I am a total noob and this is way beyond my knowledge. What do I need to do with the script you wrote? Your help is MUCH appreciated.


weekend00 - It gives you the results in columns C-H, but then it can't be exported properly as it's taking data from column A.

[NoellaG]

hi,

the code is already in the example I send you. just press ALT + F11 and you'll be in the VBA code window.
First you'll need to restore the references as I indicated. Choose Tools -> References and in the window that pops up you'll likely see some "missing references". uncheck those that are missing, and look in the list for those with the same name, but another version number. Check them.
Then create a new form, draw a command button on that form, and go to the properties sheet. In the event property "on click" choose to add an event procedure. Click the ... button to open the procedure (if it doesn't do that automatically)

Add code to call the function (tin ther example cmdImport is the name of the button, this will be different if you give it another name):

Private Sub cmdImport_Click()
Dim lngRecsAdded As Long

lngRecsAdded = fReadFileInTable("")

End Sub


instead of "" you can type in "path + name of the imort file"

hope it works

grNG

[skubeedoo]

There were six or seven versions of the missing references, so I selected all, but got an error and it continued anyway.

I’ve got it up to create form. This is what I’ve done: In the ReadTextFile, with the forms button highlighted, I Created form in design view. Then I drew a command button in the first box of the form and got this error message: Compile error, in query expression ‘AppLoadString([bw_tblBtnActions].[Description])’
I clicked OK and then it opens the command button wizard. Don’t know what to do here….


Thanks again for your help, I really appreciate it.

[skubeedoo]

NoellaG, thanks for your help. I figured out with a suggestion from weekend00 how to do it. After using his suggestion, I highlighted the data and copied and pasted it into a notepad doc and it was able to be imported directly into act with Tab separated.

thanks again....access is a tough one.

[weekend00]

NoellaG, thanks for your help. I figured out with a suggestion from weekend00 how to do it. After using his suggestion, I highlighted the data and copied and pasted it into a notepad doc and it was able to be imported directly into act with Tab separated.

thanks again....access is a tough one.

copy to another excel sheet is much better than copy to notepad. it keep all words in one field.

if copy to notepad, sometime it's hard to seperate the fields.

or you can just move the formular to sheet2, column A to column F.

But this way won't work if any company doesn't has exactly 6 lines.