Access 2003, import data from a txt file

[I am Nick]

Hi,

I have a txt file with all info in one collumn. Data is seperated by ":".
How do I convert them into collumns so that I can import them to access?

Company: Dufwa Design
Date : 15-10-2004
Address: Box 54,
ZIP Code: S-182 05 Djursholm
Country: Sweden
Phone: (46) 8-54496960
Fax: (46) 8-54490020
E-mail: (e-mail address removed)
Product/Service Category: Giftware, Premium & Hobbies>Giftware & Premiums
Contact: Ms Birgitta Dufwa
Position: Owner
Nature of Business: Importer

Company: MaerkesMaennen AB
Date : 15-10-2004
Address: Box 14363,
ZIP Code: S-400 20 Goeteborg
Country: Sweden
Phone: (46) 31-3333450
Fax: (46) 31-333460
E-mail: (e-mail address removed)
Website: www.markesmannen.se
Product/Service Category: Giftware, Premium & Hobbies>Giftware & Premiums
Contact: Mr Kjell Zanders
Position: President
Nature of Business: Importer

I would not mind doing some manual work with the data first.Would it at
least be possible to transpose the data so all records for each company get
to its own collumn, converting many rows like today to many collumns. Any
suggestions appreciated. Cheers Nick

 

[Sam Snow]

I use the Excel import external data tool to convert the txt file to Excel.
Then I import my Excel data into Access. It may be an extra step, but it
works, and I've got it automated to where it takes almost no extra time.
Can't wait to see a better solution.

 

[Tom Wood]

Hello "I am Nick"
This can be done in code.
First create a data import specification. This step is only necessary if you
will be doing this regularly.
File>Get External Data>import
Click on the advanced button.
Select the delimiter as ":"
Add column names if desired.
for table name I used "DataSupplied"
Save "DataSupplied Import Specification"
You should have a table named DataSupplied that looks like this.
DataType DataValue
Company Dufwa Design
Date 15-10-2004
Address Box 54,
ZIP Code S-182 05 Djursholm
Country Sweden
Phone (46) 8-54496960
Fax (46) 8-54490020
E-mail (e-mail address removed)
Product/Service Category Giftware, Premium & Hobbies>Giftware &
Premiums
Contact Ms Birgitta Dufwa
Position Owner
Nature of Business Importer



Company MaerkesMaennen AB
Date 15-10-2004
Address Box 14363,
ZIP Code S-400 20 Goeteborg
Country Sweden
Phone (46) 31-3333450
Fax (46) 31-333460
E-mail (e-mail address removed)
Website www.markesmannen.se
Product/Service Category Giftware, Premium & Hobbies>Giftware &
Premiums
Contact Mr Kjell Zanders
Position President
Nature of Business Importer





If you get this data often you can automate it with

Public Sub GetData()
DoCmd.TransferText acImportDelim, "DataSupplied Import Specification",
"DataSupplied", "DataSupplied.txt"
End Sub

Now create a query to get all of the headers I called it
qryColummHeaderFromDataSupplied
Here is the SQL
SELECT [DataSupplied].ColumnHead AS Expr1
FROM DataSupplied
GROUP BY [DataSupplied].ColumnHead
HAVING ((([DataSupplied].[ColumnHead])<>""));

Create a new table with all of the column heads from the query

Public Sub createTableStructure()
Dim db As DAO.Database
Dim tdfNew As TableDef
Dim rs As DAO.Recordset
Dim idxNew As DAO.Index

Set db = CurrentDb
Set rs = db.OpenRecordset("qryColummHeaderFromDataSupplied",
dbOpenDynaset)
Set tdfNew = db.CreateTableDef("Contacts")
tdfNew.Fields.Append tdfNew.CreateField("CompanyID", dbLong)
tdfNew.Fields("CompanyID").Attributes = dbAutoIncrField

Set idxNew = tdfNew.CreateIndex("CompanyID")
idxNew.Fields.Append idxNew.CreateField("CompanyID")
idxNew.Primary = True
tdfNew.Indexes.Append idxNew

With rs
Do While Not .EOF

tdfNew.Fields.Append tdfNew.CreateField(rs.Fields(0), dbText)
.MoveNext
Loop
End With

db.TableDefs.Append tdfNew

rs.Close
db.Close

End Sub

Now you can populate the data table

Public Sub populate()
Dim db As DAO.Database
Dim rsin As DAO.Recordset
Dim rsout As DAO.Recordset
Dim strType As String
Dim strData As String
Dim nextrecord As AcRecord
Dim i_out As Long
i_out = 1
Set db = CurrentDb
Set rsin = db.OpenRecordset("DataSupplied", dbOpenDynaset)
rsin.MoveLast
rsin.MoveFirst
Set rsout = db.OpenRecordset("Contacts", dbOpenDynaset)

Do While Not rsin.EOF

If rsin.Fields(0) <> "" Then
If i_out = 1 Then
rsout.AddNew
Else
rsout.Edit
End If
strType = rsin.Fields(0)
strData = rsin.Fields(1)
rsout.Fields(strType).Properties(0) = strData
rsout.Update
rsout.Bookmark = rsout.LastModified

Else
If i_out < rsin.RecordCount Then
rsout.AddNew
rsout.Update
rsout.Bookmark = rsout.LastModified
End If
End If
rsin.MoveNext
i_out = i_out + 1
Loop
rsin.Close
rsout.Close
db.Close
End Sub

HTH
Tom