Access 2003, import data from a txt file
am Nick@discussions.microsoft.com I am Nick
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: birgitt...@dufwadesign.se
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: in...@markesmannen.se
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
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.
need_spam@yahoo.com Tom Wood
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
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
"I am Nick" <I am Ni...@discussions.microsoft.com> wrote in message
news:9487494C-5D42-4B12...@microsoft.com...